Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Determine average in Excel when number of values varies

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
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Leo AlexanderCommented:
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
 
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, 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
 
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.

Thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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
 
Leo AlexanderCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
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):
=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
 
barry houdiniCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
WeThotUWasAToadAuthor Commented:
Thanks Barry. As always, your solution is simple & effective.
0
 
Glenn RayExcel VBA DeveloperCommented:
Barry's the man. :-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now