Calculate Difference Between Latest Entry and Previous Entry (Dates)

AshleyOrd

Hello! 

We are collecting weekly meter reading data (Meter Name, Reading Date, Actual Reading) via webform. 

I would like to create a calculation in a new column (within the sheet) that automatically calculates the weekly meter usage based on the meter name using current and previous entries by date.  *For each Meter Name: Calculate Weekly Usage = current reading  - previous reading (readings in the 1 column by reading date)* I am not sure how to make this if statement using dates and meter name criteria... I will note that the readings are not always exactly 7 days apart. 

We would also like to eventually create alerts for when usage variance % goes over a threshold. How do I do that?

Shaine Greenwood

Hello,

From your screenshot, it appears that you only have one Meter Reading column. All readings (current and recent) must exist in cells in your sheet. There currently isn't a way to use the cell history (previous reading) to perform calculations.

To accomplish what you need, you'll need to add another column in your sheet that tracks the prior reading. You'll then be able to create an IF statement that will take the difference between the two depending on your meeting reading.

=IF([Meter Name & Number]1, "Natural Gas Meter # 0000000", [Meter Reading]1 - [Prior Meter Reading]1)

You can then create an alert that will notify you when your meter reading or your difference column reaches a certain value. More on notifications here: https://help.smartsheet.com/articles/542904-using-notifications