Sep 11 2017
10:53 PM
- last edited on
Jul 25 2018
10:03 AM
by
TechCommunityAP
Sep 11 2017
10:53 PM
- last edited on
Jul 25 2018
10:03 AM
by
TechCommunityAP
I am creating data tables for sensor readings taken each hour, each day, for a month. Sometimes data is missed due to recalibrations, resulting in a zero/blank value. I am hoping to automatically take an average of the hour before and hour after of zero value cells. Is this possible? Is it possible to also retain linked data at the same time? My research got me to this formula:
=if(Water201701!$C2=0,average(offset(0,-1,0),offset(0,1,0)),Water201701!$C2)
Also, can this easily be applied across multiple cells so that my referenced cells adjust accordingly ($C3, $C4, ...)?
Sep 12 2017 10:00 AM
I was able to get this to work for what I need. Here is the formula base I used.
=IF('Gas201701'!$C30=0,AVERAGE(OFFSET(C7,-1,0),OFFSET(C7,1,0)),'Gas201701'!$C30)
C7 is the cell the formula is applied to, and C30 is the referenced value.
Hope this helps someone in the future.