Excel 2003 Average formula ignoring % under 50% and over 100%

[tomshaw4]

I am trying to find an average formula on Excel 2003 which will only average the percentages between 50% and 100% for a row of percentages. ie I need to discount many of the outlier percentages which are either negative or over 100%.

I have tried Average (if (B2:AE2 ">0.5 and < 1") but just somes up value#

Can you help please

 

[njimack]

Welcome to the board. Confirm the following formula with ctrl+shift+enter:

=AVERAGE(IF(B2:AE2>0.5,IF(B2:AE2<1,B2:AE2)))

 

[tomshaw4]

Welcome to the board. Confirm the following formula with ctrl+shift+enter:

=AVERAGE(IF(B2:AE2>0.5,IF(B2:AE2<1,B2:AE2)))

Thanks for the formula Neil. It is comeing up with a figure of a figure of #N/A

Here are the 4 figures I am trying to enter:

	01/04/2013</SPAN>	02/04/2013</SPAN>	03/04/2013</SPAN>	04/04/2013</SPAN>
AALEXAND</SPAN>	#N/A</SPAN>	75%</SPAN>	81%</SPAN>	89%</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3></COLGROUP>

It did come up with a percentage when I input Average (B2:AE2). I have ctrl shift and entered the formula. What am I doing wrong?

Regards

 

[njimack]

Sheet1

*	A	B	C	D	E	F	G
1	*	01/04/2013	02/04/2013	03/04/2013	04/04/2013	*	*
2	AALEXAND	#N/A	75%	81%	89%	*	82%

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:8px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G2 {=AVERAGE(IF(NOT(ISERROR(B2:E2)),IF(B2:E2>0.5,IF(B2:E2<1,B2:E2))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[tomshaw4]

Sheet1

*	A	B	C	D	E	F	G
1	*	01/04/2013	02/04/2013	03/04/2013	04/04/2013	*	*
2	AALEXAND	#N/A	75%	81%	89%	*	82%

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula G2 {=AVERAGE(IF(NOT(ISERROR(B2:E2)),IF(B2:E2>0.5,IF(B2:E2<1,B2:E2))))} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Fantastic Neil. This has worked. Thanks for your help.

 

[njimack]

You're welcome, thanks for the feedback.