Formula...don't know where to start
I have 3 columns. The cells in each column can have a value of Low, Medium, or High. In a fourth column I want to return a value based the combination of Low, Medium, or High selected in the other three cells. So:
Two or more highs = 4
One high and two mediums = 4
One high, one med, one low = 2
Three mediums = 2
Two mediums and one low = 1
One medium and two lows = 0.5
Three lows = 0.25
This formula far exceeds my abilities. Any takers?
Best Answer
-
Lets start here...
First we will determine the count of each:
=COUNTIFS([First Column]@row:[Last Column]@row, "High")
=COUNTIFS([First Column]@row:[Last Column]@row, "Medium")
=COUNTIFS([First Column]@row:[Last Column]@row, "Low")
Next we set them up in order to make sure we have everything accounted for:
4:
COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2
2:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1
(we will leave that as is - if there is only one high and only one medium, then the third MUST be low so there is no need to clutter things up by specifying that)
1:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1
.5:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Next we will use AND and OR statements to pull each together.
4:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2))
2:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1))
1:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1)
.5:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2)
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Now we create our IF's and nest them.
If the criteria for 4 is true, output 4:
=IF(criteria for 4, 4,
If 4 is false then check 2. If true, output 2:
=IF(criteria for 4, 4, IF(criteria for 2, 2,
If 2 is false then check 1. If true, output 1:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1,
If 1 is false then check .5. If true, output .5:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5,
If .5 is false then check .25. If true, then output .25:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
.
Finally we drop in our criteria for each portion:
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3, .25)))))
And there you have it.
Answers
-
Lets start here...
First we will determine the count of each:
=COUNTIFS([First Column]@row:[Last Column]@row, "High")
=COUNTIFS([First Column]@row:[Last Column]@row, "Medium")
=COUNTIFS([First Column]@row:[Last Column]@row, "Low")
Next we set them up in order to make sure we have everything accounted for:
4:
COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2
2:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3
or
COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1
(we will leave that as is - if there is only one high and only one medium, then the third MUST be low so there is no need to clutter things up by specifying that)
1:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1
.5:
COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1 AND COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Next we will use AND and OR statements to pull each together.
4:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2))
2:
OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1))
1:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1)
.5:
AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2)
.25:
COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3
.
Now we create our IF's and nest them.
If the criteria for 4 is true, output 4:
=IF(criteria for 4, 4,
If 4 is false then check 2. If true, output 2:
=IF(criteria for 4, 4, IF(criteria for 2, 2,
If 2 is false then check 1. If true, output 1:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1,
If 1 is false then check .5. If true, output .5:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5,
If .5 is false then check .25. If true, then output .25:
=IF(criteria for 4, 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
.
Finally we drop in our criteria for each portion:
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(criteria for 2, 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(criteria for 1, 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(criteria for .5, .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(criteria for .25, .25)))))
=IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "High") >= 2, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2)), 4, IF(OR(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 3, AND(COUNTIFS([First Column]@row:[Last Column]@row, "High") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1)), 2, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 2, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 1), 1, IF(AND(COUNTIFS([First Column]@row:[Last Column]@row, "Medium") = 1, COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 2), .5, IF(COUNTIFS([First Column]@row:[Last Column]@row, "Low") = 3, .25)))))
And there you have it.
-
Paul, you are a marvelous human being! I expected this to go unanswered. Not only did you provide the solution, you provided step-by-step guidance on how to get there. I am flabbergasted! Thank you so much. You put so much time and effort helping a complete stranger. Thank you. Thank you.
-
Happy to help! 👍️
The breakdown actually serves a few purposes...
- It helps you understand exactly how it works and why.
- It helps build out the more complicated final solution.
- It makes trouble shooting much easier.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
