I am using Excel 2003 on a Desktop PC and would like to create a formula which will average a percent in a cell even if the percent or the formula is moved to a different location in the worksheet. The spread sheet will need to be rearranged. I understand that I can use Absolutes to keep the formula looking to a specific cell regardless of where it is moved.
The description of my project and problem:
I am developing an Excel Spreadsheet for multiple employees to input groupings of information. Each group will receive one final percentage. At each point a group is completed I will need to access the groups final percentage and incorporate the percentage into an averaging formula. This needs to be done regardless where the percents and/or formulas have been moved to on the spreadsheet.
For example.
Group A's average is 50%
Group B's average is 60%
Group C's average is 40%
Group D's average is 50%
The overall percentage for Group A - D is 50%
My Goal:
1. Create an average formula for the groups percentages as each group is completed. For example, after group B is finished, I would average A and B together. Then when Group C is completed I would average A, B, and C together. This would continue until all groups are completed and there will be a final average at the end.
2. I would like the formula to do this regardless of any rearranging on the workbook which will move the percentages to different cells.
Typically, I would create an additional averaging formula at the end of each group to average all of the previous percentages. I already know how to do both of these.
My problem is, each group is not going to be completed in order. The individuals using this spreadsheet want to rearrange the groups so they can view the spreadsheet from top to bottom in chronological order by date each group was completed. Each time the spreadsheet will be used the order will be different.
I would like to make one spreadsheet that can be used over and over where the averaging formula won't be impacted by any rearranging.
When I have my average formula pointed to the cells that group A's average is in and group B's average is in, everything works fine, until the groups are copied and pasted to another location on the worksheet.
I understand somewhat about using Absolutes to keep the formula looking at a specific cell regardless if either have been relocated.
My next problem is creating a formula that will average the "absolute" average fields selected if they are above the formula. So if the groups are rearranged, then the formula looks only to the averages that appear above on the worksheet.
Thank you!
The description of my project and problem:
I am developing an Excel Spreadsheet for multiple employees to input groupings of information. Each group will receive one final percentage. At each point a group is completed I will need to access the groups final percentage and incorporate the percentage into an averaging formula. This needs to be done regardless where the percents and/or formulas have been moved to on the spreadsheet.
For example.
Group A's average is 50%
Group B's average is 60%
Group C's average is 40%
Group D's average is 50%
The overall percentage for Group A - D is 50%
My Goal:
1. Create an average formula for the groups percentages as each group is completed. For example, after group B is finished, I would average A and B together. Then when Group C is completed I would average A, B, and C together. This would continue until all groups are completed and there will be a final average at the end.
2. I would like the formula to do this regardless of any rearranging on the workbook which will move the percentages to different cells.
Typically, I would create an additional averaging formula at the end of each group to average all of the previous percentages. I already know how to do both of these.
My problem is, each group is not going to be completed in order. The individuals using this spreadsheet want to rearrange the groups so they can view the spreadsheet from top to bottom in chronological order by date each group was completed. Each time the spreadsheet will be used the order will be different.
I would like to make one spreadsheet that can be used over and over where the averaging formula won't be impacted by any rearranging.
When I have my average formula pointed to the cells that group A's average is in and group B's average is in, everything works fine, until the groups are copied and pasted to another location on the worksheet.
I understand somewhat about using Absolutes to keep the formula looking at a specific cell regardless if either have been relocated.
My next problem is creating a formula that will average the "absolute" average fields selected if they are above the formula. So if the groups are rearranged, then the formula looks only to the averages that appear above on the worksheet.
Thank you!
