Need help on IF formula calculation

kburke

Hi! I have a sheet that calculates Priority based on how Volume and Complexity are answered (out of Low/Medium/High). It's no doubt probably bloated, but working perfectly.

HOWEVER, I need the end user to better understand what constitutes volume being considered "Low". What I would really like to do is change the dropdown options on Volume from Low, Medium, High to:

• Low (<10/month),
• Medium (11-20/month),
• High (>21/month).

Likewise, I'd like to change the dropdown options on Complexity from Low, Medium, High to:

• Low (involves only legal)
• Medium (involves 1-2 other business units)
• High (involves 3+ business units).

Unfortunately, that breaks my formula. I've tried to fix it in a couple different ways, but none are working.

If there is a different way to provide guidance to the user without making the criteria part of the dropdown, I'm open to that too.

Here's the formula:

=IF(AND(Complexity@row = "Low", Volume@row = "High"), "High", IF(AND(Complexity@row = "high", Volume@row = "Low"), "Low", IF(AND(Complexity@row = "medium", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Low"), "Quick Win", IF(ISBLANK(Complexity@row), " ", "Low"))))))))))

Paul Newcome

I would suggest dropping the formula excluding the very first equals (so that it is just a text string) into another cell.

Then you can use CTRL + F and use a find and replace to swap all instances of

Complexity@row = "Low"

with

Complexity@row = "Low (involves only legal)"

Using the find/replace method ensures you don't miss anything and that you don't accidentally grab a parenthesis or quote by accident. Then you can do this for the other 5 swaps you want to make but be sure to include the cell references in the find/replace so that you aren't replacing all instances of "Low" with "Low (involves only legal)" because that will grab the Volume "Low" as well.

kburke

I would suggest dropping the formula excluding the very first equals (so that it is just a text string) into another cell.

I am sorry, but I'm not quite sure what you mean here. Do you mind giving me an example? I'm new at Smartsheet and a bit lost.

Sing C

Hi @kburke,

We've come across this very situation numerous times in our business. The way we've addressed it is to user so-called helper columns.

If you're happy with the logic in the formula for Priority (I haven't looked at it), you could take a helper column approach by referencing helper columns in the Priority formula and set the value of the helper columns using formulas based on the longer values you want users to select from.

Easier to explain with a screenshot:

Taking the Volume column as the example, this is the column with dropdown values that users select from, so they have the guidance you want in order for them to make the correct selection. The Volume-Helper column uses an IF formula to set its value to L, M, or H based on what the user selected in the Volume column:

=IF(Volume@row = "Low (<10/month)", "L", IF(Volume@row = "Medium (11-20/month)", "M", IF(Volume@row = "High (>21/month)", "H", "")))

Same approach with the Complexity and Complexity-Helper columns:

=IF(Complexity@row = "Low (involves only legal)", "L", IF(Complexity@row = "Medium (involves 1-2 other business units)", "M", IF(Complexity@row = "High (involves 3+ business units)", "H", "")))

You would then update the formula in the Priority column to set the priority based on the combinations of these helper columns instead of the user-facing columns. You can then hide the helper columns once you're happy that it's working.

I hope that helps. Give it a try and let me know how you get on.

Have a wonderful day!

Thanks,

Sing

Paul Newcome

Here is your existing formula:

=IF(AND(Complexity@row = "Low", Volume@row = "High"), "High", IF(AND(Complexity@row = "high", Volume@row = "Low"), "Low", IF(AND(Complexity@row = "medium", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Low"), "Quick Win", IF(ISBLANK(Complexity@row), " ", "Low"))))))))))

Because the very first character is an equals, it is considered a formula. If you remove the very first equals:

IF(AND(Complexity@row = "Low", Volume@row = "High"), "High", IF(AND(Complexity@row = "high", Volume@row = "Low"), "Low", IF(AND(Complexity@row = "medium", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "High", Volume@row = "High"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Medium", Volume@row = "Medium"), "Medium", IF(AND(Complexity@row = "Low", Volume@row = "Low"), "Quick Win", IF(ISBLANK(Complexity@row), " ", "Low"))))))))))

It will be a text string and will show exactly that in the cell instead of trying to run it as a formula. Since it is now a text string, you can use the find/replace on it. Once find and replace is done, you can copy it, paste it where you want it, add the equals sign back on to the beginning to turn it back into a formula, and move on to your next project.

kburke

THANK YOU!! This is brilliant and is working perfectly!

Paul Newcome

Happy to help. 👍️