Help with Averaging and SUMIF formula

H

hkg003

Active Member
Joined
Apr 6, 2008
Messages
257
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:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]House<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]77%<o:p></o:p>
[/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]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
hkg003 said:
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]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:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]House<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan<o:p></o:p>
[/TD]
[TD="width: 64, bgcolor: transparent"]77%<o:p></o:p>
[/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]
Click to expand...

G2, just enter:

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

Format the cell as percentage.
 
Upvote 0
Aladin Akyurek said:
G2, just enter:

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

Format the cell as percentage.
Click to expand...

Boy do I feel like an idiot! :laugh: Thanks for coming through for me again Aladin! I know enough to get myself hurt but your help makes it easy for me to understand! Thanks again Aladin for your time!
 
Upvote 0
Aladin Akyurek said:
No problem. You are welcome.
Click to expand...

One more question if you will...Some of the values in my cell that contains the rate has blank cells therefore if there is no value I get a #VALUE error. Do I use the IFERROR function? Also, I am getting the decimal places in the wrong area but when I add "/100" at the end of your formule, it puts it in the right format. Do I just continue adding the /100 or is there another way? Thank you
 
Upvote 0
hkg003 said:
One more question if you will...Some of the values in my cell that contains the rate has blank cells therefore if there is no value I get a #VALUE error. Do I use the IFERROR function? Also, I am getting the decimal places in the wrong area but when I add "/100" at the end of your formule, it puts it in the right format. Do I just continue adding the /100 or is there another way? Thank you
Click to expand...

Do you have a formula in the rate column? If so, care to post it?
 
Upvote 0
Aladin Akyurek said:
Do you have a formula in the rate column? If so, care to post it?
Click to expand...

Hello Aladin,
No I do not have a formula in the rate column as it was from a raw data dump. I think I have fixed it so far but entering this formula: =IFERROR(AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2),"0")/100 it seems to be working right now. Didnt know if that is correct though. Thanks
 
Upvote 0
hkg003 said:
Hello Aladin,
No I do not have a formula in the rate column as it was from a raw data dump. I think I have fixed it so far but entering this formula: =IFERROR(AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2),"0")/100 it seems to be working right now. Didnt know if that is correct though. Thanks
Click to expand...

=IFERROR(AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2)/100,"")

It's not clear to me though why we get a #VALUE! error.

Care to carry out the following:

=COUNT(B2:B10)

=COUNTIF(B2:B10,"?*")

=SUMPRODUCT(ISERROR(B2:B10)+0)

What results do we get?
 
Upvote 0
Aladin Akyurek said:
=IFERROR(AVERAGEIFS($B$2:$B$10,$A$2:$A$10,E2,$C$2:$C$10,F2)/100,"")

It's not clear to me though why we get a #VALUE! error.

Care to carry out the following:

=COUNT(B2:B10) 362

=COUNTIF(B2:B10,"?*") 0

=SUMPRODUCT(ISERROR(B2:B10)+0) 0

What results do we get?
Click to expand...

I have posted my results within your quote
 
Upvote 0
hkg003 said:
I have posted my results within your quote
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
226
Fluff
Fluff
S
  • Question Question
Create Formula to randomly Sequence/Prioritize a list of items
Replies
3
Views
189
dreid1011
dreid1011
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
302
JV0710
J
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
166
FormulasOnly
F
K
  • Solved
Calculate Average with Criteria
Replies
2
Views
236
kumara_faith
K

Forum statistics

Threads
1,224,585
Messages
6,179,700
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top