Help with Averaging and SUMIF formula

[hkg003]

Hello,
I am trying to get the result for a specific item that includes averaging the rate for that item for a specific month. I have used the SUMIFS formula but that formula will not average my result. Also, since the value in the cell for the rate is in whole numbers on my spreadsheet, how do I get the decimal to return in the correct place? Example, I want to avoid having 7700.0% as result instead I would prefer 77.0%. Thanks for your time!



[TABLE="class: MsoTableGrid"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Name
[/TD]
[TD="width: 64, bgcolor: transparent"]Rate
[/TD]
[TD="width: 64, bgcolor: transparent"]Month
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Type
[/TD]
[TD="width: 64, bgcolor: transparent"]Month
[/TD]
[TD="width: 64, bgcolor: transparent"]Avg %
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]House
[/TD]
[TD="width: 64, bgcolor: transparent"]89%
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 105, bgcolor: transparent"]Result ==><o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]House<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan<o></o>
[/TD]
[TD="width: 64, bgcolor: transparent"]77%<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]House
[/TD]
[TD="width: 64, bgcolor: transparent"]99%
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]House
[/TD]
[TD="width: 64, bgcolor: transparent"]70%
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]House
[/TD]
[TD="width: 64, bgcolor: transparent"]65%
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Apple
[/TD]
[TD="width: 64, bgcolor: transparent"]89%
[/TD]
[TD="width: 64, bgcolor: transparent"]May
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Pear
[/TD]
[TD="width: 64, bgcolor: transparent"]92%
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Orange
[/TD]
[TD="width: 64, bgcolor: transparent"]75%
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Pear
[/TD]
[TD="width: 64, bgcolor: transparent"]50%
[/TD]
[TD="width: 64, bgcolor: transparent"]Dec
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Apple
[/TD]
[TD="width: 64, bgcolor: transparent"]98%
[/TD]
[TD="width: 64, bgcolor: transparent"]Nov
[/TD]
[TD="width: 105, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[hkg003]

Well, those results do not explain the #VALUE! error.

When there is no number in column B, we would get a #DIV/0! error, otherwise AVERAGEIFS should simply succeed.

The need to divide the result by 100 is also a bit strange.

Care to test the following on a different sheet in the same workbook:

A1:2

B1: 4

C1:

=A1/B1

What result do we get in C1?

I get 0.5.

I think I have found the problem. There was several dashes (-) for some reason and when I removed it, the formula worked great! Sorry for taking up so much of your time but I appreciate you working this out with me.

 

[Aladin Akyurek]

I get 0.5.

I think I have found the problem. There was several dashes (-) for some reason and when I removed it, the formula worked great! Sorry for taking up so much of your time but I appreciate you working this out with me.

Glad to hear the issue is sorted out. Thanks for providing feedback.

 

[hkg003]

Glad to hear the issue is sorted out. Thanks for providing feedback.

I have one more question and I should be good for the evening... with the formula you gave me of =AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2), how do I make it so that it does not average any cell containing a zero? In other words, I would like to average anything above zero. Thanks a bunch.

 

[Aladin Akyurek]

I have one more question and I should be good for the evening... with the formula you gave me of =AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2), how do I make it so that it does not average any cell containing a zero? In other words, I would like to average anything above zero. Thanks a bunch.

=AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$B$2:$B$10,">0",$C$2:$C$10,F2)

should do it.

 

[hkg003]

=AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$B$2:$B$10,">0",$C$2:$C$10,F2)

should do it.

Perfect that took care of it! Thanks again for your quick response and you have a great evening!

 

[Aladin Akyurek]

Perfect that took care of it! Thanks again for your quick response and you have a great evening!

You are welcome.