I have a meter tracking excel sheet where I input a meter number on a daily basis. I skip weekends and occasionally week days. After I enter the date and time in column B (m/d/yyyy hh:mm), and the meter reading in Column C,
Column D provides the Meter delta (=IF(ISBLANK(C525),"",C525-C524)),
Column E provides the Time Delta (=IF(ISBLANK(C525),"",(B525-B524)*1440)),
Column F provides the Gallons per minute used (=IF(ISBLANK(C525),"",D525/E525)), and
Column K provides a trailing 12 month average (=IF(ISBLANK(B525),"",AVERAGE(F513:F525)))
The issue with Column K is that it averages my averages, which is not an accurate way of doing it. To put it in other words, if my average of 2 days is 10 gallons per minute (GPM) and my average over 5 days is 2 GPM, Column K will result with a trailing 12 month average of 6 GPM when it should have produced 4.28 GPM.
I'm trying to figure out how to do an averageif formula that will take a range of data, determine if the dates are within 365 days of Column B's date and then SUM Column D and divide by 365/24/60 to provide me with an accurate trailing 12-month average GPM. Any idea's what that formula would be?
EDIT: After posting and re-reading to proof my own submission, I think I need to be using SUMIF and then dividing it out for my average. I will try this and report back.
EDIT 2: This is what I came up with, but I'm not getting the result I wanted: =(SUMIFS($D$35:$D$2354,$B$35:$B$2354,">="&B525,$B$35:$B$2354,"<="&(B525-365)))/365/24/60
Still continuing to troubleshoot the above, but don't want others wasting time on this if I can figure it out.
EDIT 3: Had my greater than and less than signs reversed. Formula works: =(SUMIFS($D$35:$D$2354,$B$35:$B$2354,"<="&B525,$B$35:$B$2354,">="&(B525-365)))/365/24/60
Sorry for the useless post. I guess I just needed to read my own question to figure it out.
Bookmarks