Average a row of a range if the letter "i" is typed in - range has to be dynamic.

[mt206689]

This is my first post on here, so I apologize if I leave out details or do not explain something well enough.

I have a range, where values between 1 and 5 are entered manually depending on how well someone scored, and a total row that calculates the averages of the column per section. If there is insufficient data to get a score, the letter "i" should be typed instead of a value between 1 and 5. If the "i" is typed in, it should calculate the average value of the row - here is an example of my table:

[TABLE="class: grid, width: 900, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Emply #1[/TD]
[TD="align: center"]Emply #2[/TD]
[TD="align: center"]Emply #3[/TD]
[TD="align: center"]Emply #4[/TD]
[/TR]
[TR]
[TD]Section #1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Criteria #1[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]4.0[/TD]
[TD="align: center"]3.0[/TD]
[TD="align: center"]2.0[/TD]
[/TR]
[TR]
[TD="align: center"]Criteria #2[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]5.0[/TD]
[/TR]
[TR]
[TD="align: center"]Criteria #3[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]2.0[/TD]
[TD="align: center"]i[/TD]
[/TR]
[TR]
[TD="align: right"]Average[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]4.67[/TD]
[TD="align: center"]3.33[/TD]
[TD="align: center"]3.67[/TD]
[/TR]
</tbody>[/TABLE]

So as seen above, the "i" under employee #3 would be calculated in the total row below as a 5, since the row has an average of 5.0 for criteria #2 - [calculated average (5 + 5 + 5)/3 = 15/3 = 5.0].

The i under Employee #4 would be calculated as 4.0 as the average for the row in Criteria #3 -
[calc: (5 + 5 + 2)/3 = 12/3 = 4.0]. I came up with a formula with works, but I want to make this range dynamic as I will be adding rows and columns, and deleting rows and columns as the survey changes. Here is the formula that currently works, but not dynamic. {formula entered in total row of employee#1}

=AVERAGE(IF(B3="I",AVERAGE($B3:$E3),B3),IF(B4="I",AVERAGE($B4:$E4),B4),IF(B5="I",AVERAGE($B5:$E5),B5))

Without going into to much depth, I cannot use tables or pivot tables (such as using table references) as this will restrict me in other areas. I am sure there is a way to do this with Index and match in an array, but I cannot figure this one out.

If anyone has ideas, please let me know, and if you need more info, I will get back to you as soon as I can.

 

[mt206689]

I have found a solution after many days of trying with success. What I did was added a static column (lets say Column F) which calculates the average of each row. Then I added the following calculation to the total row under Employee #1:

{=AVERAGE(IF(B$1:B$3="I",$F$1:$F$3,B$1:B$3))} - notice that this is an array (you must select Ctrl + Shift + Enter when you insert the formula).

The way that I set it up where if I add a row or column, it will not break the formula, and will include the value of that row (where my old formula would be skipped on the new row). Note that I will be using VBA to add in columns and rows as to copy the formulas over/across, else you will need to manually insert row/column and copy the formulas.