Excel: Dividing numbers and using the remainders

Question

Little issue I'm having that I'm hoping someone can help me with please?

So I have 3 columns in Excel. Each Column (A/B/C) contains either "high" / "Medium" / "Low" scored issues. However, if you have 3 Low issues, this is grouped together, and this becomes 1 Medium Issue for example.

The difficulty I'm having is writing a formula that will do this for me. Obviously I could just divide the number of Low issues I have by 3, but in the case where I have 7 Low issues, It should result with 2 Mediums and 1 remaining Low. I've tried using the "Mod" function, but that only returns the remainder.

What I need is a formula that will say "If you have 7 Low Issues, (3 low = 1 medium), therefore you have 2 medium and 1 Low). The medium issues would then be added to the Medium Column (Col B), and the remaining low issue is counted in the Low issue column (Col C).

I hope this explanation makes sense, fingers crossed one of you might be able to help me! Thank you in advance

As requested, a screenshot!

Answer 1

It's exactly like a long addition that you do at school where each column carries over to the one to the left of it (except base 3 instead of base 10). I'm not clear that existing answers cover the case where there is a carry from one column and that causes a further carry from the next column so here is another answer

In the totals row (e.g. for the medium column) in (say) C12

=COUNTA(C2:C10)+INT(D12/3)

Then use mod as before

=MOD(C12,3)

except that in the high column you don't want to use MOD so it's just

=B12

Answer 2

Sounds like you were already nearly there with using =MOD() just needed a little tweak:

For the high column:

=COUNTA(A2:A8)+FLOOR(COUNTA(B2:B8)/3,1)

For the medium column:

=FLOOR(COUNTA(C2:C8)/3,1)+MOD(COUNTA(B2:B8),3)

For the low column:

=MOD(COUNTA(C2:C8),3)

Answer 3

If I understand you correctly, I think you should be able to adapt the following formulas to meet your needs.

To get the number of occurrences of the word "Low" in column A:

=COUNTIF(A:A, "=Low")

To get the number of "Mediums" from 3 occurrences of "Low" in column A, round down the above number divided by 3:

=FLOOR(COUNTIF(A:A, "=Low")/3,1)

To get the remaining "Lows" after groupings of 3 into "Mediums", use MOD:

=MOD(COUNTIF(A:A, "=Low"),3)

Putting this into a worksheet:

Values

Formulas

Finally, if you wanted one "Mediums" count, i.e. adding the remaining "Mediums" which aren't grouped into "Highs", you would use a combination of the above formulas for what is left after grouping to "Highs" with what is gained from grouping of "Lows".

---

Edit:

Now you've included an image, I can show how these formulas are directly applicable...

Values

Formulas