Which formula do I use for following, this is what I want to work out.

B5=£397 if C3<801 and B5=£545 if C3<1601and B5=£742 if C3<3501 and so on

I'm sure there's a perfectly simple explanation but allas I can't seem to work it out for myself...

What you are looking for is called a "Nested IF". The arguments for an IF statement include a

value_if_trueand avalue_if_false.Instead of a "value" you can use another IF statement as those arguments. In your case, you would use an IF as the

value_if_falsearguments for the precedinglogical_test.Keep in mind that Excel 2003 is limited to 7 Nested IF's, so if you have more conditions than that, this solution won't work.

A Google search on "Nested If limitation" will return lots of hits with workarounds.

=IF(C3<801, 397, IF(C3<1601,545, IF(C3<3501,742, "C3 is Greater Than Or Equal to 3501")))

Ask Your Question

Weekly Poll