Question about if statement or formula?

[JRyan]

I have a set of six different dollar amounts. I want to write a formula (argument) so that if I pit a number (1-6) in a cell it will put the value (Dollar amount) I want assigned to that number in the cell next to it? Is that possible.

 

[Aladin Akyurek]

On 2002-03-06 04:30, JRyan wrote:
I have a set of six different dollar amounts. I want to write a formula (argument) so that if I pit a number (1-6) in a cell it will put the value (Dollar amount) I want assigned to that number in the cell next to it? Is that possible.

How about:

=VLOOKUP(A1,{1,dol1;2,dol2;3,dol3;4,dol4;5,dol5;6,dol6},2,0)

where dol1 is the dollar amount associated with 1, dol2 the dollar amount associated with 2, etc.?

dol1, dol2,... are numbers and must be entered as constants between the braces.

If this is not what you want, try to be more specific.
This message was edited by Aladin Akyurek on 2002-03-06 06:52

 

[dk]

Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D

 

[JRyan]

I'll try to be more specific. In cell C5 I want to be able to enter a number.(1,2,3,4,5, or 6). I want the dollar amount for that number to appear in C7.
The value for 1 is $32.50
The value for 2 is $22.50
The value for 3 is $37.50
The value for 4 is $25.00
The value for 5 is $45.00
The value for 6 is $32.50

Thanks
JRyan

 

[JRyan]

On 2002-03-06 04:44, dk wrote:
Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D

 

[Guest]

I understand this one and it does exactly what I want it to. If I could just push it one step further. If I don't put a number (1 - 6)in cell C1, can I get D1 to show nothing. ( Now it shows #N/A if I don not enter in C1 )

Thanks so much to the both of you for your fast response and help. LIFE IS GOOD AGAIN!!
JRyan

On 2002-03-06 04:44, dk wrote:
Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D

 

[dk]

Try this:-

=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1,A1:B6,2,FALSE))

HTH,
D

 

[JRyan]

BINGO!!!!!
I can't thank you guys enough!
I've talked to so many people who I thought were wizards in the program and they had no idea even if it could be done....
YOU GUY'S ARE GREAT!!!
I won't loose the address for this board.
JRyan

On 2002-03-06 06:18, dk wrote:
Try this:-

=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1,A1:B6,2,FALSE))

HTH,
D

 

[Andy Gee]

Or try this

=OFFSET(A1,B2,0)

When A2:A7 contain the dollar amounts and B2 is the cell you enter the number into.

 

[Aladin Akyurek]

On 2002-03-06 05:23, JRyan wrote:
I'll try to be more specific. In cell C5 I want to be able to enter a number.(1,2,3,4,5, or 6). I want the dollar amount for that number to appear in C7.
The value for 1 is $32.50
The value for 2 is $22.50
The value for 3 is $37.50
The value for 4 is $25.00
The value for 5 is $45.00
The value for 6 is $32.50

Thanks
JRyan

=VLOOKUP(A1,{1,32.5;2,22.5;3,37.5;4,25;5,45;6,32.5},2,0)

If you want some control over what can be inputted in A1, use:

=IF(AND(ISNUMBER(A1),A1>=1,A1<=6,INT(A1 =A1)),VLOOKUP(A1,{1,32.5;2,22.5;3,37.5;4,25;5,45;6,32.5},2,0),0)

Format the cell of this formula as number with 3 decimals, if so desired.

Aladin