Using average on Named Range in SUMPRODUCT

**Lifeseeker** · 11-17-2011, 09:07 PM

Hello,

I have a sample SUMPRODUCT formula partially working. What is not working is the average() embedded. While it does return a numeric value, it is way off the mark, and I cannot seem to find out where the problem is.

Please see the attached file.

The problem is in "Overall Reporting", Col F, F1. If you see the formula, I put an Average() on the named range. The p2 and p3 rows do seem to return the right value, but not p 1 row for site A. I'm pretty sure I am just using ONE named range.

The 30.6 doesn't make sense.

Thank you

**ConneXionLost** · 11-17-2011, 09:57 PM

What result are you expecting to see in F2?

Cheers,

**kvsrinivasamurthy** · 11-18-2011, 03:31 AM

Mr lifeseeker,

I have verified the formla .It is working fine.
In Sheet A Data Entry,
The average of col D is 5.1 and thee are 5 rows which satisfies your condition (colC=p1 ,colF=A)
Sumproduct gives 5.1*6=30.6.

Pl clarify what actually you want.

**Deamo** · 11-18-2011, 04:38 AM

Based on the formula you're using, you're looking for the average of the days where reporting period is p 1, p 2, p 3, etc & site is A,b,c, etc ?

If that's the case, try replacing your sumproduct formula with:

=AVERAGEIFS(RangAMean,RangARP,$B2,RangASite,$E2)

Updating the range references as necessary

**Lifeseeker** · 11-18-2011, 03:04 PM

Hi,

I have tried to follow this formula suggested by Deamo and it works out great.

Please Login or Register  to view this content.

As I expanded the formula to accommodate for 1 additional site, I did this

Please Login or Register  to view this content.

When I hit enter, I get division by zero error.

Maybe I need to redefine the named range or I cannot just add two AVERAGEIFS together as if using SUMPRODUCT?

Thank you

**daddylonglegs** · 11-18-2011, 03:14 PM

You get #DIV/0! because at least one of the AVERAGEIFS formulas has no matching rows, you can avoid that by wrapping in an IFERROR function.....

What are you trying to achieve by adding two averages, though? I don't see how that will give you any meaningful result?

**Lifeseeker** · 11-18-2011, 03:24 PM

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.

**daddylonglegs** · 11-18-2011, 03:55 PM

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))

**Lifeseeker** · 11-18-2011, 05:39 PM

Hello,

I see. The concept of average is correct. We are getting there almost.

When I used this formula:

Please Login or Register  to view this content.

F 2 cell's number doesn't make sense. I'm getting 28. Does it have to do with the denominator formula?

**daddylonglegs** · 11-18-2011, 05:55 PM

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

**Lifeseeker** · 11-18-2011, 06:11 PM

ahh thank you. I tried to put the parenthesis after the first SUMIFS, which apparently didn't work.

**Lifeseeker** · 11-18-2011, 06:16 PM

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

**daddylonglegs** · 11-18-2011, 06:22 PM

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?

**Lifeseeker** · 11-18-2011, 06:32 PM

Yes.

So far I have...

Please Login or Register  to view this content.

But to do it on multiple ranges.....I almost wish I could just forcefully put another median() after the first..

**Lifeseeker** · 11-21-2011, 01:36 PM

Hi,

Could someone help me?

I am trying to do a multi-range median calculation in "Overall Reporting", but it seems to be tricky as I am not sure where you would put the condition in the formula.

In the 'overall reporting" sheet, the median column calculates the median value based on site and reporting period, and I am using a named range for gathering data from multiple worksheets, but I'm getting no where with it.

Is anyone able to help?

Thank you

**Lifeseeker** · 11-22-2011, 03:41 PM

Can someone help?

I would like to apply the same formula on multiple-ranges with same conditions. I'm not sure if VBA is where I need to invest more in for this kind of request?

Using average on Named Range in SUMPRODUCT

LinkBack

Thread Tools

Rate This Thread

Display

Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: Using average on Named Range in SUMPRODUCT

Re: multi-range median calculation with conditions

Re: multi-range median calculation with conditions

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions