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

Collins Nwosu

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.

Mark 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

Collins Nwosu

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!

Collins Nwosu

SS Example.PNG

Leibel 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%

Collins Nwosu

@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 S

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

Collins Nwosu

@Leibel S

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

Collins Nwosu

@Leibel S

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

Mark 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