Question about if statement or formula?
Question about if statement or formula?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Question about if statement or formula?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,809
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Guest

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:-

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

    HTH,
    D

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or try this

    =OFFSET(A1,B2,0)

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

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,809
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com