Need help with IF, over 7 in one formula

[icka]

In cell C1, enter in a number from 1-10, then in cell C2, depending on the number in C1, a dollar amount will appear.

If C1 = 10, then C2 is 262.50
If C1 = 9, then C2 is 259.88 etc...

10 - 262.50
9 - 259.88
8 - 257.25
7 - 254.63
6 - 252.00
5 - 249.38
4 - 246.75
3 - 244.13
2 - 241.50
1 - 238.88

Of course, I can set up the formula up to 7 IF's. What is the best way to go beyond 7?

One column is where 1-10 would be entered, and the 2nd column is where I want 238.88-262.50 to show up. The 2nd column would have the formula.

This is what I have..
=IF(C32=1,238.88,IF(C32=2,241.5,IF(C32=3,244.13,IF(C32=4,246.75,IF(C32=5,249.39,IF(C32=6,252,IF(C32=7,254.63,0)))))))
This message was edited by icka on 2002-03-27 13:04

 

[BabyTiger]

Hi icka,

My suggest is, instead using IF statements, use a vlookup function or something similar.

For example, have your 1 to 10 table entered somewhere else. , like Column K1 to K10 is 1 to 10, and L1 to L10 is the amounts.

Then in cell C1, C2 onwards, enter whatever number you wish (between 1 and 10 of course), then in column D (eg.D1), enter the following vlookup function:

=vlookup(C1,$K$1:$L$10,2,FALSE)

Basically, it is looking up the value from table K1:L10 based on the reference in C1.

HTH

 

[wi_guy]

If I read it right, what u want is a lookup table.

In 2 columns, anywhere in your worksheet, you would have:
1 - 238.88
2 - 241.50
3 - 244.13
4 - 246.75
5 - 249.38
6 - 252.00
7 - 254.63
8 - 257.25
9 - 259.88
10 - 262.50


If they were in cells a1:b10, and the number that you want to enter is in say D1, then the formula would be =vlookup(d1,$a1.$b10,1)

 

[Aladin Akyurek]

On 2002-03-27 12:25, icka wrote:
In cell C1, enter in a number from 1-10, then in cell C2, depending on the number in C1, a dollar amount will appear.

If C1 = 10, then C2 is 262.50
If C1 = 9, then C2 is 259.88 etc...

10 - 262.50
9 - 259.88
8 - 257.25
7 - 254.63
6 - 252.00
5 - 249.38
4 - 246.75
3 - 244.13
2 - 241.50
1 - 238.88

Of course, I can set up the formula up to 7 IF's. What is the best way to go beyond 7?

This is what I have..
=IF(C32=1,238.88,IF(C32=2,241.5,IF(C32=3,244.13,IF(C32=4,246.75,IF(C32=5,249.39,IF(C32=6,252,IF(C32=7,254.63,0)))))))
This message was edited by icka on 2002-03-27 12:26

Adding to Tiger's observation:

=INDEX({238.88,241.5,244.13,246.75,249.38,252,254.63,257.25,259.88,262.5},C32)

Aladin

 

[wi_guy]

Opps, I had a typo.....offset is 2, not 1 and I forgot the ,false also.........sheeesh you guys are quick.

 

[bobmc]

OK, this is my first try at offering advice on this board, but here goes...

I'd use a VLOOKUP command. Build a table much like you have above with the "1 through 10" and their amounts. Let's say you build this table in cells A1 through B10.

Then, have the user enter any number 1 through 10 in cell C1. In cell C2, the formula would be as follows...

VLOOKUP(C1,A1:B10,2,FALSE)

Give this a try. I would also defer to one of the more experienced board members on this.

bobmc

 

[icka]

On 2002-03-27 12:38, wi_guy wrote:
If I read it right, what u want is a lookup table.

In 2 columns, anywhere in your worksheet, you would have:
1 - 238.88
2 - 241.50
3 - 244.13
4 - 246.75
5 - 249.38
6 - 252.00
7 - 254.63
8 - 257.25
9 - 259.88
10 - 262.50


If they were in cells a1:b10, and the number that you want to enter is in say D1, then the formula would be =vlookup(d1,$a1.$b10,1)

Actually, one column is where 1-10 would be entered, and the 2nd column is where I want 238.88-262.50 to show up. The 2nd column would have the formula.

 

[wi_guy]

That's correct...the vlookup table where the data resides would be off to the side somewhere or even on another worksheet, and then you would type 1-10 in column A, and the formula in Column B....and it will lookup the information in Column A and bring back the correct $ figure in Column B.....if this isn't what you were after, please redifine the problem.

 

[icka]

Thanks for the help!!
It worked!

 

[Aladin Akyurek]

Actually, one column is where 1-10 would be entered, and the 2nd column is where I want 238.88-262.50 to show up. The 2nd column would have the formula.

The INDEX formula also fits what you want:

If A1 has a target number,

in B1 enter:

=INDEX({238.88,241.5,244.13,246.75,249.38,252,254.63,257.25,259.88,262.5},A1)

INDEX uses the value in A1 to determine which number to retrieve from the constant array:

{238.88,241.5,244.13,246.75,249.38,252,254.63,257.25,259.88,262.5}

in case you missed it.

Aladin