Avoiding #NUM!

[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

 

[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!

 

[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?

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

 

[Max]

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

 

[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 !

 

[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.

 

[Harlan Grove]

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.

....

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)),"")

 

[Bruno Campanini]

Bruno Campanini wrote...

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

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

 

[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)),"")

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

 

[Domenic]

Beautiful...just the type of thing I was looking for! Thanks Harlan!
Much appreciated!