Replacing Values Above 100% in an Average

[redhots4]

Hi everyone. I have an array that includes percentages from 0-100+, including blanks. For values over 100%, I want to use 100% for that field in my average. How do I do that?

Book2
	A	B
1	Percentage of Goal	Notes
2	100%
3	100%
4	100%
5	50%
6	50%
7	150%	<-- Use 100%
8	200%	<-- Use 100%
9
10
11	25%
12	0%
13	0%
14	77.50%	<-- Average
15	62.50%	<--Desired Result
Sheet1

Cell Formulas
Range	Formula
A14	A14	=AVERAGE(A2:A13)
A15	A15	=AVERAGE(1,1,1,0.5,0.5,1,1,0.25,0,0)

 

[Micron]

Maybe =If(AVERAGE(A2:A13)<=100, AVERAGE(A2:A13),100)
as long as there is at least one value in the range, otherwise you'll raise division by zero error.

 

[redhots4]

Thanks for your reply @Micron. Your formula returns the same value in my use case, because the overall average is still less than 100%. What I need to do is somehow use 100% in place of anything over 100% when running the average).

 

[Micron]

I guess I still don't get it then. If the average is less than 100% don't you want whatever the average is? If it's over 100% don't you want 100%?

	Required	Actual of A2:A6
	100	169
50
25
100
500

 

[redhots4]

No, that's not it. If the VALUE of a given field is over 100%, I want to use 100% for that field in my calculation. See the original spreadsheet I posted -- My "desired result" formula shows a manually-created set of values for use in the average.

 

[Fluff]

How about

=AVERAGE(IF(A2:A13>1,1,IF(A2:A13="","",A2:A13)))

 

[Micron]

Sorry, I thought those values in A were calculations using values from somewhere in the workbook. I don't see why you can't use If though, as in column B IF(A2<=100, A2,100)