Average with Named Ranges

[SteveMcd217]

This is my first post as is the first time I was unable to find an answer already here great forum.

I am working on a scorecard sheet and have named ranges for rows and columns. Rows are named for business functions and columns named for business units. The ranges allow me to simply use a function such as AVERAGE(PRINT) to retrieve an overall average of all print rows for all business units. What I would like to do is find a way to average each unit.

Print = rows 8 - 10 Business Units = Single Column Row 7 will hold Average so

Column "All" Row 7 Formula =Average(Print) gives me an average of all Business Units All rows of Print.

Column "HR" Row 7 I can use Average(D8:D10) to get what I need but would like to use combination of the "Print" and "HR" named ranges.

Thank you

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this...
=AVERAGE(PRINT HR)

Hope this helps

M.

 

[Marcelo Branco]

Take a look at
What is Intersect Operator in Excel and How to Use it

M.

 

[SteveMcd217]

Have tried that without success, The issue is limiting the Print to only Column HR and Rows to Only Print. HR crosses many named sections in which I am only seeking to average the intersection rows of the two named ranges

 

[SteveMcd217]

Take a look at
What is Intersect Operator in Excel and How to Use it

M.

Was able to get Intersect to work with single row but not for all rows for the average

 

[Marcelo Branco]

Have tried that without success, The issue is limiting the Print to only Column HR and Rows to Only Print. HR crosses many named sections in which I am only seeking to average the intersection rows of the two named ranges

Maybe i have misunderstood what you need, but i think the formula above does exactly this.
Have you seen the link in post number 3?

Anyway, a small data sample and expected result(s) would be helpful - could you provide it?

M.

 

[Marcelo Branco]

Was able to get Intersect to work with single row but not for all rows for the average

Sorry, i'm confused. Isn't the named range PRINT a multi-row range? If so, the intersection also will have more than one row.

M.

 

[SteveMcd217]

Sorry, i'm confused. Isn't the named range PRINT a multi-row range? If so, the intersection also will have more than one row.

M.

Correct Below is sample data set formula =Average(PRT) gets the 15 as rows 3,4,5 are all named PRT and it averages combined HR and IT all rows. I can add =Average(C3:C5) to get average of Column named HR but would like to use named selection to only select the rows where Column named HR and Rows Name PRT intersect.

[TABLE="width: 216"]
<colgroup><col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3413;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="class: xl65, width: 96, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]All[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]HR[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]IT[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Total PRT AVG[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Control One[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Control Two[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Control Three[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

This?


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

All​

[/td][td]

HR​

[/td][td]

IT​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Total PRT AVG​

[/td][td]

15​

[/td][td]

10​

[/td][td]

20​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Control One​

[/td][td][/td][td]

5​

[/td][td]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Control Two​

[/td][td][/td][td]

10​

[/td][td]

20​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Control Three​

[/td][td][/td][td]

15​

[/td][td]

30​

[/td][/tr]
[/table]

Named Ranges
PR --> C3:D5
HR --> C3:C5
IT --> D3:D5

Formula in B2
=AVERAGE(PRT)

Formula in C2
=AVERAGE(PRT HR)

Formula in D2
=AVERAGE(PRT IT)

M.

 

[Marcelo Branco]

If you want to use the columns Headers, try in B2
=AVERAGE(PRT INDIRECT(C$1))
copy (drag) across

M.