Formula equation for a conditional number - PART 2 - said better

[G Hunter]

Here is a better way to say what I wanted --

What if a final number is let's say "56"? I would like this number to
trigger a dollar amount based on these range of numbers:

Group 1 Total Est. Hourly Usage (10-30 people) = $50
Group 2 Total Est. Hourly Usage (40-50 people) = $100
Group 3 Total Est. Hourly Usage (60-70 people) = $150
Group 4 Total Est. Hourly Usage (80-90 people) = $200
Group 5 Total Est. Hourly Usage (100-110 people) = $250
Group 6 Total Est. Hourly Usage (120+ people) = $300

So if my final number is a random number of "56 people" then how do I write
the formula to be able to work.

Because the number may change each month. One month it may be "26 people"?

So in words I would like to say:
"If field A1 is between 10 & 39 then put $50 in field A2.
If field A1 is between 40 & 59 then put $100 in field A2.
If field A1 is between 60 & 79 then put $150 in field A2.
If field A1 is between 80 & 99 then put $200 in field A2.
If field A1 is between 100 & 119 then put $250 in field A2.
If field A1 is 120 and above then put $300 in field A2.

I am self taught, so if I am saying this wrong, my apologies. But I think I
said it right. All in all, the total number needs to be able allow for any
number & give me a dollar amount.

Thanks for any assistance,
G. Hunter
(e-mail address removed)

 

[Luke M]

Easiest way to do this would be with a lookup table. You could even hide it
on a unused sheet, if you want. You didn't say, so I'll assume the number
you're comparing to is in A1.
Table setup like this:
0 $0 'I'm assuming you wanted 0 for anything
below 10.
10 $50
40 $100
60 $150
80 $200
100 $250
120 $300

Then in A2, input
=LOOKUP($A$1,'Sheet2'!$A$1:$A$7,'Sheet2'!$B$1:$B$7)

 

[Tom Hutchins]

I would use a lookup table as per Luke's suggestion. However, if the
groupings you listed are all there are, and are not likely to change often,
you could just use a formula with nested IF functions:

=IF(A1>=120,300,IF(A1>=100,250,IF(A1>=80,200,IF(A1>=60,150,IF(A1>=40,100,IF(A1>=10,50,0))))))

Hope this helps,

Hutch

 

[Ken Wright]

Take a look at the help for =VLOOKUP().

You will want one of the formulas where the last argument is TRUE. The
example they give in 2003 could easily be adapted to your data. You do need
to be careful of your cutoff points though, as though yours are quite easy
to see they will be whole numbers, sometimes they are fractional and people
forget that fractions exist between say 10-20 and 21-30. If you still need
help with it then post back, but you'll get a lot more satisfaction if you
can crack it with just a prod in the right direction.

Regards
Ken.....................