Divide Excel Values into Six Sections and Halves with a Formula
Question details
Classify values in an Excel column into six sections and identify the half of each section.
- Product
- Microsoft Excel 2010
- Device & OS
- not provided
- Scenario
- The user wants a formula that assigns each number to a section and labels it as being in the first or second half.
- Observed behavior
- The desired output is a section number and half label for each value, based on specified boundaries.
Decide the minimum and maximum values and whether the sections should have equal numeric widths. The example below assumes numeric values in column A and a maximum greater than the minimum.
Classify values into six equal-width sections in Excel 2010
Use this formula when the full range should be divided into six equal numeric intervals, each with two equal halves.
The formula treats each half as a lower-inclusive, upper-exclusive interval, except that the maximum value is included in the final half. This divides the numeric range evenly, not necessarily the number of rows.
Enter the minimum in E2 and the maximum in E3. For example, enter 0 in E2 and 120 in E3.
With your first value in A2, enter this formula in B2: =IF(A2="","",IF(OR(A2<$E$2,A2>$E$3),"Out of range","Section "&MIN(6,INT((A2-$E$2)/($E$3-$E$2)*6)+1)&" - "&IF(MOD(MIN(11,INT((A2-$E$2)/($E$3-$E$2)*12)),2)=0,"First half","Second half")))
Select B2 and drag its fill handle down alongside the values in column A.
With boundaries 0 and 120, check that 0 returns Section 1 - First half, 10 returns Section 1 - Second half, and 120 returns Section 6 - Second half.
Classify spreadsheet values into sections with WPS Office
Open your workbook in WPS Spreadsheet and use the same equal-width formula to label each value by section and half.
- 1. Open the workbook: Open WPS Office, select Spreadsheet, and open the workbook containing your values.
- 2. Set the range: Enter the minimum value in E2 and a larger maximum value in E3. Put the numbers to classify in column A, starting at A2.
- 3. Add the formula: Enter this formula in B2: =IF(A2="","",IF(OR(A2<$E$2,A2>$E$3),"Out of range","Section "&MIN(6,INT((A2-$E$2)/($E$3-$E$2)*6)+1)&" - "&IF(MOD(MIN(11,INT((A2-$E$2)/($E$3-$E$2)*12)),2)=0,"First half","Second half")))
- 4. Copy the formula down: Select B2 and drag the fill handle down to classify the remaining values.
Frequently Asked Questions
What happens when a value is exactly on a half-section boundary?
It is assigned to the half that starts at that boundary. For example, with a range of 0 to 120, 10 starts the second half of Section 1.
Is the maximum value included?
Yes. A value equal to E3 is labeled Section 6 - Second half. Values below E2 or above E3 are labeled Out of range.
Can I use this formula for unequal sections?
No. The example divides the range between E2 and E3 into equal widths. For unequal sections, list the desired boundaries and labels in a helper table and use a lookup formula based on those boundaries.
Why does the formula return a division error?
Check E2 and E3: the maximum in E3 must be greater than the minimum in E2. Equal boundaries would make the formula divide by zero.
