Formula for getting average value

**Zaid** · 02-08-2013, 05:42 AM

Dear All,

I am attaching one excel in which in "Analytical Tab" i have score in G Column " Score ". Now i have another tab "Master Capability" in which i have listed down many Capabilities against each Value stream.

I want output in Master Capability - Proficiency score.

Whenever i enter some score in "Analytical" tab, it should match the "Value stream- Analytical Engineering" & corresponding "capability" in "Master Capability" TAB and give the average of score for each capability.

Ex. for capability "CFD/Flow Analysis" the Proficiency score should be "2.66".

Please resolve

Regards
Mohammad

**dilipandey** · 02-08-2013, 06:23 AM

Hi Mohammad,

Ex. for capability "CFD/Flow Analysis" the Proficiency score should be "2.66".

but there is no score listed as 2.66 in column G of "Analytical" tab.

Regards,
DILIPandey
<click on below * if this helps>

**Zaid** · 02-08-2013, 06:35 AM

If you filter the CFD/Flow Analysis hte score gives average of 2.66. I have not calculated it separately.

**dilipandey** · 02-08-2013, 06:42 AM

Oops.. I did'nt noticed that

try using below formula:-

Formula:

Please Login or Register  to view this content.

see the blue cell in the attachment.. since you have validation in the respective place, hence I moved formula to right side

Test(20).xlsx

Regards,
DILIPandey
<click on below * if this helps>

**Zaid** · 02-08-2013, 06:52 AM

Dilip,

Thanks for the response but formula needs not to be Analytical Engineering specific, It should do validation of value stream & capabilities in both the tabs and give the average value. It is like, if i enter any score in the Analytical or Turbo tab , Average value should reflect in Master tab against each capability for that value stream.

Regards
Mohammad

**dilipandey** · 02-08-2013, 07:02 AM

formula is not specific to anything.. but off course you need to increase the range reference since as of now it is considering data till row 15 and for this you can use defined name as well.. suggest you to test this formula with more data and if you face any issue, highlight the same and attach back.

Regards,
DILIPandey
<click on below * if this helps>