Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Need help with IF, over 7 in one formula

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

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,630
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

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

    Default

    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.






  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

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

    Default

    Thanks for the help!!
    It worked!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,630
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

Some videos you may like

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
  •