Forum Discussion
Averaging data around cell in table, if cell equals zero
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, ...)?
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.
