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

  • Thread starter Thread starter SmokingMirror
  • Start date Start date
S

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

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
 
Thank you VERY much, Ron. That was exactly what I was looking for. Th
code works properly now.

Thanks once again for your help
 
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
_ _
^¢¯^
--
 
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
 
Hi
it represents the 'column index'. So in this case the second 'column'
of the array is returned
 
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
 
Back
Top