I have a square tank with a sight glass that measures the level in the tank in inches.
The volume of the tank is 550 gallons. Sight glass has a maximum reading of 71 inches.
1 inch is equal to 8.7 gallons.
A reading is taken daily and I am using the previous day reading minus the current day reading to calculate the total inches, multiplied by gallons per inch which give me the total gallons used.
(Prev Day - Current Day)*8.7 = total gallons used however....
When the level gets down to 5 inches, the tank is refilled.
How do I handle the formula due to the previous day being greater than the current day after the tank is refilled?
Example:
Prev Day level: 5 (tank is refilled this day) Current Day level is 50.
(Prev Day - Current Day)*8.7 = -391.50 (Negative incorrect value)
If I use the if statement like this,
IF(Prev Day - Current Day)*8.7 < 0, ((Current Day - Previous Day)*8.7), ((Previous Day - Current Day)*8.7) = 391.50
This only converts the negative to a positive with an incorrect value.
Is there a proper way to handle this scenario in a formula?
Thanks!
