Solved

Determine average in Excel when number of values varies

Posted on 2014-07-26
13
138 Views
Last Modified: 2014-08-12
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):

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
Thanks
0
Comment
Question by:WeThotUWasAToad
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 3

Expert Comment

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

...etc
0
 
LVL 3

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

More Groups
0
 

Author Comment

by:WeThotUWasAToad
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.

Thanks
0
 
LVL 3

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

Author Comment

by:WeThotUWasAToad
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.

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

by:Leo Alexander
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 31

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.

=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 31

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:

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

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.

Thanks
Rob H
0
 
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):
=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

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

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

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

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now