+ Reply to Thread
Results 1 to 2 of 2

adding mean and standard deviation values to chart while having % values

  1. #1
    Registered User
    Join Date
    05-18-2017
    Location
    Sweden
    MS-Off Ver
    2013/2016
    Posts
    22

    Unhappy adding mean and standard deviation values to chart while having % values

    I am having a hard time figuring out how to visualise my mean and standard deviation
    in the chart I have.

    I want to have the mean and stdav visualised in the chart like in the example, however
    because the values are in percentages (I want to have percentages) the mean lines always
    end up at 20 % and I would like this to be changed so that the line goes where it would if the percentage values were normal number values instead.

    For example the red data/IT graph has correct values but the middle line (mean line) is
    above the 17,65 % bars and when I calculate the mean normally like in the left side the mean is 2,14..
    which means that the line should be much further down as the 17,65 % bars represent 3 votes.

    Maybe there is a simpler way to do this?

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: adding mean and standard deviation values to chart while having % values

    If I'm understanding this correctly, then I think that the issue is that your percentages are proportional, which means the mean of the percentages will always be 20%, since there are 5 values totaling 100%, and the STDEV is calculating the STDEV of those percentages, not the STDEV of the raw data. I think what you want is to calculate the mean and STDEV of the raw total, as you've done in column D, then get the proportional percentage of that STDEV to use on the chart. In the attachment, I've added some headers in row 4 to simplify the formulas, then I used the following in L6 to get the proportional percentage of the mean for each section:

    =IF(H6="",NA(),AVERAGEIF($A$2:$A$50,L$4,$B$2:$B$50)/COUNTIF($A$2:$A$50,L$4))

    Fill the formula right through column N and down through row 20 and you should have your averages. For the mean, use the following formula in P6 where it should be array-confirmed (use Ctrl + Shift + Enter instead of Enter):

    =IF(H6<>"",L6-STDEV(IF($A$2:$A$50=P$4,$B$2:$B$50))/COUNTIF($A$2:$A$50,P$4),NA())

    Fill right and down, then use the following formula (array entered) in T6:

    =IF(H6<>"",L6+STDEV(IF($A$2:$A$50=T$4,$B$2:$B$50))/COUNTIF($A$2:$A$50,T$4),NA())

    Fill right and down. The results seem to align better with what (I think) you're after. Take a look at the attachment to see if it's a good fit:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. visualise mean and standard deviation values in chart/graph
    By doters in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-17-2017, 05:08 AM
  2. Calculating Standard Deviation using IF and ignoring N/A values
    By sroh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2015, 12:25 PM
  3. Replies: 0
    Last Post: 02-04-2013, 01:18 AM
  4. Replies: 1
    Last Post: 07-19-2011, 08:31 AM
  5. Standard Deviation forumula: More than 30 values?
    By Goalie35 in forum Excel General
    Replies: 3
    Last Post: 12-15-2009, 05:37 PM
  6. Individual Standard Deviation Values for Each Column
    By Maurice. in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-14-2009, 11:48 PM
  7. [SOLVED] simulate values for a given Mean and Standard deviation
    By Myl in forum Excel General
    Replies: 3
    Last Post: 04-05-2006, 12:30 PM

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