Solved

# Determine average in Excel when number of values varies

Posted on 2014-07-26
145 Views
Hello,

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

Note that the number of values varies in each group as shown by the horizontal lines included in 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):

Thanks
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 3
• 3
• +2

LVL 3

Expert Comment

ID: 40222053
Hi!

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(B7:B12,"A",D7:D12)
=AVERAGEIF(B7:B12,"B",D7:D12)
=AVERAGEIF(B7:B12,"C",D7:D12)

...etc
0

LVL 3

Expert Comment

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, etc..it 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:

0

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

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.

Thanks
0

LVL 3

Expert Comment

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

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:

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.

Note:
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.
c-EE-2014-07-27b.xlsm
0

LVL 3

Expert Comment

ID: 40223352
Honestly,

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

LVL 33

Expert Comment

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.

=IF(B3="","",AVERAGEIF(\$C\$3:\$C\$20,C3,\$E\$3:\$E\$20))

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.

Thanks
0

LVL 33

Expert Comment

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:

=OFFSET(StartPoint,OffsetRows,OffsetColumns,Height,Width)

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.

Thanks
Rob H
0

LVL 27

Expert Comment

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!"
I 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):
=COUNTIF(\$B\$3:B3,B3)
3) The Average column formula is:
=IF(C3=1,AVERAGEIF(\$B\$3:\$B\$23,B3,\$D\$3:\$D\$23),"")
(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.

Regards,
Glenn
EE-Q-28484599.xlsx
0

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

=IF(C3=1,AVERAGE(D3:INDEX(D4:D\$100,MATCH(1,INDEX((C4:C\$100=1)+(C4:C\$100=""),0),0)-1)),"")

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
0

LVL 27

Expert Comment

ID: 40247606
Hi,

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.

Thanks,
-Glenn
0

Author Closing Comment

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

LVL 27

Expert Comment

ID: 40256553
Barry's the man. :-)
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month5 days, 10 hours left to enroll

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

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