0

enter image description here

I have a weekly scorecard that our leadership is going to fill out. Its all on one tab and has 500+ rows of the same scorecard that runs with the same statistics. I took a screen grab of one section. I am needing a scorecard at the very top that averages all the numbers. For example: the top scorecard will have the averages of all departments like HVAC Sales Revenue

The data is all exactly 65 rows apart and in column H.

We are going to continue adding weeks throughout the year so I need a formula that finds the data and only averages the data when there is data filled out.

Improve this question
1
  • Do you mean to take the by description in the column left from it? If so AVERAGEIF(S) Commented Dec 13, 2024 at 21:01

3 Answers 3

Reset to default
0

Correct data organization is the first action you need to start from.

A solution for you is to transpose the table:

enter image description here

Then you'll be able to summarize data as easily as never before:

[B2]=AVERAGE(B3:B54)

You can search SO to find a solution to pivot your existing data.

The pivot table is another right way

enter image description here

No comments.

An alternative way

enter image description here

[C13]=AVERAGEIF(B2:B10,B13:B15,C2:C10)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

=GROUPBY(B2:B19,C2:C19,AVERAGE,,0,,(LEFT(B2:B19,5)<>"total")*(C2:C19<>""))

enter image description here

This way it doesn't matter if at one time a category gets added.

Improve this answer

Comments

0

Another alternative workaround using AVERAGEIFS() :

enter image description here

=LET(
     a, B2:B19,
     b, C2:C19,
     c, AVERAGEIFS(b,b,"<>",a,a,a,"<>Total*"),
     UNIQUE(FILTER(HSTACK(a,c),ISNUMBER(c))))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.