+ Reply to Thread
Results 1 to 5 of 5

how can i make a chart show how close a value is to a total amount

  1. #1
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    how can i make a chart show how close a value is to a total amount

    I have data like this

    Deviant 94 70 45 76 158 156 83 37 (8 different values)

    Total possible for each is 1935

    I want to use a pie chart or some chart to visually show how close each value is to the total possible value of 1935. If it was pie chart the entire pie would be 1935 and each piece would overlay showing from 0 up to the value reported. I could just say 94 is 4.8% of 1935 and 70 is 3.6% of 1935 and so on but I'm looking for a visual way to display this and to display all of them on one chart. Is that possible?

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,811

    Re: how can i make a chart show how close a value is to a total amount

    How about a stacked bar/column chart?
    Please Login or Register  to view this content.
    Select the table and insert a stacked bar or column chart. Fix up and format as desired.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i make a chart show how close a value is to a total amount

    I think my explanation is bad. Here is an example. I have 7 columns on the example. A is just the labels, B-I have values and at the bottom (row 1941) it adds up the number of cells that are above 1 or below -1. Each column is a different frequency so they need to be separate. Each column has 1937 values so I'm looking to have a visual representation that shows how many values are being counted out of the possible 1937 and if possible one graph for all 6 columns. I was thinking a pie chart but i cant figure out how to have the whole pie be 1937 and have it display each column as a colored piece that overlaps each other. Pie chart probably isnt the correct chart for it. So something that will show a max possible 1937 then visually show how much each column is in comparison to the max. Hope that is a better explanation.
    Attached Files Attached Files
    Last edited by kevinu; 02-24-2019 at 08:48 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,811

    Re: how can i make a chart show how close a value is to a total amount

    I'm not sure exactly what kind of chart you are looking for, but I still think a stacked column/bar or 100% stacked column/bar might work.

    0) optional. I entered =B2 into B1940 and copied across to I 1940 -- just to make a copy of the category labels down at the bottom
    1) in A1942, I entered "in bounds" (or other text or nothing).
    2) In B1942, I entered =COUNT(B3:B1938)-B1941 and copied across to I1942. This represents all of the points that are between -1 and 1
    3) I selected A1940:I1942 -> Insert -> Stacked column chart. I get a chart with 8 stacked columns where the lower portion of each column indicates how much is "deviant" and the upper portion indicates how much is "in bound".

    Is that more what you are looking for?

  5. #5
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i make a chart show how close a value is to a total amount

    ok i see. Ya that works. I was trying to do that with a pie chart but couldnt figure that out, but that works. Thanks

+ 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. show total amount invoiced per job number between 2 dates
    By TERRYJOHNMATTHEWS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2016, 04:48 PM
  2. Replies: 2
    Last Post: 08-11-2014, 09:56 PM
  3. Show total amount per supplier in one click
    By jaymkent in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-08-2013, 09:15 AM
  4. Need to chart both amount and percentage from a cell as total
    By Taiter in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-25-2013, 09:50 AM
  5. Replies: 1
    Last Post: 06-26-2012, 08:09 AM
  6. Replies: 6
    Last Post: 02-18-2012, 07:06 AM
  7. Sum up all amount and subtract with receivable to show Net Total
    By ebin charles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2011, 03:59 AM

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