Formula wont take any more?

[Mel]

Hello,

I have the following formula and it works UP TO putting into "H", but I want
to put another two logical tests ("I" & "J") (or if I wanted more) but
cannot seem to get it to accept?

I would also like the formula for the cell B181 that will automatically put
the A B C D up to J etc in if the amount is more than number indicated. e.g.
100, 149.99, 199.99 etc

I have broken the formula up so as to post but it is continuous in the cell.

=IF ((B181="A")*((F181)>0),0.01*(F181),
IF ((B181="B")*((F181)>100),0.02*(F181),
IF ((B181="C")*((F181)>149.99),0.03*(F181),
IF ((B181="D")*((F181)>199.99),0.0375*(F181),
IF ((B181="E")*((F181)>249.99),0.04*(F181),
IF ((B181="F")*((F181)>299.99),0.055*(F181),
IF ((B181="G")*((F181)>349.99),0.06*(F181),
IF ((B181="H")*((F181)>399.99),0.0775*(F181), (accepts if I have up to this
point)
IF ((B181="I")*((F181)>449.99),0.08*(F181),
IF ((B181="J")*((F181)>499.99),0.1*(F181),0))))))))))

Thanks for any help
Mel

 

[Rishie]

The reason for this is that you cannot have more than 7 IFs (or 8
conditions in total) in a single cell. My suggestions is to use
VLookup as Martin suggested.

Mohit Khurana, CFA
http://ExcelMatic.blogspot.com

 

[Mel]

I have found using VLOOKUP that I don't need the A B C column as it was just
to recognise the price over and above etc,

I have created a table and named it Bonus_Table on another sheet in the
workbook, and now only need the function =VLOOKUP(F2,Bonus_Table,2) however
in the H2 cell where this formula is, if there is nothing in F2, which will
occur many times, it come up with #N/A, how do I fix this please.

Tks Mel

 

[Mel]

This still gives the #N/A

If F2 is truly empty,

=IF(ISBLANK(F2),"nothing there",VLOOKUP(F2,Bonus_Table,2))

 

[Mel]

OH Yah, I "think", I have it

I put in
=IF(ISERROR(VLOOKUP(F2,Bonus_Table,2)),"",VLOOKUP(F2,Bonus_Table,2))

BTW
"smartin" I did not mean to reply to you direct, so sorry about that, but
thanks for your boost to my confidence. for some strange reason I have
always avoided the VLOOKUP, just one of those things I needed to overcome.
Thanks.
Mel