Formula...don't know where to start

Mike Gilner

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?

Paul Newcome

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.