0

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?

Improve this question

2 Answers 2

Reset to default
1

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

=if(int(A2/3)=(A2/3)=False,RoundUp(A2/3),(A2/3))

Column C - Section 2

=if(int((A2-B2)/2)=((A2-B2)/2)=False,ROUNDup((A2-B2)/2),((A2-B2)/2))

Column D - Section 3

=if(int(A2/3)=(A2/3)=False,ROUNDDOWN(A2/3),(A2/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.

Improve this answer
0

@DimensionDatacraft provided a perfectly acceptable and serviceable solution. The following is not as concise as that of @DimensionDatacraft but it is provided as an interesting alternative.

You want to divide the number of students by three. This brings to mind the Divisibility test for 3.

  • If the sum of the digits of a number is divisible by 3, then the number is divisible by 3.

Sum of the Digits (SOTD)

  • =SUMPRODUCT(MID(B1,ROW(OFFSET($E$1,,,LEN(B1))),1)+0) where Cell B1 holds the number.

Is the SOTD divisible by three? (i.e. the remainder is zero)

  • =IF(MOD(B1,3)=0,"Divisible","Non-Divisible") where Cell B2 holds the sum of the digits

Remainders:

  • 0: SOTD is divisible by three
  • 1: SOTD is NOT divisible by three - the remainder is 1
  • 2: SOTD is NOT divisible by three - the remainder is 2

Quotient: SOTD divided by 3

  • =QUOTIENT(B1,3).

Working out the Results

  • SOTD remainder=0: #1=Quotient, #2=Quotient, #3=Quotient

  • SOTD remainder=1: #1=Quotient plus one, #2=Quotient, #3=Quotient

  • SOTD remainder=2: #1=Quotient plus one, #2=Quotient plus one, #3=Quotient

Overview

Summary

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.