Bottomline is that I have data coming from two additional sheets, and the average calculation is needed to bring results from them as well.
If I got rid of the 2nd averageifs then it's working with no problems, but not when I added in the 2nd averageifs.
I wasn't sure if I was supposed to add the two averageifs either, but the idea is that I need to have the average calculation done on data coming from worksheet 2 and worksheet 3 and at the end put them all in "overall reporting" worksheet.
But adding 2 averages isn't the same as averaging the whole group, e.g.
If you have 2 groups of numbers, 1, 4 and 7 in the first....and you average those then that gives you 4. If I have another group 10 & 18 then the average of those two is 14. Adding the two averages gives you 4+14 =18. That number doesn't really represent anything. The average of all 5 numbers is the sum of them all divided by the count of them all
40/5 = 8
You can use the same approach here, add all the numbers that meet the criteria and divide by the count of rows which meet the criteria, i.e.
=SUMIFS(RangAMean,RangARP,$B2,RangASite,$E2)+SUMIFS(RangBMean,RangBRP,$B2,RangBSite,$E2))/MAX(1,COUNTIFS(RangARP,$B2,RangASite,$E2)+COUNTIFS(RangBRP,$B2,RangBSite,$E2))
Audere est facere
Hello,
I see. The concept of average is correct. We are getting there almost.
When I used this formula:
F 2 cell's number doesn't make sense. I'm getting 28. Does it have to do with the denominator formula?
=SUMIFS(RangAMean,RangARP,$B2,RangASite,$E2)+SUMIFS(RangBMean,RangBRP,$B2,RangBSite,$E2)/MAX(1,COUNTIFS(RangARP,$B2,RangASite,$E2)+COUNTIFS(RangBRP,$B2,RangBSite,$E2))
Sorry there was a parenthesis missing from the beginning of the last formula I posted - the SUMIFS need to be in parentheses (so that the whole sum gets divided by the whole count), so F2 should be
=(SUMIFS(RangAMean,RangARP,$B2,RangASite,$E2)+SUMIFS(RangBMean,RangBRP,$B2,RangBSite,$E2))/MAX(1,COUNTIFS(RangARP,$B2,RangASite,$E2)+COUNTIFS(RangBRP,$B2,RangBSite,$E2))
which should give you 4.67 in that cell
ahh thank you. I tried to put the parenthesis after the first SUMIFS, which apparently didn't work.
By the way, could you also shed some light on how the median calculation might be done? Can you even put conditions on median calculation in much the same way as the AVERAGE()?
Thanks
You can use conditions with MEDIAN too but you'll need an "array formula" for that. In it's simplest form you can get the median of B2:B10 when A2:A10 is "x"
=MEDIAN(IF(A2:A10="x",B2:B10))
that's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.
For your example that would be
=MEDIAN(IF((RangARP=$B2)*(RangASite=$E2),RangAMean)
....but it's tricky to do if you want to include multiple ranges as per your average, is that what you want?
Yes.
So far I have...
But to do it on multiple ranges.....I almost wish I could just forcefully put another median() after the first..=MEDIAN(IF(RangASite=E$2,RangAMedian))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks