Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard for any number in conditional formula | Excel Discussion (Misc queries) | |||
array conditional equation | Excel Worksheet Functions | |||
Continuing equation a set number of times | Excel Discussion (Misc queries) | |||
A conditional equation with three possible outcomes? | Setting up and Configuration of Excel | |||
Equation Referencing Row Number Stored in Cell | Excel Discussion (Misc queries) |