if function for 5 conditions

[sansan88]

=IF($N4<=$B$12,$B$10,IF($N4<$C$12,$B$10,IF($N4>=$C$12,$C$10,IF($N4<$D$12,$C$10,IF($N4>=$D$12,$D$10)))))

I cant seem to get if function to work for 5 conditions, can some help urgently?

Thanks

Sansan

[cbatrody]

It's tough to comprehend the requirement by going through the formula, can you please post a sample template in excel format with an expected outcome in it?

[Sam Capricci]

as cbatrody noted it is difficult to understand your formula without seeing how you are using it.
for example...
=IF($N4<=$B$12,$B$10,IF($N4<$C$12,$B$10,IF($N4>=$C$12,$C$10,IF($N4<$D$12,$C$10,IF($N4>=$D$12,$D$10)))))
what happens if N4 is greater than B10 but still less than C12? It appears you may need an OR statement in the mix or maybe an AND statement or a lookup formula will work?
A sample can go a long way to getting you the specific help you need.
=IF(AND($N4<=$B$12,$N4<$C$12),$B$10,IF($N4>=$C$12,$C$10,IF($N4<$D$12,$C$10,IF($N4>=$D$12,$D$10))))) Maybe this last part, can be the simplified last portion that says =IF(AND($N4<=$B$12,$N4<$C$12),$B$10,IF($N4>=$C$12,$C$10,IF($N4<$D$12,$C$10,$D$10))) (untested)
Again, without seeing the formula in use and the data it is hard to guess.

[sansan88]

thanks, hope you can help :-)

password "Increase2013"

[cbatrody]

Hi,

Try the following formula in B7:

=INDEX(C3:H3,1,MATCH(A3,C3:H3,1))

[sansan88]

thanks Cbatrody, but if the amount is £100, then it should change to midpoint? can you help with that?

Thanks

Kind regards

Sandy

[cbatrody]

It does change to Mid Point when the amount is hundred, see the attached file.