Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
I need a formula for the following example:
If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
On Wed, 7 Jan 2009 10:31:02 -0800, pattigail1960
wrote: I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. You do not specify what you want to occur if A1 < 0.01, or if A1 is between 4 and 4.01, etc. I made some assumptions, but if you want to specify, you should do so. Set up a table someplace with these entries: 0 0.5 4 1 8 1.5 12 2 16 2.5 20 3 24 3.5 28 4 32 4.5 36 5 I used the range I1:J10 Then enter this formula: B1: =IF(A1="","",VLOOKUP(A1,$I$1:$J$10,2)) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
Try this:
=IF(A1=36,5,INT(CEILING(A1/4,1))/2) -- Biff Microsoft Excel MVP "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
You've left a number of ranges undefined, such as <0.01, between 4 and 4.01,
between 8 and 8.01, and so on up to between 32 and 32.01, then strangely between 35.99 and 36, but guessing at what you might have wanted for some of those, try =IF(A1<=0,"undefined",IF(A1=36,5,CEILING(A1,4)/8)) The answer to your footnote is that you can't use that many nested IFs, at least in Excel 2003. Another option worth looking at is VLOOKUP. -- David Biddulph "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
Hi,
In 2007 you could use a nested if, but why bother, since other solutions are so simple. Excel 2003 allows 7 level deep nesting Excel 2007 allows 64 level deep nesting -- If this helps, please click the Yes button. Cheers, Shane Devenshire "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
=IF(A1=36,5,INT(CEILING(A1/4,1))/2)
Since we're using the CEILING function the INT function becomes redundant. =IF(A1=36,5,CEILING(A1/4,1)/2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A1=36,5,INT(CEILING(A1/4,1))/2) -- Biff Microsoft Excel MVP "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
That works perfectly! Thank you!
"T. Valko" wrote: Try this: =IF(A1=36,5,INT(CEILING(A1/4,1))/2) -- Biff Microsoft Excel MVP "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "pattigail1960" wrote in message ... That works perfectly! Thank you! "T. Valko" wrote: Try this: =IF(A1=36,5,INT(CEILING(A1/4,1))/2) -- Biff Microsoft Excel MVP "pattigail1960" wrote in message ... I need a formula for the following example: If A1 = 0.01 to 4.00, then B1 = 0.5 If A1 = 4.01 to 8.0, then B1 = 1.0 If A1 = 8.01 to 12.0, then B1 = 1.5 If A1 = 12.01 to 16.0, then B1 = 2.0 If A1 = 16.01 to 20.0, then B1 = 2.5 If A1 = 20.01 to 24.0, then B1 = 3.0 If A1 = 24.01 to 28.0, then B1 = 3.5 If A1 = 28.01 to 32.0, then B1 = 4.0 If A1 = 32.01 to 35.99, then B1 = 4.5 If A1 = 36.0 or greater, then B1 = 5.0 Thank you to anyone who can help - I am not proficient enough w/Excel to even know if that many if/then's can be met. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|