+ Reply to Thread
Results 1 to 8 of 8

Percentage target and actual on graph

  1. #1
    Forum Contributor
    Join Date
    05-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    198

    Percentage target and actual on graph

    HI



    Can anyone please help, I have a spread sheet where I need to achieve 37 audits a year and complete 25% every 3 months, I have worked out the target in a graph and im struggling to work out the actual evry month and show on the graph,​



    I have attached the file which should make more sense in what im trying to do.

    Than yu for any help
    Attached Files Attached Files
    Last edited by donkey_uk; 03-26-2020 at 04:15 PM.

  2. #2
    Forum Contributor
    Join Date
    05-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    198
    Anyone? Could really do with as much help as poo

    Thank you all

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,836

    Re: Percentage target and actual on graph

    As near as I can tell, the chart looks fine. Once you fill in the correct formula(s) for column E (and values into column D) and whatever else needs to happen, then the chart should update fine. It is not clear to me how you intend to calculate/enter the values in columns D and E of your table, so I cannot comment on how the table works. Can you clarify these entries/calculations?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    05-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Percentage target and actual on graph

    HI

    Sorry I never explained very well

    when I complete Column D each month with the actual amount I would like column E %completed to populat the total for that month and the previous as a total c% completed

    I would like to be populated for every month so I can see if on target every month.

    Thank you .

    Hope this help
    Last edited by donkey_uk; 03-27-2020 at 05:21 AM.

  5. #5
    Forum Contributor
    Join Date
    05-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Percentage target and actual on graph

    HI

    Just to clarify, I want to get the formulas sorted in the %completed chart and not the graph as first mentioned. sorry for the confusion

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,836

    Re: Percentage target and actual on graph

    It isn't clear to me how you intend to calculate percent completed. Going all the way back to early algebra, the challenge in most "percent of" word problems was identifying the values that go into the basic expression "x is y percent of z" (or x=y*z). In the case of a "percent completed" problem, I am looking for a value for z that represents the total to be completed (I assume this is the 37 in C39??) and a value for x that represents how much has been completed (a running total of what is in D27:D38???). With those assumptions, I would expect something like =SUM($D$27:D27)/$C$39 (note the mix of relative and absolute references) in E27 and copied down to E39. I can't be sure my assumptions are correct, but maybe that will help you see what to look for and put the correct values into the formula.

  7. #7
    Forum Contributor
    Join Date
    05-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    198

    Re: Percentage target and actual on graph

    HI Mr Shorty

    Thank you that's exactly what I wanted.

    One other thing thou I have copied the formula down to E38 as suggested, is there an if statement that I could add to the end of the code ie

    If cell d30 is blank then leave cell E30 blank

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,836

    Re: Percentage target and actual on graph

    Absolutely, just wrap that formula inside of an IF() function that will test the condition and take the appropriate action. Especially in our older versions (since I don't know if some of this has changed in the newest versions), you might be mindful of the principles here (https://peltiertech.com/mind-the-gap...g-empty-cells/ ) so you understand how Excel's chart is going to treat those "cells that look blank but are not really blank" and deal with them appropriately.

+ 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