Help! Creating a formula.

[shenyyang]

Hello geniuses!

I need help calculating a formula for a mileage log sheet. The information below is what I will be working with:

Types of Rides:
Ambulatory (A)
Share Ride with Ambulatory (SA)
Wheel Chair (W)
Share Ride with Wheel Chair (SW)

These rides are attached to set rates that are calculated by a certain range of miles. For example:
1-3 miles:
A = $10.00
SA = $5.00
W = $20.00
SW = $10.00

31-35 miles:
A = $58.00
SA = $58.00
W = $58.00
SW = $58.00

How do I do it so that when I enter mileage, it will show up with these set rates?

 

[Sobkowl]

Not sure what you want to do with the range of 4-30, but this should get you started

=IF(AND(E8>=31,E8<=35),58,IF(AND(E8>0,E8<=3),VLOOKUP(D8,G2:H5,2,FALSE)))

where d8 is the ride and e8 is where the miles are entered & g2:h5 is type of ride and then fee for miles 1-3 since all rides 31-35 miles are the same

 

[shenyyang]

This is the chart I'm working with:
[TABLE="width: 305"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Miles[/TD]
[TD]Ambulatory
[/TD]
[TD]Share Ride Ambulatory
[/TD]
[TD]Wheelchair Accessible[/TD]
[TD]Share Ride Wheelchair Accessible[/TD]
[/TR]
[TR]
[TD]1.00[/TD]
[TD="align: right"] $10.00 [/TD]
[TD="align: right"] $5.00 [/TD]
[TD="align: right"] $20.00 [/TD]
[TD="align: right"] $10.00 [/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD="align: right"] $10.00 [/TD]
[TD="align: right"] $5.00 [/TD]
[TD="align: right"] $20.00 [/TD]
[TD="align: right"] $10.00 [/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD="align: right"] $10.00 [/TD]
[TD="align: right"] $5.00 [/TD]
[TD="align: right"] $20.00 [/TD]
[TD="align: right"] $10.00 [/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[TD="align: right"] $17.00 [/TD]
[TD="align: right"] $8.50 [/TD]
[TD="align: right"] $27.00 [/TD]
[TD="align: right"] $13.50 [/TD]
[/TR]
[TR]
[TD]5.00[/TD]
[TD="align: right"] $17.00 [/TD]
[TD="align: right"] $8.50 [/TD]
[TD="align: right"] $27.00 [/TD]
[TD="align: right"] $13.50 [/TD]
[/TR]
[TR]
[TD]6.00[/TD]
[TD="align: right"] $17.00 [/TD]
[TD="align: right"] $8.50 [/TD]
[TD="align: right"] $27.00 [/TD]
[TD="align: right"] $13.50 [/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD="align: right"] $24.00 [/TD]
[TD="align: right"] $12.00 [/TD]
[TD="align: right"] $34.00 [/TD]
[TD="align: right"] $17.00 [/TD]
[/TR]
[TR]
[TD]8.00[/TD]
[TD="align: right"] $24.00 [/TD]
[TD="align: right"] $12.00 [/TD]
[TD="align: right"] $34.00 [/TD]
[TD="align: right"] $17.00 [/TD]
[/TR]
[TR]
[TD]9.00[/TD]
[TD="align: right"] $24.00 [/TD]
[TD="align: right"] $12.00 [/TD]
[TD="align: right"] $34.00 [/TD]
[TD="align: right"] $17.00 [/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[TD="align: right"] $24.00 [/TD]
[TD="align: right"] $12.00 [/TD]
[TD="align: right"] $34.00 [/TD]
[TD="align: right"] $17.00 [/TD]
[/TR]
[TR]
[TD]11.00[/TD]
[TD="align: right"] $33.00 [/TD]
[TD="align: right"] $16.50 [/TD]
[TD="align: right"] $44.00 [/TD]
[TD="align: right"] $22.00 [/TD]
[/TR]
[TR]
[TD]12.00[/TD]
[TD="align: right"] $33.00 [/TD]
[TD="align: right"] $16.50 [/TD]
[TD="align: right"] $44.00 [/TD]
[TD="align: right"] $22.00 [/TD]
[/TR]
[TR]
[TD]13.00[/TD]
[TD="align: right"] $33.00 [/TD]
[TD="align: right"] $16.50 [/TD]
[TD="align: right"] $44.00 [/TD]
[TD="align: right"] $22.00 [/TD]
[/TR]
[TR]
[TD]14.00[/TD]
[TD="align: right"] $33.00 [/TD]
[TD="align: right"] $16.50 [/TD]
[TD="align: right"] $44.00 [/TD]
[TD="align: right"] $22.00 [/TD]
[/TR]
[TR]
[TD]15.00[/TD]
[TD="align: right"] $33.00 [/TD]
[TD="align: right"] $16.50 [/TD]
[TD="align: right"] $44.00 [/TD]
[TD="align: right"] $22.00 [/TD]
[/TR]
[TR]
[TD]16.00[/TD]
[TD="align: right"] $41.00 [/TD]
[TD="align: right"] $20.50 [/TD]
[TD="align: right"] $53.00 [/TD]
[TD="align: right"] $26.50 [/TD]
[/TR]
[TR]
[TD]17.00[/TD]
[TD="align: right"] $41.00 [/TD]
[TD="align: right"] $20.50 [/TD]
[TD="align: right"] $53.00 [/TD]
[TD="align: right"] $26.50 [/TD]
[/TR]
[TR]
[TD]18.00[/TD]
[TD="align: right"] $41.00 [/TD]
[TD="align: right"] $20.50 [/TD]
[TD="align: right"] $53.00 [/TD]
[TD="align: right"] $26.50 [/TD]
[/TR]
[TR]
[TD]19.00[/TD]
[TD="align: right"] $41.00 [/TD]
[TD="align: right"] $20.50 [/TD]
[TD="align: right"] $53.00 [/TD]
[TD="align: right"] $26.50 [/TD]
[/TR]
[TR]
[TD]20.00[/TD]
[TD="align: right"] $41.00 [/TD]
[TD="align: right"] $20.50 [/TD]
[TD="align: right"] $53.00 [/TD]
[TD="align: right"] $26.50 [/TD]
[/TR]
[TR]
[TD]21.00[/TD]
[TD="align: right"] $50.00 [/TD]
[TD="align: right"] $25.00 [/TD]
[TD="align: right"] $62.00 [/TD]
[TD="align: right"] $31.00 [/TD]
[/TR]
[TR]
[TD]22.00[/TD]
[TD="align: right"] $50.00 [/TD]
[TD="align: right"] $25.00 [/TD]
[TD="align: right"] $62.00 [/TD]
[TD="align: right"] $31.00 [/TD]
[/TR]
[TR]
[TD]23.00[/TD]
[TD="align: right"] $50.00 [/TD]
[TD="align: right"] $25.00 [/TD]
[TD="align: right"] $62.00 [/TD]
[TD="align: right"] $31.00 [/TD]
[/TR]
[TR]
[TD]24.00[/TD]
[TD="align: right"] $50.00 [/TD]
[TD="align: right"] $25.00 [/TD]
[TD="align: right"] $62.00 [/TD]
[TD="align: right"] $31.00 [/TD]
[/TR]
[TR]
[TD]25.00[/TD]
[TD="align: right"] $50.00 [/TD]
[TD="align: right"] $25.00 [/TD]
[TD="align: right"] $62.00 [/TD]
[TD="align: right"] $31.00 [/TD]
[/TR]
[TR]
[TD]26.00[/TD]
[TD="align: right"] $58.00 [/TD]
[TD="align: right"] $29.00 [/TD]
[TD="align: right"] $72.00 [/TD]
[TD="align: right"] $36.00 [/TD]
[/TR]
[TR]
[TD]27.00[/TD]
[TD="align: right"] $58.00 [/TD]
[TD="align: right"] $29.00 [/TD]
[TD="align: right"] $72.00 [/TD]
[TD="align: right"] $36.00 [/TD]
[/TR]
[TR]
[TD]28.00[/TD]
[TD="align: right"] $58.00 [/TD]
[TD="align: right"] $29.00 [/TD]
[TD="align: right"] $72.00 [/TD]
[TD="align: right"] $36.00 [/TD]
[/TR]
[TR]
[TD]29.00[/TD]
[TD="align: right"] $58.00 [/TD]
[TD="align: right"] $29.00 [/TD]
[TD="align: right"] $72.00 [/TD]
[TD="align: right"] $36.00 [/TD]
[/TR]
[TR]
[TD]30.00[/TD]
[TD="align: right"] $58.00 [/TD]
[TD="align: right"] $29.00 [/TD]
[TD="align: right"] $72.00 [/TD]
[TD="align: right"] $36.00 [/TD]
[/TR]
[TR]
[TD]31.00[/TD]
[TD="align: right"] $67.00 [/TD]
[TD="align: right"] $33.50 [/TD]
[TD="align: right"] $83.00 [/TD]
[TD="align: right"] $41.50 [/TD]
[/TR]
[TR]
[TD]32.00[/TD]
[TD="align: right"] $67.00 [/TD]
[TD="align: right"] $33.50 [/TD]
[TD="align: right"] $83.00 [/TD]
[TD="align: right"] $41.50 [/TD]
[/TR]
[TR]
[TD]33.00[/TD]
[TD="align: right"] $67.00 [/TD]
[TD="align: right"] $33.50 [/TD]
[TD="align: right"] $83.00 [/TD]
[TD="align: right"] $41.50 [/TD]
[/TR]
[TR]
[TD]34.00[/TD]
[TD="align: right"] $67.00 [/TD]
[TD="align: right"] $33.50 [/TD]
[TD="align: right"] $83.00 [/TD]
[TD="align: right"] $41.50 [/TD]
[/TR]
[TR]
[TD]35.00[/TD]
[TD="align: right"] $67.00 [/TD]
[TD="align: right"] $33.50 [/TD]
[TD="align: right"] $83.00 [/TD]
[TD="align: right"] $41.50 [/TD]
[/TR]
[TR]
[TD]36.00[/TD]
[TD="align: right"] $76.00 [/TD]
[TD="align: right"] $38.00 [/TD]
[TD="align: right"] $92.00 [/TD]
[TD="align: right"] $46.00 [/TD]
[/TR]
[TR]
[TD]37.00[/TD]
[TD="align: right"] $76.00 [/TD]
[TD="align: right"] $38.00 [/TD]
[TD="align: right"] $92.00 [/TD]
[TD="align: right"] $46.00 [/TD]
[/TR]
[TR]
[TD]38.00[/TD]
[TD="align: right"] $76.00 [/TD]
[TD="align: right"] $38.00 [/TD]
[TD="align: right"] $92.00 [/TD]
[TD="align: right"] $46.00 [/TD]
[/TR]
[TR]
[TD]39.00[/TD]
[TD="align: right"] $76.00 [/TD]
[TD="align: right"] $38.00 [/TD]
[TD="align: right"] $92.00 [/TD]
[TD="align: right"] $46.00 [/TD]
[/TR]
[TR]
[TD]40.00[/TD]
[TD="align: right"] $76.00 [/TD]
[TD="align: right"] $38.00 [/TD]
[TD="align: right"] $92.00 [/TD]
[TD="align: right"] $46.00
[/TD]
[/TR]
[TR]
[TD]41.00[/TD]
[TD="align: right"] $84.00 [/TD]
[TD="align: right"] $42.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $50.00 [/TD]
[/TR]
[TR]
[TD]42.00[/TD]
[TD="align: right"] $84.00 [/TD]
[TD="align: right"] $42.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $50.00 [/TD]
[/TR]
[TR]
[TD]43.00[/TD]
[TD="align: right"] $84.00 [/TD]
[TD="align: right"] $42.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $50.00 [/TD]
[/TR]
[TR]
[TD]44.00[/TD]
[TD="align: right"] $84.00 [/TD]
[TD="align: right"] $42.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $50.00 [/TD]
[/TR]
[TR]
[TD]45.00[/TD]
[TD="align: right"] $84.00 [/TD]
[TD="align: right"] $42.00 [/TD]
[TD="align: right"] $100.00 [/TD]
[TD="align: right"] $50.00 [/TD]
[/TR]
</tbody>[/TABLE]