Formula Errors

Excel Formula to Check Whether a Quantity Divides Evenly by 6

Ayan MasoodAyan Masood Nov 20, 2026 868 views

Question details

Determine whether a quantity divides evenly by 6 and return the number of boxes, or zero if it does not.

Check Whether a Quantity Divides Evenly by 6 in Excel
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.
Before you start

Enter the quantity as a number in a worksheet cell. The examples below use cell A1 and assume each box contains six items.

Solution 1Recommended

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.

1
Enter the quantity

Type the quantity in cell A1, such as 18.

2
Enter the formula

Select an empty cell, enter =IF(A1/6=INT(A1/6),A1/6,0), and press Enter.

3
Check the result

Change the value in A1 to test the formula: 6 returns 1, 18 returns 3, and 3 returns 0.

Use IF and INT to Check the Box Count
Use a different cell or box size: Replace A1 with the cell containing your quantity and replace both instances of 6 with the number of items per box.
Calculate box counts in WPS Spreadsheets

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. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the quantity: Type the quantity in cell A1.
  3. 3. Add the formula: Select an empty cell, enter =IF(A1/6=INT(A1/6),A1/6,0), and press Enter.
  4. 4. Save the workbook: Select Menu > Save As and choose an Excel-compatible format such as XLSX if needed.
Check quantities and calculate whole box counts with a single formula.Open and edit Microsoft Excel XLS and XLSX files.Use familiar worksheet cells and formula entry tools.
microsoft office alternative - wps office

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.