I have a total number (55, for example). I want to divide that number by three and put the result (needs to be a whole number) into three different columns. But, the numbers in the three columns need to add up to the original number.
Let me give you the context. A 3rd grade in a building has a total of 55 students. We have 3 sections of 3rd grade. I want to have 55/3 … in three columns (one for each section) and have a result of 19, 18, 18… to signify that each of the three sections will have 19 students, 18 students and 18 students. Is there a formula that will allow me to do this?
Best Answer
The way I approached this was first checking if the number is whole or not once divided. Depending on that result we either need to handle rounding the resulting number up or down. The first column handled by dividing by three and rounding up if it's not whole, the second by dividing the remaining amount by 2 and determine if whole, and the third is the remaining (or the original but rounded down).
If Cell A2 has your total number (55), and B, C, and D are your three sections, then the formulas I used follow.
Column B - Section 1
Column C - Section 2
Column D - Section 3
Here is a sheet for you to copy and explore... https://docs.google.com/spreadsheets/d/13ZjMERy3tkIPoa8hiGgVqHJkALBemgczev4fyuv2Epc/edit?usp=sharing
As always if you have a long list you can use arrayformula to expand this downward automatically. Sample of this is in the sheet as well.