+ Reply to Thread
Results 1 to 4 of 4

Average value of a parent in an hierarchy

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Pittsburgh
    MS-Off Ver
    2013
    Posts
    2

    Average value of a parent in an hierarchy

    I have primarily four columns with the first three in Hierarchy

    XX-->A-->B-->C-->D

    XX is a parent with just about 4 values, in A

    A is the parent with around a 100 attributes in it

    B is the child with about 1-4 values for each A

    C is a measure value - pecentage

    D is the status based off that percentage value in C, such as >95 being good, less than 90 being poor etc.


    I want a separate tab that just lists the parent A and its average of all B's with the status next to it.


    I have attached a sample sheet as an example. With regions as the parent A. So
    East A a.1 100 Good
    East A a.2 70 Poor
    West A a.3 90 Average
    West A a.2 99 Good

    I would like the result to be,

    East A 85 poor
    West A 94.5 Average

    I'd really appreciate any help i can get with respect to this. Thanks guys.

    Regards,
    Nik

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2013, 2010, 2007
    Posts
    4,633

    Re: Average value of a parent in an hierarchy

    Sounds like you can just use a pivot table. There's no attachment though, so hard to demonstrate...
    =IF(question_answered=TRUE,mark_thread="solved")
    =IF(someone_helped=TRUE,Reputation=Reputation+1)


    Walking the tightrope between genius and eejit...

    My Excel blog: http://excel.solutions

  3. #3
    Registered User
    Join Date
    01-03-2018
    Location
    Pittsburgh
    MS-Off Ver
    2013
    Posts
    2

    Re: Average value of a parent in an hierarchy

    Hi,

    Please find the sample file attached. You may use sheet4 in the file.

    Also, the column names are different that what I mentioned in the problem, and I also gave an example of the output I am looking for, in the same sheet.

    Thank you.

    Nik
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Average value of a parent in an hierarchy

    One way to do this, as Olly states, is to use a pivot table, or at least partially.
    On the PT sheet a pivot table is derived from the data the range in A1:E9 on sheet 4.
    The Region and Category are used as row labels.
    1) Tabular form is selected,
    2) Repeat item labels for Region,
    3) Subtotals and Grand Totals turned off.
    Score is placed in values and Average is chosen.
    A Status column, formatted to look as if it is part of the pivot table is populated using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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