Screwing up a nested IF function

[Merlin Archer]

Hi. I'm creating a spreadsheet for a music group that pays tutor expenses for travel. For a round trip up to 200 miles the standard payment is £25, for a round trip of between 200 and 400 miles its £50, and anything over a 400 mile round trip pays £100. What I want is to be able to enter the mileage in one cell (D15) and use a nested IF function to automatically add the payment in another cell. I've created a nested iF function,

=IF(D15<201,"25",IF(D15>201<399,"50",IF(D15>400,"100")))

which works for the first term but returns FALSE for anything over. I'm nobody's Excel expert and can't see what I've got wrong. Any help would be greatly appreciated!

 

[Trebor76]

Hi Merlin,

Try this:

=IF(D15<=200,25,IF(AND(D15>=201,D15<=399),50,100))

Regards,

Robert

 

[Merlin Archer]

Hi Merlin,

Try this:

=IF(D15<=200,25,IF(AND(D15>=201,D15<=399),50,100))

Regards,

Robert

Thank you Robert- that works beautifully! Its now nearly 1am; tomorrow when my brain has rested and been liberally doused in coffee I shall study your formula and learn!

 

[Michael M]

Shorter

=IF(D15<=200,25,IF(D15<=399,50,100))

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[Merlin Archer]

Shorter

=IF(D15<=200,25,IF(D15<=399,50,100))

Thank you Michael. Robert's answer arrived very quickly after I posted and I have used that; yours is different, shorter, and I shall use both in an effort to learn about nesting functions. Your reply is much appreciated.

 

[Michael M]

Glad to help...