If Function neglecting 0 as a number

[managingcrap]

I have this function:

=IF(B1="","",LOOKUP(COLUMNS($A$1:B1)-COUNTIF($A1:B1,"="),{1,2,3,4},{0.2,0.19,0.18,0.17}))

I'm wanting to modify it in such a way that when I put 0(zero) on A1
the result on B2 would still be 0.2 instead of .19. For some reason
every time I put 0 on A1, the function is recognizing it as a number.

I attached the excel screen shot.

Thanks

+-------------------------------------------------------------------
|Filename: Untitled.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=929
+-------------------------------------------------------------------

 

[joeu2004]

I have this function:
=IF(B1="","",LOOKUP(COLUMNS($A$1:B1)-COUNTIF($A1:B1,"="),{1,2,3,4},{0.2,0.19,0.18,0.17}))
I'm wanting to modify it in such a way that when I put 0(zero) on A1,
the result on B2 would still be 0.2 instead of .19. For some reason,
every time I put 0 on A1, the function is recognizing it as a number.

I believe COLUMNS($A$1:B1)-COUNTIF($A1:B1,"=") is the same as
COUNTIF($A1:B1,"<>"), when dragged across a column.

(The expression does not make sense if you dragged it across and down a
rectangular range.)

Try:
=IF(B1="","",LOOKUP(COUNTIF($A1:B1,"<>")-COUNTIF($A1:B1,0),{0,2,3,4},{0.2,0.19,0.18,0.17}))

Note the change from {1,2,3,4} to {0,2,3,4}. Thus, 0.2 is returned when the
first expression is 0 or 1.

I attached the excel screen shot. [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=929|

For future reference, an actual Excel file is better than an image.
Otherwise, we must take it on faith that you replicated the formula
correctly in A3 and C3:H3.