Intermittent Errors on Column Formula, Report always displaying errors

Michelle St George

Why is my sheet intermittently displaying error messages & Report always displaying error messages. One minute the columns have data then randomly they all show up with the error messages?  My report consistently ONLY displays error messages.

I have a sheet performing calculations related to consumption of water based on a form submission containing Form Submission Date, Meter Reading, Car Count.  My form users aren't consistent about completing said form (sometimes they miss a day, sometimes they submit twice … see picture below)

Calculated columns are as follows…

Meter Reading (Gallons)  =IF([MeasurementType]@row = "Cubic Feet", (VALUE([MeterReading]@row ) * 7.48052),VALUE([Meter Reading]@row ))

Yesterdays Meter Reading (Gallons) =IFERROR(INDEX(COLLECT([Meter Reading(Gallons)]:[Meter Reading (Gallons)], [Full Site Name]:[Full Site Name], [FullSite Name]@row , [Form Submission Date]:[Form Submission Date], [Form SubmissionDate]@row - 1), 1), "Missing Previous Day")

Consumption (Gallons) =IFERROR([Meter Reading (Gallons)]@row - [YesterdaysMeter Reading (Gallons)]@row , "Missing Data to Calculate")

Consumption Gallon / Car =IF([Consumption (Gallons)]@row = "Missing Datato Calculate", "Missing Data to Calculate", IF(VALUE([CarCount]@row ) > 0, VALUE([Consumption (Gallons)]@row ) / VALUE([CarCount]@row )))

One minute my sheet looks like this…

Then next time I look it looks like this…

My report consistently looks like this…

Michelle St George

Unable to edit my original post so adding some other things I've tried here for more context.

I removed all error handling to see what errors I'm actually getting.

Yesterdays Meter Reading (Gallons) will return an #INVALID VALUE when there is no previous date to compare against which will cascade into the Consumption (Gallons) and Consumption Gallon / Car. The remainder of the sheet displays the correct formula results, however my report continues to display #INVALID VALUE for the columns (even those with a valid result).

Lastly, there is an additional column Gallon / Car Compared to 30 Day Rolling Average which interrogates another sheet that averages Consumption Gallon / Car over the last 30 days that is returning a #DIVIDE BY ZERO error. I've built a similar formula in the Sheet Summary of the main sheet and the formula returns a #INVALID VALUE.

Not sure if any of this helps but trying to provide enough details… @Paul Newcome - I could really use your genius here.