Formula Errors

How to Split a Total into Two-Thirds and One-Third in Excel

Steven Parker Oct 19, 2026 868 views

Question details

The user needs to calculate a total cost from a daily rate and number of days, and then split that total into exact two-thirds and one-third portions without losing penny accuracy.

How to Split a Total into Two-Thirds and One-Third in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating financial or transportation totals and distributing the costs based on exact fractional splits.
Observed behavior: To correctly divide the total into exact 2/3 and 1/3 portions while ensuring the sum of the split portions equals the exact original total without rounding errors.

Before you start

Ensure your base calculation (total amount) is correct before attempting to split it into fractions. When working with currency, verify how many decimal places you need to retain to prevent accounting discrepancies.

Solution 1Recommended

Use Multiplication, Subtraction, and the ROUND Function

This is the most accurate method for splitting currency into fractions. By subtracting the rounded two-thirds amount from the total, you ensure no pennies are lost or gained due to rounding.

When splitting a sum into non-terminating fractions (like 1/3 and 2/3), simply multiplying both sides by the fraction can cause a one-cent discrepancy when added back together. To fix this, you must calculate the first portion, round it, and then subtract it from the total to get the remaining portion.

Calculate the full amount

In cell C1, enter `=A1*B1` to multiply your daily cost (A1) by the number of days (B1).

Calculate the two-thirds portion

In cell D1, enter `=ROUND(C1*2/3, 2)`. This multiplies your total by the exact fraction 2/3 and rounds the result to two decimal places for currency.

Calculate the remaining one-third portion

In cell E1, enter `=ROUND(C1-D1, 2)`. By subtracting the first split (D1) from the total (C1), you guarantee that the two splits add up perfectly to the original amount.

Use Multiplication, Subtraction, and the ROUND Function

Avoid SUM for simple multiplication: There is no benefit to using `=SUM(A1*B1)` in Excel. A simple `=A1*B1` is cleaner, more efficient, and follows standard spreadsheet practices.

Smart Spreadsheet Calculations

Perform Advanced Formula Calculations Easily with WPS Spreadsheet

WPS Office offers a fully compatible spreadsheet tool that handles financial rounding, fractional math, and complex data modeling seamlessly.

1. Open your dataset: Launch WPS Spreadsheet and open your financial or transportation workbook.
2. Set up your base calculation: Select the cell for your total and input a standard multiplication formula like `=A1*B1`.
3. Apply the fraction formula: Type `=ROUND(C1*2/3, 2)` into the next cell to accurately calculate the exact two-thirds split.
4. Find the perfect remainder: Subtract the first split from the total using `=ROUND(C1-D1, 2)` to extract the remaining third without floating-point errors.

100% compatible with Microsoft Excel formulas and rounding functionsFree, lightweight, and fast office suite for PC and mobileBuilt-in advanced math capabilities for accurate financial modeling

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I multiply by 66.67% and 33.33%?

Multiplying by approximations like 66.6667% or 33.3333% introduces small inaccuracies in your calculation. Using the exact mathematical fractions `2/3` and `1/3` ensures mathematically correct and absolute results.

Why do we subtract the first portion instead of multiplying by 1/3 for the second portion?

If you calculate both parts by multiplying (e.g., C1*2/3 and C1*1/3) and round them independently, the two results might not add up to the exact original total due to floating-point rounding anomalies. Subtracting the first part from the total guarantees the two parts will equal the whole.

Can I use the SUM function instead of just multiplying?

While `=SUM(A1*B1)` technically works without error, there is no need to use the SUM function for a simple multiplication or division task. Using `=A1*B1` is cleaner and easier to read.

How do I round to a different decimal place?

You can change the second argument in the ROUND function. For example, using `=ROUND(C1*2/3, 0)` will round the calculation to the nearest whole number, which is useful if you do not want to display or track decimal cents.

Excel & Data Problems