Average IF excluding a specific value.

[lewryanjack]

Hi,

This is my first post. I own a finance company and am trying to determine the % increase / decrease in my fees from year to year. I have this figured out on a month to month basis. However, I am trying to run the month to date average for all the months. The months where no data has yet posted shows a value of -100%. I would like to exclude cells from my average that have -100% while running the average for all 12 monthhs. If I would like to take the average of all the cells below, but exclude the cells in the average showing -100% how would I do this. I only have data through May so technically the average would be for 5 months. Please see below. I appreciate any help.

Thank you.

Lewis

[TABLE="width: 1184"]
<tbody>[TR]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="class: xl178, width: 135"]Month[/TD]
[TD="class: xl179, width: 145"]% Change Prior Year[/TD]
[/TR]
[TR]
[TD="class: xl173"]Janary[/TD]
[TD="class: xl180"]-4.68%[/TD]
[/TR]
[TR]
[TD="class: xl173"]February[/TD]
[TD="class: xl180"]-1.75%[/TD]
[/TR]
[TR]
[TD="class: xl173"]March[/TD]
[TD="class: xl180"]-9.37%[/TD]
[/TR]
[TR]
[TD="class: xl173"]April[/TD]
[TD="class: xl180"]-14.29%[/TD]
[/TR]
[TR]
[TD="class: xl173"]May[/TD]
[TD="class: xl180"]-10.66%[/TD]
[/TR]
[TR]
[TD="class: xl173"]June[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]July[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]August[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]September[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]October[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]November[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
[TR]
[TD="class: xl173"]December[/TD]
[TD="class: xl180"]-100.00%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robby19]

Assuming your January data starts in B2;

=AVERAGEIF(B2:B13,">-1",B2:B13)*1

B2 = -4.68% (January)
B13 = -100.00% (December)

 

[Aladin Akyurek]

No need to multiply the result with 1

=AVERAGEIF(B2:B13,">-1",B2:B13)

will suffice.