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:
<tbody>
</tbody>
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.
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:
Emply #1 | Emply #2 | Emply #3 | Emply #4 | |
Section #1 | ||||
Criteria #1 | 5.0 | 4.0 | 3.0 | 2.0 |
Criteria #2 | 5.0 | 5.0 | i | 5.0 |
Criteria #3 | 5.0 | 5.0 | 2.0 | i |
Average | 5.0 | 4.67 | 3.33 | 3.67 |
<tbody>
</tbody>
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.