Avoiding #NUM!
 

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

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(SMAL L(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!

In article ,
"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?

Bruno

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.

... just wondering aloud how you can see the "hidden"
formulas posted that I (like Harlan) don't <bg
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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 !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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. :)

In article ,
"Max" wrote:

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 !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Domenic wrote:
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(COUNT IF(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)),RO WS($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,MATC H(COUNTIF($A$1:$A$8,
"<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)), "")

"Harlan Grove" wrote in message
ups.com...
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.

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

"Domenic" wrote in message
...

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

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

In article .com,
"Harlan Grove" wrote:

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,MATC H(COUNTIF($A$1:$A$8,
"<="&C1),COUNTIF($A$1:$A$8,"<"&$A$1:$A$8),0)), "")