+ Reply to Thread
Results 1 to 4 of 4

Formula for getting average value with condition

  1. #1
    Registered User
    Join Date
    07-23-2012
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    60

    Formula for getting average value with condition

    Dear All,

    I am attaching one excel in which i have 3 TABS. (1.Master Capability, 2. Analytical, 3. Turbo ).

    Now the process is:

    1. In Analytical Tab i have score in Column F against Capability and Maturity selected from Drop down menu
    2. In Turbo Tab i have score in Column F against Capability and Maturity selected from Drop down menu

    In Anlytical Tab, if we calculate "Aero-Thermo / Mechanical Design" capability has avg score of 2.66 & "Optimization / Test Validation" has avg score 2.5 and so on.
    Same is the case with Turbo Tab.

    Now i have listed all the capabilities against each value stream in "Master Capability" TAB.

    OUTPUT REQUIRED

    I want to display avg score calculated for each capability as above in Column F "Proficiency Score" of Master Capability TAB.

    Note: Capabilities are not unique so to get the average score for each capability against particular value stream validation is required.
    Like, After filling the score, the formula matches the capability and value stream and then display the average score.

    I have manually entered the output for reference.


    Please help
    Regards
    Mohammad
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Formula for getting average value with condition

    Hi Zaid,

    I believe I answered this using AverageIFs function. Is it the same or you revised something ?


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    07-23-2012
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Formula for getting average value with condition

    Can you please insert formula in Master Capability column of proficiency score and drag it down and calculate the avg score as i have manually calculated and entered ?

    That will help me. I am confuse with your previous reply.

    Regards
    Mohammad

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Formula for getting average value with condition

    Hi Zaid,

    See the attached file where I have used below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Changed one field to match up with sheet name to facilitate Indirect function

    Copy of Capability Template.xlsx


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

  5. #5
    Registered User
    Join Date
    07-23-2012
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Formula for getting average value with condition

    Thanks a lot Dilip. It worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1