Avoiding #NUM!

B

Bruno Campanini

I've got a couple of formulas for doing in single step:

B A
A B
F D
A F
A #NUM!
D #NUM!
F #NUM!
D #NUM!

but I'm unable to avoid those #NUM!
Any suggestion?

Bruno
 
D

Domenic

Assuming that Column A contains your data, try...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(IF(LEN($A$1:$A$8)>0,1/COUNTIF($A$1:$A$8,$A$1:$A$8)
)),INDEX($A$1:$A$8,MATCH(SMALL(IF(COUNTIF(OFFSET($A$1:$A$8,0,0,ROW($A$1:$
A$8)-ROW($A$1)+1),$A$1:$A$8)=1,COUNTIF($A$1:$A$8,"<"&$A$1:$A$8)),ROWS($B$
1:B1)),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")

....confirmed with CONTROL+SHIFT+ENTER. Actually, it would be more
efficient to enter the following formula in a cell, let's say, C1...

=SUM(IF(LEN(A1:A8)>0,1/COUNTIF(A1:A8,A1:A8)))

....confirmed with CONTROL+SHIFT+ENTER, and then enter the following
formula in B1 and copy down:

=IF(ROWS($B$1:B1)<=$C$1,INDEX($A$1:$A$8,MATCH(SMALL(IF(COUNTIF(OFFSET($A$
1:$A$8,0,0,ROW($A$1:$A$8)-ROW($A$1)+1),$A$1:$A$8)=1,COUNTIF($A$1:$A$8,"<"
&$A$1:$A$8)),ROWS($B$1:B1)),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
H

Harlan Grove

Bruno Campanini wrote...
I've got a couple of formulas for doing in single step:

B A
A B
F D
A F
A #NUM!
D #NUM!
F #NUM!
D #NUM!

but I'm unable to avoid those #NUM!
Any suggestion?
Click to expand...

If you don't show us the formulas, we can't help you.
 
M

Max

... just wondering aloud how you can see the "hidden"
formulas posted that I (like Harlan) don't <bg>
 
M

Max

Ah, think I see it now, it was deduced from what the OP posted, data in col
A to be "tranformed" into col B minus the #NUM! via a formula copied down ..
Magical !
 
D

Domenic

Yeah, I guessed! :) I'm not sure what formulas the OP is using, but if
he wants to use his formula instead he can do the following...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(IF(LEN($A$1:$A$8)>0,1/COUNTIF($A$1:$A$8,$A$1:$A$8)
)),<OP's Formula Here>,"")

Nevertheless, I'd be curious to see the formulas the OP is using. :)
 
H

Harlan Grove

Domenic said:
Assuming that Column A contains your data, try...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(IF(LEN($A$1:$A$8)>0,>1/COUNTIF($A$1:$A$8,
$A$1:$A$8))),INDEX($A$1:$A$8,MATCH(SMALL(IF(COUNTIF(OFFSET($A$1:$A$8,
0,0,ROW($A$1:$A$8)-ROW($A$1)+1),$A$1:$A$8)=1,COUNTIF($A$1:$A$8,
"<"&$A$1:$A$8)),ROWS($B$1:B1)),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")

...confirmed with CONTROL+SHIFT+ENTER. Actually, it would be more
efficient to enter the following formula in a cell, let's say, C1...

=SUM(IF(LEN(A1:A8)>0,1/COUNTIF(A1:A8,A1:A8)))

...confirmed with CONTROL+SHIFT+ENTER, and then enter the following
formula in B1 and copy down:

=IF(ROWS($B$1:B1)<=$C$1,INDEX($A$1:$A$8,
MATCH(SMALL(IF(COUNTIF(OFFSET($A$1:$A$8,0,0,ROW($A$1:$A$8)-ROW($A$1)+1),
$A$1:$A$8)=1,COUNTIF($A$1:$A$8,"<"&$A$1:$A$8)),ROWS($B$1:B1)),
COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")

...confirmed with CONTROL+SHIFT+ENTER.
Click to expand...
....

But it'd be more efficient still not to use SMALL or the volatile
OFFSET at all. It requires using different formulas for the top most
result and the subsequent results.

C1 [array formula - topmost cell]:
=INDEX($A$1:$A$8,MATCH(0,COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0))

C2 [array formula - subsequent, fill down as needed]:
=IF(COUNTIF($A$1:$A$8,">"&C1),INDEX($A$1:$A$8,MATCH(COUNTIF($A$1:$A$8,
"<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")
 
B

Bruno Campanini

Harlan Grove said:
Bruno Campanini wrote...

If you don't show us the formulas, we can't help you.
Click to expand...

Ok, this is the formula:

{=INDEX(Ra5,MATCH(SMALL(IF(COUNTIF(OFFSET(Ra5,,,
ROW(Ra5)-ROW(OFFSET(Ra5,,,1))+1),Ra5)=1,COUNTIF
(Ra5,"<="&Ra5)),ROW(A1)),COUNTIF(Ra5,"<="&Ra5),0))}
FormulaArray over one row, then to copied down.

Bruno
 
B

Bruno Campanini

Assuming that Column A contains your data, try...

B1, copied down:

=IF(ROWS($B$1:B1)<=SUM(IF(LEN($A$1:$A$8)>0,1/COUNTIF($A$1:$A$8,$A$1:$A$8)
)),INDEX($A$1:$A$8,MATCH(SMALL(IF(COUNTIF(OFFSET($A$1:$A$8,0,0,ROW($A$1:$
A$8)-ROW($A$1)+1),$A$1:$A$8)=1,COUNTIF($A$1:$A$8,"<"&$A$1:$A$8)),ROWS($B$
1:B1)),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)),"")
Click to expand...

Ok Domenic, your formula was very useful for me to
understand why I wasn't able to put some ISERROR
before mine in order to avoid those #NUM!.
Then I've modified your formula in order to make it
only dependent by Ra6 (my range of data).

{=IF(ROW(A1)<=SUM(IF(LEN(Ra6)>0,1/COUNTIF(Ra6,Ra6))),
INDEX(Ra6,MATCH(SMALL(IF(COUNTIF(OFFSET
(Ra6,,,ROW(Ra6)-CELL("row",Ra6)+1),Ra6)=1,
COUNTIF(Ra6,"<"&Ra6)),ROW(INDIRECT
(ROW(A1)&":"&ROWS(Ra6)))),COUNTIF(Ra6,"<"&Ra6),0)),"")}

Many thanks Domenic
Ciao
Bruno
 
Top