Hello,
I have three attendance columns M:O which will contain the number of viisits per month. I am averaging these columns in column P. I am using the following formula to calculate the averages of three columns using Windows XP and Excel 2003.
=IF(ISERROR(AVERAGE(IF(M5:O5<>0,M5:O5))),"",AVERAGE(IF(M5:O5<>0,M5:O5)))
The formula works fine as I initially started to remove the error message from the zero values. My problem started when I created a conditional format to color the entire row yellow, based upon the formula in column A
=IF(ISERROR(IF(P24<8,"Warning","Ok")),"",IF(P24<8,"Warning","Ok"))
As you can see the first formula is trying to average columns M:O and based upon that number Column A is displaying Warning or Ok.
So, if any columns in the range M:0 contain a 0 value, then column P (Average column) needs to show the correct average, including if the average is =0; therefore column A will show a warning if the average is <8 or Ok if >=8. I then use a conditional format to color the entire row Yellow if a cell in column A is = Warning.
I have three attendance columns M:O which will contain the number of viisits per month. I am averaging these columns in column P. I am using the following formula to calculate the averages of three columns using Windows XP and Excel 2003.
=IF(ISERROR(AVERAGE(IF(M5:O5<>0,M5:O5))),"",AVERAGE(IF(M5:O5<>0,M5:O5)))
The formula works fine as I initially started to remove the error message from the zero values. My problem started when I created a conditional format to color the entire row yellow, based upon the formula in column A
=IF(ISERROR(IF(P24<8,"Warning","Ok")),"",IF(P24<8,"Warning","Ok"))
As you can see the first formula is trying to average columns M:O and based upon that number Column A is displaying Warning or Ok.
So, if any columns in the range M:0 contain a 0 value, then column P (Average column) needs to show the correct average, including if the average is =0; therefore column A will show a warning if the average is <8 or Ok if >=8. I then use a conditional format to color the entire row Yellow if a cell in column A is = Warning.