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:

Miles	Ambulatory	Share Ride Ambulatory	Wheelchair Accessible	Share Ride Wheelchair Accessible
1.00	$10.00	$5.00	$20.00	$10.00
2.00	$10.00	$5.00	$20.00	$10.00
3.00	$10.00	$5.00	$20.00	$10.00
4.00	$17.00	$8.50	$27.00	$13.50
5.00	$17.00	$8.50	$27.00	$13.50
6.00	$17.00	$8.50	$27.00	$13.50
7.00	$24.00	$12.00	$34.00	$17.00
8.00	$24.00	$12.00	$34.00	$17.00
9.00	$24.00	$12.00	$34.00	$17.00
10.00	$24.00	$12.00	$34.00	$17.00
11.00	$33.00	$16.50	$44.00	$22.00
12.00	$33.00	$16.50	$44.00	$22.00
13.00	$33.00	$16.50	$44.00	$22.00
14.00	$33.00	$16.50	$44.00	$22.00
15.00	$33.00	$16.50	$44.00	$22.00
16.00	$41.00	$20.50	$53.00	$26.50
17.00	$41.00	$20.50	$53.00	$26.50
18.00	$41.00	$20.50	$53.00	$26.50
19.00	$41.00	$20.50	$53.00	$26.50
20.00	$41.00	$20.50	$53.00	$26.50
21.00	$50.00	$25.00	$62.00	$31.00
22.00	$50.00	$25.00	$62.00	$31.00
23.00	$50.00	$25.00	$62.00	$31.00
24.00	$50.00	$25.00	$62.00	$31.00
25.00	$50.00	$25.00	$62.00	$31.00
26.00	$58.00	$29.00	$72.00	$36.00
27.00	$58.00	$29.00	$72.00	$36.00
28.00	$58.00	$29.00	$72.00	$36.00
29.00	$58.00	$29.00	$72.00	$36.00
30.00	$58.00	$29.00	$72.00	$36.00
31.00	$67.00	$33.50	$83.00	$41.50
32.00	$67.00	$33.50	$83.00	$41.50
33.00	$67.00	$33.50	$83.00	$41.50
34.00	$67.00	$33.50	$83.00	$41.50
35.00	$67.00	$33.50	$83.00	$41.50
36.00	$76.00	$38.00	$92.00	$46.00
37.00	$76.00	$38.00	$92.00	$46.00
38.00	$76.00	$38.00	$92.00	$46.00
39.00	$76.00	$38.00	$92.00	$46.00
40.00	$76.00	$38.00	$92.00	$46.00
41.00	$84.00	$42.00	$100.00	$50.00
42.00	$84.00	$42.00	$100.00	$50.00
43.00	$84.00	$42.00	$100.00	$50.00
44.00	$84.00	$42.00	$100.00	$50.00
45.00	$84.00	$42.00	$100.00	$50.00

<colgroup><col span="5"></colgroup><tbody>
</tbody>