+ Reply to Thread
Results 1 to 1 of 1

Auto-updating graphs + percentage of pool formula.

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    27

    Auto-updating graphs + percentage of pool formula.

    Hello,

    Please see attached example of the type of spreadsheet I am trying to create.

    I want to be able to copy and paste data into the CONTROL worksheet and then have the formulas in each of the tables below the graphs contained in the GRAPHS worksheet that will automatically update the charts everytime this new data is pasted into CONTROL.

    Each graph is reporting on the same data, but in a different way. For example, in Fig 1. the formula in the 3rd column of the 'Actual' row will pick out the number of instances in the CONTROL worksheet where someone is recorded as a 'Director' (column G) with a score of '3' (column T). I would also like the formula to display this as a percentage value of the pool. E.g. 40% of 'Director' scored a competency score of '3'. The next column in the Actual row would do the same for all 'Director' who had a score of '4' etc.

    In Fig 2. the fourth column of the 'Actual' row of the graph's table is will look for data that is recorded as belonging to the 'London' region (column J) and the 'Buildings Fabric' substream (column L) who have a score of '4'. Again, this would need to display as a percentage of that pool, e.g. 35% of London, Buildings Fabric employees belong have a score of '4'.

    I've got a feeling it may be doable with a COUNTIFS formula (see below). Also the data pool will change each time this report will be generated so the end of the formula I have used below to get the percentage (/984) will not work if the pool is larger or smaller!

    =COUNTIFS(Sheet5!$T$4:$T$987,Sheet5!$T$4,Sheet5!$G$4:$G$987,Sheet5!$G$4)/984

    I hope that the above makes sense, but please ask me if you need further clarification.

    Thanks
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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