AVERAGE formula question

[personia]

I have numbers in 7 columns looking like this:

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>$111.84</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>$99.66</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>$129.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>NA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>$149.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>$105.16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>$101.86</TD></TR></TBODY></TABLE>

I am looking for a formula that takes out the MAX of the set of numbers and averages the remaining numerical values.

In this example, I would like the formula to take out $149.00 and average the remaining numbers while ignoring "NA".

Thank you so much for your help!

Ian

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G
1	111.84	99.66	129	NA	149	105.16	101.86
2	*	*	*	*	*	*	*
3	109.504	This formula works in all version of Excel	*	*	*	*	*
4	*	*	*	*	*	*	*
5	109.504	This only works in 2007 and 2010	*	*	*	*	*
Sheet1

 

[personia]

Excel Workbook
	A	B	C	D	E	F	G
1	111.84	99.66	129	NA	149	105.16	101.86
2	*	*	*	*	*	*	*
3	109.504	This formula works in all version of Excel	*	*	*	*	*
4	*	*	*	*	*	*	*
5	109.504	This only works in 2007 and 2010	*	*	*	*	*
Sheet1

Thanks! If I want to apply this formula to an entire column can I just remove the "$"s and use the fill handle?

 

[njimack]

I think you mean an entire row, not column. That would work but is it reallly necessary? In Excel 2007 and 2010, you would be referencing 16,000 columns!

 

[barry houdini]

AVERAGE and AVERAGEIF type functions ignore text values so you don't explicitly have to exclude "NA", e.g. this formula will be sufficient in Excel 2007

=AVERAGEIF(A1:G1,"<"&MAX(A1:G1),A1:G1)

 

[personia]

Thank you njimack and barry houdini for your fast responses. It's much appreciated.