Determine average in Excel when number of values varies

Posted on 2014-07-26
Last Modified: 2014-08-12

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
Question by:WeThotUWasAToad
  • 4
  • 3
  • 3
  • +2

Expert Comment

by:Leo Alexander
ID: 40222053

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


Expert Comment

by:Leo Alexander
ID: 40222058
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

Author Comment

ID: 40222913
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.

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Expert Comment

by:Leo Alexander
ID: 40223033
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--

Author Comment

ID: 40223075
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.

Expert Comment

by:Leo Alexander
ID: 40223352

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 :)
LVL 32

Expert Comment

by:Rob Henson
ID: 40223831
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.

LVL 32

Expert Comment

by:Rob Henson
ID: 40224321
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
LVL 27

Expert Comment

by:Glenn Ray
ID: 40230834
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.

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 40235373
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
LVL 27

Expert Comment

by:Glenn Ray
ID: 40247606

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.


Author Closing Comment

ID: 40256542
Thanks Barry. As always, your solution is simple & effective.
LVL 27

Expert Comment

by:Glenn Ray
ID: 40256553
Barry's the man. :-)

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question