Cell references when inserting and deleting rows

[Bithsa]

Hi all!

I'm relatively new to more advanced Excel functions, so this question may be relatively easy to answer.

I have a spreadsheet with a list of employees that is copy and pasted for every month of the year. As of now, the spreadsheet only goes through June. There are four columns, employee name, month, completions monthly average, and completions YTD average.

I'm currently using this general formula to average all of the monthly averages as they inserted into the spreadsheet:

AVERAGEIF($B$2:B77,$B2,$G$2:G77) B=list of employee names, $B2 is a specific employee name and G is the column of monthly avg completions.

The issue that I am having is that employees are inserted and deleted occasionally as they join or leave the workplace...and this formula needs to be applicable to all of the employees on the list, which means it has to be dynamic in a sense. However, when rows are inserted/deleted the cell references get messed up. I know the INDIRECT() function is supposed to help with this but in my situation I am not trying to reference only one cell. When an employee is inserted, the formula for one employee now references the cell of a different employee which is NOT GREAT.

Is there any way I can have one formula that will not change when a new person is added/dropped and can be applied to each employee?

Sorry this was wordy! I just like to be clear. Let me know if this is confusing and I'll try to explain better or send an example spreadsheet.

 

[Fishboy]

Hi all!

I'm relatively new to more advanced Excel functions, so this question may be relatively easy to answer.

I have a spreadsheet with a list of employees that is copy and pasted for every month of the year. As of now, the spreadsheet only goes through June. There are four columns, employee name, month, completions monthly average, and completions YTD average.

I'm currently using this general formula to average all of the monthly averages as they inserted into the spreadsheet:

AVERAGEIF($B$2:B77,$B2,$G$2:G77) B=list of employee names, $B2 is a specific employee name and G is the column of monthly avg completions.

The issue that I am having is that employees are inserted and deleted occasionally as they join or leave the workplace...and this formula needs to be applicable to all of the employees on the list, which means it has to be dynamic in a sense. However, when rows are inserted/deleted the cell references get messed up. I know the INDIRECT() function is supposed to help with this but in my situation I am not trying to reference only one cell. When an employee is inserted, the formula for one employee now references the cell of a different employee which is NOT GREAT.

Is there any way I can have one formula that will not change when a new person is added/dropped and can be applied to each employee?

Sorry this was wordy! I just like to be clear. Let me know if this is confusing and I'll try to explain better or send an example spreadsheet.

Hi Bithsa, welcome to the boards.

It sounds like the best option for you is going to be something called Named Ranges. This is basically where you outline a specific range of your sheet and assign a name to it. These named ranges are flexible meaning that if you insert or delete rows the named ranges expand or shrink accordingly.

To create a named range it is as simple as selecting the range with the cursor then typing a valid name into the "Name" box just to the left of the formula bar. A valid name cannot start with a number, cannot contain spaces and cannot directly match an existing cell reference (so something like C10 would not work). I would recommend having one blank row at the bottom of your named ranges meaning you can just keep inserting a new row at that point every time you want to expand them. For instance, using your example data above you would first select B2:B78, go to the "Name" box, type in EmployeeNames and press enter. Next select G2@G78, go to the "Name" box, type in MonthlyAverage and press enter. You can now replace this formula:

=AVERAGEIF($B$2:B77,$B2,$G$2:G77)

With this:

=AVERAGEIF(EmployeeNames,$B2,MonthlyAverage)

If you need to add more data to the list you simply select the blank row at the bottom and Insert-->Row. Both of the named ranges will expand to account for the new data. Likewise if you were to delete one or more rows from the data the named ranges would both shrink to suit it. I have a crude simplified example below:

Excel 2010
	A	B	C	D	E	F	G
1		Named Range: EmployeeNames					Named Range: MonthlyAverage
2		Tom					10
3		Richard					20
4		Harry					50
5		Tom					15
6		Richard					17
7		Harry					10
8		Tom					130
9		Richard					14
10		Harry					17
11	Insert new rows at this point to keep them inside the named ranges -->
12
13
14	Formula below converts to =AVERAGEIF(EmployeeNames,B2,MonthlyAverage)
15	51.66666667
Sheet1