Excel Formula to Check Whether a Quantity Divides Evenly by 6
Question details
Determine whether a quantity divides evenly by 6 and return the number of boxes, or zero if it does not.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A quantity represents items packed in boxes of six.
- Observed behavior
- The desired result is the whole number of boxes when the quantity divides evenly by 6; otherwise, the result should be zero.
Enter the quantity as a number in a worksheet cell. The examples below use cell A1 and assume each box contains six items.
Use IF and INT to Check the Box Count
Compare the quotient with its integer value and return the box count only when it is whole.
Type the quantity in cell A1, such as 18.
Select an empty cell, enter =IF(A1/6=INT(A1/6),A1/6,0), and press Enter.
Change the value in A1 to test the formula: 6 returns 1, 18 returns 3, and 3 returns 0.

Use MOD to Check for a Remainder
Use this alternative if you prefer to test whether dividing the quantity by 6 leaves a remainder.
Check Divisibility and Calculate Boxes in WPS Spreadsheets
Use the same IF and INT formula in WPS Spreadsheets to return a box count when a quantity divides evenly by 6, or zero otherwise.
- 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the quantity: Type the quantity in cell A1.
- 3. Add the formula: Select an empty cell, enter =IF(A1/6=INT(A1/6),A1/6,0), and press Enter.
- 4. Save the workbook: Select Menu > Save As and choose an Excel-compatible format such as XLSX if needed.

Frequently Asked Questions
How do I return text instead of a box count?
With the quantity in A1, enter =IF(A1/6=INT(A1/6),"Whole","Not Whole") in an empty cell.
How can I use a box size other than six?
Replace both instances of 6 in =IF(A1/6=INT(A1/6),A1/6,0) with your box size. For example, use 12 for boxes of 12.
Why does a quantity of 3 return zero?
Three divided by six is 0.5, not a whole number. The IF formula therefore returns zero rather than a partial box count.
Can I apply the formula to multiple rows?
Enter the formula next to the first quantity, then drag the cell's fill handle down. Excel adjusts the A1 reference for each row.




