Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
... 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 -- |
#5
|
|||
|
|||
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 -- |
#6
|
|||
|
|||
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 -- |
#7
|
|||
|
|||
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)), "") |
#8
|
|||
|
|||
"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 |
#9
|
|||
|
|||
"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 |
#10
|
|||
|
|||
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)), "") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding the dreaded #div/0 error | Excel Worksheet Functions | |||
vlookup avoiding #N/A | Excel Worksheet Functions | |||
Avoiding typing similar formula 600 times | Excel Worksheet Functions | |||
Macro avoiding circular reference | Excel Worksheet Functions | |||
Avoiding circular reference on formula | Excel Discussion (Misc queries) |