Nested IF/AND statements

Hi, I'm having a little trouble with the logic in writing a nested IF/AND statement.

I want to make a calculator to determine the cost of mileage at the following rates

Distance Boundaries Rate/km
0 100 $2.00
101 500 $1.75
501 2000 $1.50
2001 6000 $1.25
6001 infinity $1.00

That is, if I travel 1500 miles, the first 100 will be at $2, the next 400 will be at $1.75, etc

0 - 100 is easy, but the next lines have me puzzled

the best I have come up with for 101 to 500 so far is

=IF(AND(E$12>=$B16,E12<=$C16),$D16*(E$12-C15))

Is it OK to attach the file?

Cheers
Roy Price
Alice Springs
Australia

Try this consolidated formula..

=SUMPRODUCT(--(E12>{0,100,500,2000,6000,100000}), E12-{0,100,500,2000,6000,100000}, {2,-0.25,-0.25,-0.25,-0.25,-0.25})

are the kilometre values constant, or are they changeable?, or at least, stored in that way in actual table?...there are several answers, but I need more info, maybe someone else can do of the cuff, but I need more info

aah, there you go, someone better qualified !
I'll let Ace take from here

could you post the thread that you got the answer fr m?
would probably help other members of the site...

Hi Roy,

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. Also, please mark the thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

The ranges should be cumulative. Hence use

=SUMPRODUCT(--(E2>{0,500,1500,3500,7500,100000}),E2-{0,500,1500,3500,7500,100000}, {2,-0.25,-0.25,-0.25,-0.25,-0.25})