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.

"managingcrap" wrote:
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.


"managingcrap" wrote:
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.