Calculating percent to goal when goal is zero?
- Thread starter anasmedic
- Start date
SirJB7
Excel Rōnin
Hi, anasmedic!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
You're not providing any clue about what is your data, how it's distributed in your worksheets or workbooks, which are the involved calculations between them, how you set the goal and how percents are calculated, ...
Regarding this, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
And specifically to your question on the topic title, the answer is easy and impossible: easy as long as you should do it in Excel as you'd manually do using math or stat operations and procedures; impossible since percentages are calculated as a division between two numbers where the divisor must not be zero (as y=f(x)=g(x)/h(x) it's a discontinuous function for h(x)=0). In this case you should opt for building your stats in no. of occurrences and not in percentages, where you can set your goal to 0 (but not to 0%), and use percentages to show dispersion, variance, etc.
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
You're not providing any clue about what is your data, how it's distributed in your worksheets or workbooks, which are the involved calculations between them, how you set the goal and how percents are calculated, ...
Regarding this, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
And specifically to your question on the topic title, the answer is easy and impossible: easy as long as you should do it in Excel as you'd manually do using math or stat operations and procedures; impossible since percentages are calculated as a division between two numbers where the divisor must not be zero (as y=f(x)=g(x)/h(x) it's a discontinuous function for h(x)=0). In this case you should opt for building your stats in no. of occurrences and not in percentages, where you can set your goal to 0 (but not to 0%), and use percentages to show dispersion, variance, etc.
Regards!
Thanks for the response and tips. I'll read through the site as time allows. Regarding your response to the topic tile, I am copying the table as it looks on my spreadsheet althouh I imagine this won't change the answer.
As to how I arrived at the goal, they are injury statistics. Our goal is to not have any injuries at all.
As I am a complete idiot when it comes to excel, could you provide more information on building the stats in number of occurrences? That seems like the better method.
I appreciate your help!
ET SLD Goal
Fatality 0 0 0
Severe Injury 0 0 0
First Aid Injury 0 2 0
Near Miss 2 11 0
As to how I arrived at the goal, they are injury statistics. Our goal is to not have any injuries at all.
As I am a complete idiot when it comes to excel, could you provide more information on building the stats in number of occurrences? That seems like the better method.
I appreciate your help!
ET SLD Goal
Fatality 0 0 0
Severe Injury 0 0 0
First Aid Injury 0 2 0
Near Miss 2 11 0
SirJB7
Excel Rōnin
Hi, anasmedic!
Your posted data is actually the stats you're looking for. So consider where you extract the data from, there you might have person names, companies, sectors, positions, dates, times, type of accident, other data from the incidence, treatment days, follow up, ... and so.
With that detailed data is from where and with how you'd build a stat model regarding your business or corporate KPIs (key process indicators, just in case), and that stat model is which will reflect your actual posted data. You'll have to consider time periods, job positions, sectors, and all other classifications that might help you to analyze where, when, how much and why are you closer or farther from your goal, the famous zero.
My advice is to re-think the process from the data collection (entry, registration), define the proper way to store it (one worksheet, chained worksheets starting from company/sector/employee and ending in accident date / result), and state clearly what information do you and the organization want to extract from that data. Once done that, measuring the deviation from the goal is the simplest part of the job.
Regards!
Your posted data is actually the stats you're looking for. So consider where you extract the data from, there you might have person names, companies, sectors, positions, dates, times, type of accident, other data from the incidence, treatment days, follow up, ... and so.
With that detailed data is from where and with how you'd build a stat model regarding your business or corporate KPIs (key process indicators, just in case), and that stat model is which will reflect your actual posted data. You'll have to consider time periods, job positions, sectors, and all other classifications that might help you to analyze where, when, how much and why are you closer or farther from your goal, the famous zero.
My advice is to re-think the process from the data collection (entry, registration), define the proper way to store it (one worksheet, chained worksheets starting from company/sector/employee and ending in accident date / result), and state clearly what information do you and the organization want to extract from that data. Once done that, measuring the deviation from the goal is the simplest part of the job.
Regards!
