Excel: Dividing numbers and using the remainders

0
votes

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!

what im hoping for!

3
excelexcel-formulaformula
This is very hard to follow without seeing the data and expected results – SierraOscar
The data is very simple, its just 3 Columns A/B/C, with a number of "high" in col A, "Medium" in Col B, ""Low" in Col C. I can count the numbers of Highs / meds/lows easily enough, but I then need to perform this action where 3 Lows = Med, 3 Med = 1 High. What I just can't work out is how to divide the number of lows I have in column c, and where there is a remainder keep the count in column c while 'moving' the groups of 3 into column B. – JavaStarta
The Mod() function will return the remainder, if you do a conditional sum on the severity and they get you could apply the mod function. But as Macro Man said it is hard to offer too much more with a little more insight. – Captain Grumpy
From you adding info, if you are talking about moving the data between columns as grouped data you will need to get your coding fingers warmed up (although there might be some built in functionality I haven't used there) – Captain Grumpy
The mantra "A picture is worth a thousand words" comes to mind here... You can edit your question and add a screenshot. – Wolfie

3 Answers

1
votes

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

Values

Formulas 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

values

Formulas

enter image description here

0
votes

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)
0
votes

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

enter image description here