Link to home
Start Free TrialLog in
Avatar of Dale James
Dale James

asked on

MS Excel: Obtaining Irregular Cell Ranges References.

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


Avatar of Gary Benjamin
Gary Benjamin
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale James
Dale James


Hello Garlin

Thank you very much for your quick response.

Yes, you are correct regarding the Subtotal facility and by using it was able to incorporate it into the reporting code.

Thank again.