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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » How to create a formula if 1 of 4 conditions….

    Author
    Topic
    #354195

    (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!

    Viewing 1 reply thread
    Author
    Replies
    • #519934

      Hi Stumped,

      You can use nested IF statement as follows :-

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

      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

      • #519935

        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?

        • #519938

          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

          • #519940

            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?

            • #519941

              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

            • #519943

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

            • #519945

              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?

            • #519979

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

            • #519951

              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).

            • #519976

              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

            • #520017

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

    • #520320

      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

    Viewing 1 reply thread
    Reply To: How to create a formula if 1 of 4 conditions….

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: