Meter reading log in Excel: which reading is over the limit
A meter reading log is one row per reading of one meter. You type the meter, the fluid word, a date, the previous index, the current index, and the limit for that row. Use is current minus previous when the current index is at least the previous one and the row is kept. The flag says high when that use is above the limit, and within when it is not. The first screen is a sheet named Meters. Row 1 is the header. Row 2 is one reading you can check by hand: previous 1000, current 1180, limit 150, status kept. Use is 180. The flag is high. There is no mileage and no room count on that screen. This sheet is not a vehicle log. A fleet sheet answers which vehicle is free. This list answers which building meter used more than the limit on the two indexes you typed. It is not a hotel occupancy rate. A current index below the previous one stays blank. You type both indexes. The question is which kept reading is already above the limit. Dropped rows stay on the sheet and leave the use sum. Elyx can write the use and the flag. You still type both indexes and the limit. The trial lasts 7 days, with no card.
The Problem
The round fails when it lives on a photo. A picture of a meter shows one number. Nobody can say the use, because the previous index is not on the same row. A picture without the two indexes and the limit is not a reading log. A second failure is mixing this sheet with a vehicle log or a hotel sheet. A vehicle row answers mileage and whether the vehicle is free. A hotel row answers how many rooms were occupied. If you paste odometers or room nights here, the morning sum no longer means use on the meters you read. Keep this sheet to meters, fluid words, and the two indexes. A third failure is forcing a consumption when the current index went backwards. Previous 1000 and current 900 is not a use of 100 the other way. The use stays blank until you fix the cell that is wrong. Dropping a bad reading keeps the row and removes it from the sum.
Benefits
Each reading is one row with a previous index, a current index, and a limit you typed.
Use is current minus previous when the index moves forward and the row is kept.
High means use is above the limit. Within means it is not.
A backwards index blanks the use instead of inventing a consumption.
A dropped row stays on the sheet and leaves the fluid sum.
Step-by-Step Tutorial
Create the reading log
Name the sheet Meters. In row 1 type: Meter, Fluid, Date, Previous, Current, Limit, Status, Use, Flag, Note. Meter is the meter you read. Fluid is a word such as elec or water. Date is a real date you type, and it is not used by the use formula. Previous and Current are indexes you typed. Limit is the number you do not want use to pass. Use and Flag are formulas. Status is a word from a list. Freeze row 1. Do not merge cells.
One row per reading. A second visit to the same meter is a second row. Type the previous index on that row. Do not look it up from the row above.
Lock the status words and the fluid words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is kept and drop. Kept means this reading counts. Drop means you are keeping the row but leaving it out of the sum. Select Fluid. The source is elec, water, and gas. People must pick a word. A synonym such as electricity or ignore will not match the formulas. The fluid is a filter. It is not a tariff.
Previous, Current, and Limit are numbers. Do not type the word high in those columns.
Enter four readings you can check by hand
On row 2 type meter M-E1, fluid elec, any date, previous 1000, current 1180, limit 150, status kept. You will predict use 180 and flag high, because 180 is above 150. On row 3 type meter M-E1, fluid elec, previous 1000, current 1120, limit 150, status kept. Use should be 120 and flag within, because 120 is not above 150. On row 4 type meter M-W2, fluid water, previous 1000, current 900, limit 150, status kept. Use and flag should stay blank, because 900 is below 1000. On row 5 type meter M-E1, fluid elec, previous 1000, current 1180, limit 150, status drop. Use and flag should stay blank even though 1180 is above 1000. Change the date on row 2 and use should stay 180.
If you do not have the previous index, leave Previous blank. Use stays blank. Do not type zero unless the index really was zero.
Write the use
Use is blank when status is drop, when either index is blank, or when Current is below Previous. Otherwise it is Current minus Previous. With Previous in D2, Current in E2, and Status in G2, use =IF(OR(G2="drop",D2="",E2="",E2<D2),"",E2-D2). Format as a number. 1180 minus 1000 is 180. 1120 minus 1000 is 120. 900 against 1000 does not become a use. The date and the fluid are not in this formula.
=IF(OR(G2="drop",D2="",E2="",E2<D2),"",E2-D2)A blank use on a backwards reading is the result you want. Do not type 100 by hand to fill the cell.
Write the flag
The flag is blank in the same cases as use, and also when Limit is blank. Otherwise it is high when Current minus Previous is above the limit, and within when it is not. Use =IF(OR(G2="drop",D2="",E2="",F2="",E2<D2),"",IF(E2-D2>F2,"high","within")). High means the use passed the limit you typed on that row. Within means it did not. 180 against a limit of 150 is high. 120 against the same 150 is within. The fluid is not in this formula.
=IF(OR(G2="drop",D2="",E2="",F2="",E2<D2),"",IF(E2-D2>F2,"high","within"))The limit is per row. A water row can have a different limit from an elec row. Type the number on the row.
Add use for one fluid
On a sheet named Morning, high rows are =COUNTIFS(Meters!I:I,"high"). With the rows above, that count is 1: the use of 180. Elec use is =SUMIFS(Meters!H:H,Meters!B:B,"elec"), which is 300, because 180 and 120 are the only elec uses. The water row is blank, so it adds nothing. The dropped elec row is blank, so it adds nothing. Within rows are still in the elec sum, because the sum reads the fluid, not the flag. Write that rule next to the 300.
=SUMIFS(Meters!H:H,Meters!B:B,"elec")If you want only high elec use, add the flag: =SUMIFS(Meters!H:H,Meters!B:B,"elec",Meters!I:I,"high"). On this sample that sum is 180, not 300.
Leave a backwards reading blank
Previous 1000 and current 900 is not a consumption. Use and flag stay blank while status is kept. Either the current index was misread, or the previous index on that row is not the index you meant. Fix the cell that is wrong. Do not delete the row to hide it, and do not type a positive use over the formula. A dropped row with 1180 and 1000 is also blank, on purpose, so a reading you set aside does not enter the 300. If a kept forward reading shows a blank use, the formula was overwritten.
Filter Flag to high for the morning list. Filter Fluid to water when you only want that word. Filter Status to drop when you need the readings you set aside.
Check the four readings before you share the sum
Previous 1000, current 1180, limit 150, status kept: use 180, flag high. Previous 1000, current 1120, limit 150, status kept: use 120, flag within. Previous 1000, current 900, limit 150, status kept: use blank, flag blank. The same 1000 and 1180 with status drop: use blank, flag blank. Elec use is 300. The high count is 1. Changing the date does not move use. If a check fails, fix the indexes or the status word before you send the sum. The sum is only this sheet. It is not mileage, and it is not an occupancy rate.
Write the four checks on Morning if more than one person types the round.
Template Features
Use when the index moves forward
Current minus previous. Blank when the row is dropped, an index is empty, or current is below previous.
=IF(OR(G2="drop",D2="",E2="",E2<D2),"",E2-D2)Flag against that row's limit
High when use is above the limit you typed. Within otherwise. Blank when use is blank.
=IF(OR(G2="drop",D2="",E2="",F2="",E2<D2),"",IF(E2-D2>F2,"high","within"))High count
COUNTIFS reads the flag. With the sample rows the count is 1.
=COUNTIFS(Meters!I:I,"high")Use by fluid
SUMIFS adds use for the fluid word. The sample elec sum is 300.
=SUMIFS(Meters!H:H,Meters!B:B,"elec")A dropped reading stays visible
Drop blanks the use and leaves the indexes on the sheet.
Concrete Examples
180 against a limit of 150
Meter M-E1 moved from 1000 to 1180. The limit on that row is 150. You want it on the morning list.
Previous: 1000. Current: 1180. Limit: 150. Status: kept. Fluid: elec.
Result: Use is 180. Flag is high. The high count includes the row. The 1000, the 1180, and the 150 are the numbers you typed.
120 stays within and still adds to elec
The same meter, on another row, moved from 1000 to 1120 with the same limit of 150.
Previous: 1000. Current: 1120. Limit: 150. Status: kept. Fluid: elec.
Result: Use is 120. Flag is within. The high count stays 1. Elec use is 300, which is 180 plus 120.
A backwards water index
Meter M-W2 has previous 1000 and current 900. You do not want a consumption invented from that pair.
Previous: 1000. Current: 900. Limit: 150. Status: kept. Fluid: water.
Result: Use is blank. Flag is blank. Elec use stays 300. Setting a bad elec row to drop also blanks its use.
Pro Tips
Type the previous index on the row
Each row carries both indexes. A formula that looks up the prior visit breaks when someone sorts the sheet. 1180 minus 1000 is readable on the row itself.
The date does not change the use
Type any real date. Change it and the 180 stays. This sheet is not a calendar of due readings.
Text indexes do not subtract
If Current was pasted as text, E2-D2 will not be 180. Re-enter 1000 and 1180 as numbers. Then read the flag again.
Distinct from a vehicle log
Mileage and availability belong on the fleet sheet. Room nights belong on the hotel occupancy sheet. Do not paste those rows here and expect this sum to mean meter use.