ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Formula equation for a conditional number (https://www.excelbanter.com/excel-worksheet-functions/213725-formula-equation-conditional-number.html)

G Hunter

Formula equation for a conditional number
 
If anyone knows how to create the proper formula for this condition: If the
amount of my total group of people is in let's say A5, and the number is
"40". What I need is for that number to trigger a dollar amount in another
field based on this condition. So what I want to do is create the formula
like - "IF A5 is a number between 10-39, then give me $50. IF A5 is a number
between 40-49, then give me $100. IF A5 is a number between 50-59, then give
me $150 and so on.

Here are the three things I am working with.
(A)
A1 - Group 1 Hourly Usage (10-30 people) = $50
A2 - Group 2 Hourly Usage (40-50 people) = $100
A3 - Group 3 Hourly Usage (60-70 people) = $150
A4 - Group 4 Hourly Usage (80-90 people) = $200
A5 - Group 5 Hourly Usage (100-110 people) = $250
A6 - Group 6 Hourly Usage (120+ people) = $300

(B)
B1 - TOTAL AMOUNT OF PEOPLE "56"

(C)
C5 - The dollar amount

Here is a formula I got to work, but it will only give a result based on 1
number. I need a formula to work with a range of numbers:
=IF($I$133<40,$I$57,IF($I$13340,$I$58,IF($I$133<7 0,$I$58,
IF($I$13370,$I$59,IF($I$133<100,$I$59,IF($I$1331 00,$I$60))))))

As you can see I133 is the final number field! But I that field to be able
to trigger with a range of numbers like 10-39, instead of just 40. I57 is
equal to $50, I58 is equal to $100. But what if I133 is like 56 or
something?? Then I need a range to be in this formula instead of "40, <40,
<70, 70, <100 or 100. This is not definitive enough.

So I put the range of numbers up above, as you can see. Please if anyone
knows how to do this range, I would greatly appreciate it.

G. Hunter


Pecoflyer[_46_]

Formula equation for a conditional number
 

try something like =lookup($I$33,{0,40,60,80},{50,100,150,200})
You can replace the series between brackets with ranges containing the
values


--
Pecoflyer

Cheers - MS Excel Newbie
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41087



All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com