Averaging with zeros and Conditional Formating

[jhedges]

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.

 

[GDS]

What exactly is not working? Is it your conditional formating that is not highlighting the cells with Warning? or the Formula?

Why isn't the formula

IF(P24="","",IF(P24<8,"Warning","Ok"))

 

[jhedges]

Sorry - I average columns M:O in Column P. Columns M:O are three different months attendance numbers M(Month1) N(Month2) O(Month3).

With the first array formula(below) I am removing the #DIV/0! error in column P when I don't have values in the M:O columns.

=IF(ISERROR(AVERAGE(IF(M5:O5<>0,M5:O5))),"",AVERAGE(IF(M5:O5<>0,M5:O5)))

So, my problem is that I want to remove the Divide by 0 error when nothing is displayed in columns M:O; However display a zero in column P if the actual average of columns M:O is zero; therefore, my formula in column A and conditional formatting will still work...

 

[GDS]

What #DIV/0! error? If you are averaging 3 months worth of attendance even if it is zero attendance - 0/3 is zero.

Maybe you could post a small sample.

 

[jhedges]

Looking at M:O12 I want to make sure column P shows zero and the formula in column A will show warning. It works for all the columns M:O that have numbers greater than zero. I want to make sure I don't have the #div/0! error...get a zero value or nothinbg if I remove the data and the column A formula is also working...
PL Dbase


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: MS Sans Serif,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 109px"><COL style="WIDTH: 106px"><COL style="WIDTH: 106px"><COL style="WIDTH: 84px"><COL style="WIDTH: 58px"><COL style="WIDTH: 63px"><COL style="WIDTH: 71px"><COL style="WIDTH: 61px"><COL style="WIDTH: 55px"><COL style="WIDTH: 76px"><COL style="WIDTH: 72px"><COL style="WIDTH: 71px"><COL style="WIDTH: 63px"><COL style="WIDTH: 61px"><COL style="WIDTH: 63px"><COL style="WIDTH: 64px"><COL style="WIDTH: 58px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 33px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Status</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">LastName</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">FirstName</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Personnel #</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">T #</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: center">Email</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Gender</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Mem. Type</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Locker #</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Policy Sheet</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name Tag</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Locker Type</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Mth1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Mth2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Mth3</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: center">Avg</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: center">Qtr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00">Warning</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Abel</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Elizabeth</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">01051154</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">tm0186</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">tm0186,</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Female</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Tms Rmn; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">MBC</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">F84</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">No</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Yes</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Outside</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">3.50 </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00">Warning</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Amazigo</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Uloma</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">01135970</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">tt6960</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">tt6960,</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Female</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Tms Rmn; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">MBC</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">F86</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Yes</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Yes</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Outside</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">4.00 </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Blieszner</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Kathleen</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01026591</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">u2410</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">u2410,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Female</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">MBC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">F56</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Inside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">12.50 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Bole</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Matthew</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01146897</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">tq3287</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">tq3287,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Male</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">F&HCIC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">M76</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Inside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">12.50 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00">Warning</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Brenneman</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Willliam</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">01514273</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">ac0634</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">ac0634,</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Male</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Tms Rmn; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">MBC</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">M99</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Yes</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">No</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Inside</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">5.00 </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Buchy</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Charles</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01037794</TD><TD> </TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0"> </TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Male</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">RET</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">M36</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Outside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">12</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">12.50 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Burch</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Rochelle</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01128397</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">ty5176</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">ty5176,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Female</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">MBC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">F38</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Inside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">20</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">17.00 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">34</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Buss</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Kevin</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01529128</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">af1268</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">af1268,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Male</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">WHBC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">M80</TD><TD style="FONT-FAMILY: Arial"> </TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Inside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">14</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">15.00 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">30</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Calligan</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Martha</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01503334</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">tx3086</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">tx3086,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Female</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">MBC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">F105</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Small</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">0</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #008000">Ok</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Cheng</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Menyan</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">01127803</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">ty3210</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: left">ty3210,</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Female</TD><TD style="FONT-FAMILY: Tms Rmn; TEXT-ALIGN: left">MBC</TD><TD style="FONT-FAMILY: Arial; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">F50</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Yes</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: left">Inside</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Arial; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial"> </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">9.50 </TD><TD style="BACKGROUND-COLOR: #a6caf0; TEXT-ALIGN: right">19</TD></TR></TBODY></TABLE>




Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[GDS]

Wouldn't the formula for P just be =IF(ISERROR(AVERAGE(M1:O1));0;AVERAGE(M1:O1))

 

[jhedges]

That will work, except it leaves the zero value in for the average; therefore my conditional formatting stays in place. When I remove the data from the M:O columns. I want the average column to display nothing, which would put column A back to OK and remove my conditional formatting, if there isn't any data in columns M:O, display a zero if data in M:O = 0, or display the correct average.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
11	Ok	Buss	Kevin	01529128	af1268	af1268,	Male	WHBC	M80		Yes	Inside	14	16		15.00
12	Warning	Calligan	Martha	01503334	tx3086	tx3086,	Female	MBC	F105	Yes	Yes	Small				0.00
PL Dbase

 

[GDS]

=IF(ISERROR(AVERAGE(M1:O1));"";AVERAGE(M1:O1))

I'm still not clear on what you want - if the M:O is blank then its blank, otherwise its the average. Column has to be set to numeric.

 

[jhedges]

Ok I think I figured it out. Your formulas were close you had the two fomulas below. These two were not clearing the warning when I removed the attendance data in M:O, because of the ,0, between the two average formula sections.

=IF(P4="","",IF(P4<8,"Warning","Ok"))

{=IF(ISERROR(AVERAGE(M5:O5)),0,AVERAGE(M5:O5))}

When I changed it to the formula below to ,"", it works just fine.

{=IF(ISERROR(AVERAGE(M5:O5)),"",AVERAGE(M5:O5))}

Thanks for being patient with this process and with me. Thanks for helping me figure out my issue.