Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to create a formula if 1 of 4 conditions....

    (Please see attached sample sheet).
    I am used to creating simple formulas if one of 2 conditions is correct, using the "IF" function. I now have a situation like this: I have 4 rows where I can either input a value or a formula will calculate a value (these values will be one of these four - a fraction/a percentage/a specific number/or another number); in the 5th row, I have created an in-cell drop-down box (using data validation) where I will select one of the 4 values in the four rows above. In the 6th row, I want to create a formula which will be different if the choice selected is % (percent) vs. if a number is selected vs. a fraction is selected, etc. (To explain this in simple English, the formula will tell Excel that if cell C8 is selected, multiply that value to that in C5 and subtract the total from C6; if cell C9 is selected, subtract that from C6; if cell C10 is selected, use that value; and if cell C11 is selected, subtract that % from C6.) If there were only 2 choices, the creation of the formula would be easy; unfortunately, there are 4 choices ranging from fractions to percentages so each choice selected will need to have a specific formula applied to it. Since the "IF" function is limited to 2 choices, does anyone know which function in Excel can I use to achieve this? Will it be achieved by using IF multiple times (IF(IF(IF...))) or is there another way?

    This one has me stumped! Please see attached sheet - hope that clarifies it!
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    Hi Stumped,

    You can use nested IF statement as follows :-

    =(IF(C12=2.5,C17,(IF(C12=C9,C18,(IF(C12=C10,C19,C2 0))))))

    You could also use a lookup function and I included one on the attached as well. They both give the same result, but if you were to increase the choices the lookup would be the way to go.

    Andrew
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    I am sorry but I am confused. In the sheet I attached, I merely input values in rows 16 through 20 to illustrate how the results would differ if the choice in C13 varied from C8 through C11. Will I actually have to create formulas in such a manner in my actual spreadsheet to avail of your solutions?
    And the number in C12 could conceivably vary anywhere from 0.1 to 5, but in your formula you have used my example of 2.5. Perhaps you meant C12=C8 instead of C12=2.5?
    Also, when I make a selection in the drop down box in C12, instead of displaying the actual value selected, can it just display numbers 1 through 4 where 1 would represent choice 1 corresponding to C8, 2 would be choice 2 and its correspondence would be C9, and so forth?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    You are correct, I intended C12 = C6 - however it does not matter as I missed to whole point.
    Amended version attached. I took the liberty of changing your data validation list to 1,2,3,4 - and based on the selection used a nested IF to apply your math to the value in C6 (Today's High)

    Hope I have it right now,

    Andrew
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    I think I am beginning to get it. I like the idea of the lookup feature -> I will create the appropriate formulas and, depending on whether I place them in a different column/same rows or same column/different rows, I will use the Vlookup or Hlookup, respectively. The reason I like is that I think I may be adding more choices in the future.

    Say, how did you create those nice graphics on the sheet?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    Nice graphics ? - maybe that was the Call Out box, you get them inthe Autoshapes tab on the drawing toolbar.

    I think a Lookup would be a good idea,


    Good luck

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    Thanks, Andrew. If I have any problems, I will post again.

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    It just occurred to me...the in-cell drop-down box will select choices 1-4; can it be set to a default choice of 1, so that I will only need to cahnge it if I want 2, 3 or 4?

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    I am stuck. I created the formulas in the same column corresponding to what the results would be with each of the 4 choices, but I can't figure out how to create a proper HLOOKUP argument (see attached sheet).
    Attached Files Attached Files

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    The HLOOKUP, is not really suitable in this case. Better to use INDEX() or CHOOSE(). I have put examples of both on your workbook.

    Andrew
    Attached Files Attached Files

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    Just enter 1 in the cell - it will hold that value until changed

  12. #12
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    Thanks, Andrew. I used the CHOOSE function and it appears to be working OK.

  13. #13
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create a formula if 1 of 4 conditions....

    I don't really want to get into the complexity of your particular example but I've done something similar using AND and nested if's
    eg
    IF(AND(A1>5,B2>5),good,If(AND(A1<5,B2<5),Bad,"You Got 5"))
    or something like that. Check out AND in help

    Bob

Posting Permissions

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