Calculation Issues

Calculate Excel Meter Usage When Digits Roll Over

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a reliable formula to calculate the difference between six-digit meter readings when individual digits are stored in separate cells, specifically to prevent errors during digit rollovers.

Product: Excel
Device & OS: not provided
Scenario: Tracking meter usage by subtracting previous readings from current readings where digits are inputted across multiple columns.
Observed behavior: Subtracting the digits individually column-by-column yields incorrect mathematical results whenever one or more digits roll over (e.g., from 9 to 0).

Before you start

Ensure your single-digit meter readings are stored consecutively in adjacent columns without any empty cells in between, and that your previous and current readings are aligned in corresponding rows.

Solution 1Recommended

Use the CONCAT Function to Combine Digits Before Subtracting

Combine the separated digits into a single complete number using the CONCAT function to allow Excel to evaluate the total value and perform accurate subtraction.

By merging the individual cells into one continuous text string and forcing it into a numeric value, Excel bypasses single-digit rollover logic and simply subtracts the smaller total number from the larger total number.

Select the Result Cell

Click on the cell where you want the final meter usage difference to be displayed (for example, G4).

Enter the Subtraction Formula

Type the formula =--CONCAT(A4:F4)-(--CONCAT(A3:F3)) into the formula bar. Replace A4:F4 with the range containing your current reading, and A3:F3 with the range containing your previous reading.

Calculate the Result

Press Enter. The formula will combine the digits, convert them to numbers, and subtract the previous reading accurately.

Double Unary Minus (--): The double dash (--) used before the CONCAT function forces Excel to convert the joined text string into a recognizable numeric value, preventing #VALUE! errors during subtraction.

Solution 2

Store Readings as Single Numbers

Instead of splitting a meter reading into six individual columns, input the entire reading into a single cell to natively support basic subtraction without complex formulas.

Efficient Spreadsheet Calculations

Calculate Meter Usage Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in text and math functions, including CONCAT, to help you accurately track and calculate meter usage without encountering rollover errors.

1. Open your Data: Launch WPS Spreadsheet and open the document containing your separated meter readings.
2. Apply the Formula: Click on your target cell and input =CONCAT(A4:F4)-CONCAT(A3:F3).
3. Get the Accurate Total: Press Enter to instantly view the correct usage difference, completely bypassing rollover miscalculations.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Comprehensive library of functions for handling complex text and numeric data.User-friendly interface suitable for data entry and advanced analysis.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does subtracting cell-by-cell fail when digits roll over?

When a digit rolls over from 9 to 0, subtracting the corresponding previous digit (e.g., 0 minus 9) results in a negative number for that specific column. This disrupts the carrying mechanism needed for the total usage calculation, leading to an incorrect final result.

Can I use the CONCATENATE function instead of CONCAT?

Yes, but CONCAT is highly recommended because it easily accepts a range of cells (like A4:F4). The older CONCATENATE function requires you to select each cell individually (e.g., =CONCATENATE(A4, B4, C4, D4, E4, F4)), making the formula much longer.

How can I display single digits across cells if I store the whole reading in one cell?

If your full reading is in cell A1, you can use the MID function in adjacent cells to extract each digit. For example, use =MID($A$1, 1, 1) to show the first digit, =MID($A$1, 2, 1) for the second digit, and so on.

Excel & Data Problems