How do I identify a number in a range and then multiply?

[Guest]

Under tight deadline - please help!!!!!

I have developed an excel spreadsheet to cost out different options based on
the number of groups selected. The price per group goes down the more groups
that are selected based on a predetermined range. I need to figure out how
to get the number of groups to multiply by the correct price for that range.

Here are my ranges:
<20
21-39
40-59
60-79
80-99

100

I got the >100 group to work by using:
=IF(D7>99,(D7*'fee schedule'!A28)
but can't get past that.

Please help if you can!
Thanks

 

[Gary Keramidas]

check out select case instead of if then

http://www.ozgrid.com/VBA/select-case.htm

 

[Guest]

One way, set up your table like this (let's call it FeeSchedule)

Range Fee
0 1
21 2
40 3
60 4
80 5
100 6


=VLookup(D7, FeeSchedule, 2)*D7