I'll begin by apologizing in case this topic has been covered earlier. I did quickly look through past topics, but I didn't find anything that was similar to my situation.
I'm using Excel 2007 professional and my knowledge is strictly limited to formulas ( I haven't done anything with macros as of yet, although I'm very interested in learning ). My task is to generate average hourly values for consecutive data points in a column. Sometimes more than one data point has been recorded by the dataloggers and the number of data points is never consistent but is usually between 1 and 7. A "#DIV0!" error returns when all values for a particular hour are "N/A". The formula I was using is:
=IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25106),"",IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25113),AVERAGE('Unit Conversion'!D25107:D25113),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25112),AVERAGE('Unit Conversion'!D25107:D25112),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25111),AVERAGE('Unit Conversion'!D25107:D25111),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25110),AVERAGE('Unit Conversion'!D25107:D25110),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25109),AVERAGE('Unit Conversion'!D25107:D25109),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25108),AVERAGE('Unit Conversion'!D25107:D25108),'Unit Conversion'!D25107)))))))
I appreciate the input!
I'm using Excel 2007 professional and my knowledge is strictly limited to formulas ( I haven't done anything with macros as of yet, although I'm very interested in learning ). My task is to generate average hourly values for consecutive data points in a column. Sometimes more than one data point has been recorded by the dataloggers and the number of data points is never consistent but is usually between 1 and 7. A "#DIV0!" error returns when all values for a particular hour are "N/A". The formula I was using is:
=IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25106),"",IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25113),AVERAGE('Unit Conversion'!D25107:D25113),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25112),AVERAGE('Unit Conversion'!D25107:D25112),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25111),AVERAGE('Unit Conversion'!D25107:D25111),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25110),AVERAGE('Unit Conversion'!D25107:D25110),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25109),AVERAGE('Unit Conversion'!D25107:D25109),IF(HOUR('Unit Conversion'!$C25107)=HOUR('Unit Conversion'!$C25108),AVERAGE('Unit Conversion'!D25107:D25108),'Unit Conversion'!D25107)))))))
I appreciate the input!