Formula Question: IF, OR, AND Solution Maybe? Nested? Other Logic?

01/12/21
Answered - Pending Review

What formula would work when a certain answer is selected in Column A, its result would be 10%. If that selection in Column A holds true and you select a certain answer in Column B the return value is 20%. Until you have certain answers selected in Columns C, D, E, F and G, you have 30%, 40%, 60%, 80% and 100% respectively as returns.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Collins Nwosu ,

    This is certainly possible using nested IF functions. It will get complicated fast depending upon how many possible answers there are per cell and how many columns you include. Happy to help more. Best if you could include a screenshot of your sheet and the dropdowns.

    Mark

  • So here is an example of the results I want to see for it to show 100% completed in the results column (not pictured). I doubt we need to factor the other options within the columns for a 100% completion status? My focus would be one selecting one option from each of the depicted 7 columns to mark the process 100 completed. Push comes to shove, two options from a couple out of the 7 columns.

    I muddy the waters more? I hope not and any help helps!

  • Leibel SLeibel S ✭✭✭

    @Collins Nwosu

    to keep it simple you can assign a value to each column (some are worth 10%, some 15%, and some 20%)

    Example of the formula is below, you can keep on adding to this as you wish.

    =IF([column A]@row = "correct value", 0.1) + IF([column B]@row = "correct value", 0.15)

    As well, if certain combinations add to the percentage you can do in addition to the previous formula add:

    =IF([column A]@row = "correct value", 0.1) + IF([column B]@row = "correct value", 0.15)+if(and([column A]@row = "correct value",[column B]@row = "correct value"),.05)

    In the above formula if column A and B have the correct value then the percent returned would be 30%

  • @Leibel S

    Thanks for the the feedback. I see where you are going with this. Also, what if you want an OR within Column A i.e. the correctvalueA or correctvalue B (with Column A) gives you 0.1; as an example? How would that look like?

  • Leibel SLeibel S ✭✭✭

    +if(or([column A]@row = "correct value a",[column A]@row = "correct value b"),.1)

  • @Leibel S

    I'm getting unparseable. What am I doing wrong? lol

  • @Leibel S

    Nevermind! I got it! Your suggestion worked perfectly for what I needed it to do! Thank you very much!

  • Mark CronkMark Cronk ✭✭✭✭✭

    HI @Collins Nwosu ,

    Glad you found a solution. Please accept and answer to close out this discussion. Appreciate you contributing to the Community.

    Mark

Sign In or Register to comment.