How to reference a pervious row in a Math calculation

Hi.
I’m trying to build a tool to assist me with monitoring wastewater meter readings.
I use a form to collect daily readings.
I want to create a row that will calculate and display the daily use of wastewater.
I can’t figure out the Math format to reference a pervious row to calculate the difference.
eg. Column D Row 32 (2233) - Column D Row 31 (2220) = daily use of 13

I thought i could create a new Column E and import the value from Column D plus one row, so the data is pushed down one row. Then in Column F i could create a Math calculation of Column D - Column E.

Suggestions??

  • Start by adding a RowIndex to your table by following the second method described here.
  • Add a Math column that subtracts 1 from the RowIndex
  • Add a Single Value column. It should target your table and take the N from start Meter Reading value, where N is the Math column.

Here is how that would look:

3 Likes

Hi. That worked great.
Now I have have to calculate the difference between the “meter reading dates”. Which is the same as above. But i need to round the formula to a single number.
So that I can divide the total volume (between reading dates) by the number of days (between reading dates)

Are you trying to get the date difference in days or hours? Judging by the column name, I believe you want days. Date calculations always calculate the number of days between dates, so the division by 24 should not be part of your calculation and is actually throw off your result.

Would the difference in days be rounded to a whole day? If so, would it always round up, round down, or round up or down based on the decimal value?

1 Like

Hi.
Yeah, i am trying to calculate the different in days. My first approach gave me a calculation of hours:minutes:seconds, and this threw out an interesting result when tried to divide the total wastewater volume by the number of days … hahaha

I found an experimental date tool and using that I have been able to calculate the number of days between the date range. And it now appears to be working.

1 Like