troubleshooting Question

MS Excel: Obtaining Irregular Cell Ranges References.

Avatar of Dale James
Dale James asked on
Microsoft OfficeVBAMicrosoft Excel
2 Comments1 Solution35 ViewsLast Modified:
Hello Team

I receive multiple worksheets which contain data layouts similar to the following example.
Col A                           Col B                            Col C

User                          UserPercentage      Count of GroupNumber

User 1                          100.00%                        15
User 1                           90.24%                          1
User 1 Average                                              16
User 2                           100.00%                          9
User 2 Average                                                9
User 3                           100.00%                          4
User3 Average                                                4
User4                            85.37%                          1
User4                            98.78%                          1
User4                            85.37%                          1
User4                            98.78%                          1
User4 Average                                                 4
Grand Total  Average                                      92

What I require is to insert average formulas on each row User Average subtotal row, e.g.m User 1 Average, User 2 Average.

The issue is. referencing the the correct formula cell range for each subtotal row due to the inconsistency of each Users number of entries.  For example, to obtain the Average result for User 1, would require to reference B2:B3 (2 cell range), whereas, for User 4, the reference would be F9:F12 (4 cell range).

Can you please advise either how to to identify the correct range appropriate to each user as give above or if there is another more practical way to obtain the same result.

Any advice given will be much appreciated.

Thank you


Gary Benjamin
Business Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros