IF only.....IF only....

[SmokingMirror]

I'm experiencing some troubles with the evil IF command, and Excel's i
built help isn't especially...well, helpful, so I'm hoping you lovel
people here can come to my aid.


_Background_
Ok, I have a table of data, which allows the user to input 3 sets o
numbers, one in each cell. Let's call these A, B, and C.
Through a complex formula the data in cells A-C is manipulated t
result in a number finally appearing in cell D, which the user canno
alter. This part of the worksheet is fine, and runs as it should.


_Problem_
Now, what I'm trying to get the sheet to do is take the final figure i
cell D, and, using the variables below, automatically fill out a ne
cell (cell E):

If cell D equals 0-6 then cell E should automatically read 30
If cell D equals 7-12 then cell E should automatically read 15
If cell D equals 13-48 then cell E should automatically read 10
If cell D equals 49-90 then cell E should automatically read 5
If cell D equals 91-120 then cell E should automatically read 1
If cell D is grater than 120 then cell E should automatically read 0

I can use the IF command to work between two variables, but not the 6
require. I've studied the multiple IF variables example in the hel
menu, but everytime I replicate it, it doesn't work.

The formula that I'm using, which exists in Cell E, is as follows:

IF(CellD>0,"30",IF(CellD>6,"15",IF(CellD>12,"10",IF(CellD>48,"10",IF(CellD>90,"5",IF(CellD>120,"1","0"))))))

Using this formula Cell E reads 0 when Cell D reads 0, which is fine.
If Cell D reads greater than 1, Cell E reads 30, which is also fine.
However, nomatter what Cell D reads, if it is greater than 0, Cell
always reads 30. I understand the logic behind the problem, I jus
don't see how to fix it! Please, where am I going wrong!

 

[Guest]

Hi

If you've used the same format in Cell D as you have posted, I would guess
that you've got a text result in cell D - because you've used "". Instead of
using "30" use 30. This tells Excel it's a number, and it can then calculate
cell E correctly.

 

[Ron Rosenfeld]

Now, what I'm trying to get the sheet to do is take the final figure in
cell D, and, using the variables below, automatically fill out a new
cell (cell E):

If cell D equals 0-6 then cell E should automatically read 30
If cell D equals 7-12 then cell E should automatically read 15
If cell D equals 13-48 then cell E should automatically read 10
If cell D equals 49-90 then cell E should automatically read 5
If cell D equals 91-120 then cell E should automatically read 1
If cell D is grater than 120 then cell E should automatically read 0

I can use the IF command to work between two variables, but not the 6 I
require.

The IF function is not the best one to use in this instance. VLOOKUP is
better.

For example:

=VLOOKUP(D1,{0,30;7,15;13,10;49,5;91,1;120,0},2)

will return the results I think you want. Note that in your description, only
integer values are specified. It's not clear what result you want if Cell D
equals, for example, 12.1. So you may need to alter the formula depending on
the desired behavior.

The array constant in the above can also be set up as a range on your
worksheet.

With a table in G1:H6

0 30
7 15
13 10
49 5
91 1
120 0

the above formula is equivalent to:

=VLOOKUP(D1,G1:H6,2)



--ron

 

[SmokingMirror]

Thank you VERY much, Ron. That was exactly what I was looking for. Th
code works properly now.

Thanks once again for your help

 

[Soo Cheon Jheong]

Hi,

Try a formula like the following:

E1: =IF(D1<0,"",IF(D1<7,30,IF(D1<13,15,IF(D1<49,10,
IF(D1<91,5,IF(D1<121,1,0))))))


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[SmokingMirror]

Ah, thanks, Soo Cheon Jheong. your code is actualy better for me, sinc
it covers the condition whereby D1 might be less than 0 (which ca
happen in my worksheet).

Thank you to everyone who has helped out

 

[Ryan]

VLOOKUP(D1,{0,30;7,15;13,10;49,5;91,1;120,0},2)
What does the 2 at the end represent?

 

[Frank Kabel]

Hi
it represents the 'column index'. So in this case the second 'column'
of the array is returned

 

[Gord Dibben]

Ryan

Example only......cell refs and data not yours, but same principle.

In a regular VLOOKUP you would have a 2 column table with 1,2,3,4 in say B1:B4

VG,G,OK,VB in C1:C4

=VLOOKUP(A1,B1:C4,2) would let Excel know to return from column C(2nd column
in the table)

If you notice, the internal array has semi-colons between the choices. This
emulates the 2 column table.

Change the 2 to 1 and see what happens.

Could also be written as =LOOKUP(A1,{1,2,3,4},{"VG","G","OK","VB"})

Here you can see the two parts of the table clearly.

Gord Dibben Excel MVP