Need a formula

  • Thread starter Thread starter pattigail1960
  • Start date Start date
P

pattigail1960

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.
 
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.
Click to expand...

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
 
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.
 
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(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)
 
Back
Top