Determine average in Excel when number of values varies


What would be the best approach or what Excel formula would you use to determine averages for the groups of 3-digit values shown in the following screenshot (Fig. 1):

Fig. 1Note that the number of values varies in each group as shown by the horizontal lines included in Fig. 2:

Fig. 2(Note: the numbering in column C was obtained by entering the formula:  =IF(B3<>"",1,C2+1)  in cell C3 and pasting down.)

The goal is find a formula which:
        a) can determine how many values are present in each group so it
        b) calculates the average based only on each group's values and
        c) displays the result (col E) in the same row as its corresponding group title (Fig. 3):

Fig. 3
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo AlexanderCommented:

It seems like you are on the correct path to your answer. The only missing component is the WHERE clause. Essentially, you need to specify to do averages WHERE it = a particular value. So, your groups' numbers can continue to grow and your formula will compute based on the groups' names.

See screenshot:AVERAGEIF statement

Leo AlexanderCommented:
You can continue to add the values, in any order, and they will just be searched based on the grouping formula. So, if yo add D, E, F, will nicely average the corresponding values and output to the right (or wherever you place the formula)

You can also have the entire column searched (B:B, D:D) to ensure it captures all the data:

More Groups
WeThotUWasAToadAuthor Commented:
Thanks for the responses. I can see some definite advantages to the approach you suggested. However, it also presents some disadvantages related to this specific project.

One minor disadvantage is the requirement of an inserted column to show the group name on every row.

The major disadvantage however, is the necessity of including an additional table to display the calculated averages. In my initial post (just before Fig. 3) objective #c reads:

"c) displays the result (col E) in the same row as its corresponding group title (Fig. 3)"

I did find one solution that meets the requirements but it is large and cumbersome (Fig. 4):

Fig. 4

Although it does fulfill the objectives of the initial post, it also includes limitations of its own:

1) the entire data range must first be sorted so that all values from any given group are together. This is a limitation which as far as I can tell, your solution does not have.

2) it includes a limit (10 in this case) to how many values can be included in a given group. That's OK for this project but it would be nice to have a more concise formula which meets the objectives but has none of the limitations.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Leo AlexanderCommented:
I understand your requirement.

Essentially you need an IF to look where the Column B = [the value] and continues looking at all corresponding values in Column D until the Column B value does not equal that original value. It also needs to include Nulls values as well.

I placed the result in a better format view, for me, and obviously you can move the columns over to your liking.

You may need an additional statement to only display the Average resulting value on the same row to where the group name is. The first statement will do the computing to include the correct range, and the second to do what i mentioned above.

I spent some time trying to get this to your requirement, but i have no been successful. I would suggest incorporating the AVERAGEIFS statement with a nested IF statement... some variety will give you the end results. I feel like it is a lot of work to achieve your desired result, but of course, that is just my opinion and i do not know the scope of your project.

Hopefully you can make some good progress with what has been done thus far--
WeThotUWasAToadAuthor Commented:
This is a bit of a tangent but perhaps someone may find it useful. It has to do with my previous screenshot (Fig. 4) which displays a formula which I described as "large and cumbersome."

I am a basic Excel user and it is not uncommon that I find myself needing a formula which I know ahead of time may be quite lengthy and involve a number of repeated commands.

In that situation, I often use a separate spreadsheet to construct the formula. As an example, the following screenshot (Fig. 5) shows how I created the formula in Fig. 4:

Fig. 5
Essentially, it's a process of determining which components of the formula lines will be used repetitively and which will be variable. Once that's determined, it's just a matter of concatenating the various parts using absolute cell references for the former and relative cell references for the latter.

For some reason, it is not possible to copy/paste multiple cells from a spreadsheet so that they make up multiple rows in a single cell — at least, I don't know how to do it. However, if the copied cells (in this case, D9:D19) are first pasted into a .txt window (eg Notepad), then recopied from the .txt window, they can then be pasted as multiple rows in a single cell (by first clicking the formula bar for the destination cell). Then it's just a matter of adding a leading "=" sign to make Excel recognize the newly-pasted cell content as a formula.

I find that with this approach, "large and cumbersome" formulas, although still large, are not nearly so cumbersome.  :)

The workbook containing all of the worksheets depicted in this thread is attached.
Leo AlexanderCommented:

I would have just gone with a MS Access solution using relational tables:

Main table would have your groups, child table the averages. The keys would be automatically assigned from the parent group ID... then just have a query make the appropriate views for you..

You may also try to mess with the VLOOKUP function within Excel. But, based on what you have going on it seems a bit easier to go the database route in my opinion. I am also a big Access fan, so a bit biased at that :)
Rob HensonFinance AnalystCommented:
On sheet1 of your uploaded sample I have added the following formula to column F, starting at F3 and copied down. then you can hide column C so that you cannot see the repeated Group names.


You can then get rid of columns H & I.

If you also set the data grid as a table, the formulas in columns C, D and F will copy down when new data is entered.

Rob HensonFinance AnalystCommented:
An alternative that doesn't need any additional columns but doesn't quite work:

=IF(B3="","",AVERAGE(OFFSET($B$3,MATCH($B3,$B$3:$B$21,0)-1,2,MATCH("Group "&CHAR(CODE(RIGHT(B3,1))+1),$B$3:$B$21,0)-ROW()+ROW($B$3)-1,1)))

Use this in E3 and copy down.

It gives an error on the last one because it can't find a match for the next group name. I have got round it by putting "Group G" immediately below "Group F".

The OFFSET function creates a range using the following syntax:


In your example:
Startpoint = B3 the first entry
OffsetRows = Calculated by matching the Group name in the Group column and returning the relative row number less 1
OffsetColumns = 2 so that it goes across to Value column
Height = Calculated based on finding the next Group name calculated by stripping off the last character of the previous group converting to an ASCII character value, adding 1 and converting back to a letter. This assumes that your Group names are actually "Group A", "Group B" etc.
Width = 1, only one column of values required.

Wrapped within an AVERAGE function it gives the average of the range.

Rob H
Glenn RayExcel VBA DeveloperCommented:
If you're willing to accept a little "stage trickery", you can generate the results you need with no helper columns and some relatively easy formulas.  Just need to add some conditional formatting and "presto!"
example layoutI even used "Comic Sans" as the font!

Here's how:
1) All cells in the Group column have values.  BUT, this conditional formatting rule is used to hide repeating values:
=B3=B2  <--- if true, turn the font white
2) The Num column formula is now (copied down):
3) The Average column formula is:
(I didn't round my results)

Editorial:  All tables should have values in every cell when possible, even if it's being used as a report.  As it was laid out originally, you would never be able to do any analysis or filtering on the data.

barry houdiniCommented:
Hello Steve,

Here's my suggested approach for a single (relatively simple) formula with no helper columns


The MATCH part finds the next row which is 1 or blank in column C and averages from the current row down to one row above that, hence averaging just the values for each group.

The second INDEX function is only there to avoid array entry

The formula allows up to 100 rows of data but it doesn't matter if there are fewer rows, you can make 100 into any figure you want to suit

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:

If you have any questions or issues with any of the solutions above, please let us know.

Otherwise, can you please properly close this question by clicking the "Accept this solution" link above the submission(s) above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

WeThotUWasAToadAuthor Commented:
Thanks Barry. As always, your solution is simple & effective.
Glenn RayExcel VBA DeveloperCommented:
Barry's the man. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.