Need help with IF, over 7 in one formula

icka · Mar 27th, 2002, 01:25 PM

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 · Mar 27th, 2002, 01:34 PM

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 · Mar 27th, 2002, 01:38 PM

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 · Mar 27th, 2002, 01:38 PM

Quote:

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 · Mar 27th, 2002, 01:42 PM

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

bobmc · Mar 27th, 2002, 01:42 PM

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 · Mar 27th, 2002, 02:02 PM

Quote:

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 · Mar 27th, 2002, 02:06 PM

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 · Mar 28th, 2002, 01:21 PM

Thanks for the help!!
It worked!

Aladin Akyurek · Mar 28th, 2002, 03:29 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 01:25 PM	#1
icka New Member Join Date: Mar 2002 Posts: 5	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 ]

Mar 27th, 2002, 01:34 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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

Mar 27th, 2002, 01:38 PM	#3
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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)

Mar 27th, 2002, 01:42 PM	#5
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	Opps, I had a typo.....offset is 2, not 1 and I forgot the ,false also.........sheeesh you guys are quick.

Mar 27th, 2002, 01:42 PM	#6
bobmc Board Regular Join Date: Mar 2002 Posts: 142	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

Mar 27th, 2002, 02:06 PM	#8
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	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.

Mar 28th, 2002, 01:21 PM	#9
icka New Member Join Date: Mar 2002 Posts: 5	Thanks for the help!! It worked!

Mar 28th, 2002, 03:29 PM	#10
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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