I am looking at turnover and don't know how to show the percentage increase
or decrease. I have done =o1/p1*100 and it does show as a percent but not
whether it is an increase or a decrease. Please help

Jane

Hi Jane,

My standard blurp on percentages:

================================================== ===========
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate with; just
multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at least use
percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes) the
value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will be the
number 12 or 12%. What happens in your case depends on a setting:
ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be careful
when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even better,
8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %
================================================== ===========



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JR Hove" <JR wrote in message ...
|I am looking at turnover and don't know how to show the percentage increase
| or decrease. I have done =o1/p1*100 and it does show as a percent but not
| whether it is an increase or a decrease. Please help
|
| Jane

Niek,

As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?

Jim

"Niek Otten" wrote:

Hi Jane,

My standard blurp on percentages:

================================================== ===========
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate with; just
multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at least use
percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes) the
value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will be the
number 12 or 12%. What happens in your case depends on a setting:
ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be careful
when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even better,
8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %
================================================== ===========



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JR Hove" <JR wrote in message ...
|I am looking at turnover and don't know how to show the percentage increase
| or decrease. I have done =o1/p1*100 and it does show as a percent but not
| whether it is an increase or a decrease. Please help
|
| Jane

Hi Jim,

What do you want the percentage to be? How many % is 200.000 of zero?

Maybe you want to give "no" answer (empty cell):

=IF(A1=0,"",(B1-A1)/A1)

But something in the logic of your spreadsheet is wrong, I think, if you're trying to find out what the percentage of zero is.

Or can you give an example where this is meaningful?
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jim" wrote in message ...
| Niek,
|
| As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?
|
| Jim
|
| "Niek Otten" wrote:
|
| Hi Jane,
|
| My standard blurp on percentages:
|
| ================================================== ===========
| About percentages in Excel
|
| Niek Otten, July 26 2006
|
| In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate with;
just
| multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
| calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at least
use
| percentages in a way they weren't meant to be used in Excel.
| The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes) the
| value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will be
the
| number 12 or 12%. What happens in your case depends on a setting:
| ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
| All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be
careful
| when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even
better,
| 8%).
|
| Frequently Asked Questions:
|
| Q:
| I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
| A:
| Formula in C1: =B1/A1, Format as %
|
| Q:
| I have A1 and B1. How do I show the difference as a percentage in C1?
| A:
| As a percentage of A1: =(B1-A1)/A1, Format as %
| As a percentage of B1: =(B1-A1)/B1, Format as %
| ================================================== ===========
|
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JR Hove" <JR wrote in message ...
| |I am looking at turnover and don't know how to show the percentage increase
| | or decrease. I have done =o1/p1*100 and it does show as a percent but not
| | whether it is an increase or a decrease. Please help
| |
| | Jane
|
|
|

Niek,

If A1 is a prior value and B1 is a current value how do *you* define the
change as a % when prior was 0? I've often thought of it as a 100% change,
but that is likely not good math theory. Just looking for your take on the
issue...

Jim

"Niek Otten" wrote:

Hi Jim,

What do you want the percentage to be? How many % is 200.000 of zero?

Maybe you want to give "no" answer (empty cell):

=IF(A1=0,"",(B1-A1)/A1)

But something in the logic of your spreadsheet is wrong, I think, if you're trying to find out what the percentage of zero is.

Or can you give an example where this is meaningful?
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jim" wrote in message ...
| Niek,
|
| As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?
|
| Jim
|
| "Niek Otten" wrote:
|
| Hi Jane,
|
| My standard blurp on percentages:
|
| ================================================== ===========
| About percentages in Excel
|
| Niek Otten, July 26 2006
|
| In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate with;
just
| multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
| calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at least
use
| percentages in a way they weren't meant to be used in Excel.
| The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes) the
| value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will be
the
| number 12 or 12%. What happens in your case depends on a setting:
| ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
| All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be
careful
| when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even
better,
| 8%).
|
| Frequently Asked Questions:
|
| Q:
| I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
| A:
| Formula in C1: =B1/A1, Format as %
|
| Q:
| I have A1 and B1. How do I show the difference as a percentage in C1?
| A:
| As a percentage of A1: =(B1-A1)/A1, Format as %
| As a percentage of B1: =(B1-A1)/B1, Format as %
| ================================================== ===========
|
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JR Hove" <JR wrote in message ...
| |I am looking at turnover and don't know how to show the percentage increase
| | or decrease. I have done =o1/p1*100 and it does show as a percent but not
| | whether it is an increase or a decrease. Please help
| |
| | Jane
|
|
|

Hi Jim,

Such a change as a percentage is not defined.
It's not 100% either. Just not defined.
A percentage is just one way of expressing a value as a fraction of another value. And nothing, not even zero, is a defined
fraction of zero.

Think about what division means. You have a candy bar and want to divide it between 3 kids; each gets 1/3.
Now you want to divide it between no kids. How much does each of them get?
No definition for that.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Jim" wrote in message ...
| Niek,
|
| If A1 is a prior value and B1 is a current value how do *you* define the
| change as a % when prior was 0? I've often thought of it as a 100% change,
| but that is likely not good math theory. Just looking for your take on the
| issue...
|
| Jim
|
| "Niek Otten" wrote:
|
| Hi Jim,
|
| What do you want the percentage to be? How many % is 200.000 of zero?
|
| Maybe you want to give "no" answer (empty cell):
|
| =IF(A1=0,"",(B1-A1)/A1)
|
| But something in the logic of your spreadsheet is wrong, I think, if you're trying to find out what the percentage of zero is.
|
| Or can you give an example where this is meaningful?
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Jim" wrote in message ...
| | Niek,
| |
| | As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?
| |
| | Jim
| |
| | "Niek Otten" wrote:
| |
| | Hi Jane,
| |
| | My standard blurp on percentages:
| |
| | ================================================== ===========
| | About percentages in Excel
| |
| | Niek Otten, July 26 2006
| |
| | In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate
with;
| just
| | multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
| | calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at
least
| use
| | percentages in a way they weren't meant to be used in Excel.
| | The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes)
the
| | value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will
be
| the
| | number 12 or 12%. What happens in your case depends on a setting:
| | ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
| | All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be
| careful
| | when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even
| better,
| | 8%).
| |
| | Frequently Asked Questions:
| |
| | Q:
| | I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
| | A:
| | Formula in C1: =B1/A1, Format as %
| |
| | Q:
| | I have A1 and B1. How do I show the difference as a percentage in C1?
| | A:
| | As a percentage of A1: =(B1-A1)/A1, Format as %
| | As a percentage of B1: =(B1-A1)/B1, Format as %
| | ================================================== ===========
| |
| |
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "JR Hove" <JR wrote in message ...
| | |I am looking at turnover and don't know how to show the percentage increase
| | | or decrease. I have done =o1/p1*100 and it does show as a percent but not
| | | whether it is an increase or a decrease. Please help
| | |
| | | Jane
| |
| |
| |
|
|
|

Niek,

That is certainly the correct math answer. How would you handle that issue
in an Excel report based on extracted data from another source? This would
be one line in a multi page report. I think =if(a1=0,"undefined",(b1-a1)/a1)
would produce more questions than good information.

Jim

"Niek Otten" wrote:

Hi Jim,

Such a change as a percentage is not defined.
It's not 100% either. Just not defined.
A percentage is just one way of expressing a value as a fraction of another value. And nothing, not even zero, is a defined
fraction of zero.

Think about what division means. You have a candy bar and want to divide it between 3 kids; each gets 1/3.
Now you want to divide it between no kids. How much does each of them get?
No definition for that.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Jim" wrote in message ...
| Niek,
|
| If A1 is a prior value and B1 is a current value how do *you* define the
| change as a % when prior was 0? I've often thought of it as a 100% change,
| but that is likely not good math theory. Just looking for your take on the
| issue...
|
| Jim
|
| "Niek Otten" wrote:
|
| Hi Jim,
|
| What do you want the percentage to be? How many % is 200.000 of zero?
|
| Maybe you want to give "no" answer (empty cell):
|
| =IF(A1=0,"",(B1-A1)/A1)
|
| But something in the logic of your spreadsheet is wrong, I think, if you're trying to find out what the percentage of zero is.
|
| Or can you give an example where this is meaningful?
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Jim" wrote in message ...
| | Niek,
| |
| | As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?
| |
| | Jim
| |
| | "Niek Otten" wrote:
| |
| | Hi Jane,
| |
| | My standard blurp on percentages:
| |
| | ================================================== ===========
| | About percentages in Excel
| |
| | Niek Otten, July 26 2006
| |
| | In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate
with;
| just
| | multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see a
| | calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or at
least
| use
| | percentages in a way they weren't meant to be used in Excel.
| | The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the quotes)
the
| | value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It will
be
| the
| | number 12 or 12%. What happens in your case depends on a setting:
| | ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
| | All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of percentages: be
| careful
| | when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or, even
| better,
| | 8%).
| |
| | Frequently Asked Questions:
| |
| | Q:
| | I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
| | A:
| | Formula in C1: =B1/A1, Format as %
| |
| | Q:
| | I have A1 and B1. How do I show the difference as a percentage in C1?
| | A:
| | As a percentage of A1: =(B1-A1)/A1, Format as %
| | As a percentage of B1: =(B1-A1)/B1, Format as %
| | ================================================== ===========
| |
| |
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "JR Hove" <JR wrote in message ...
| | |I am looking at turnover and don't know how to show the percentage increase
| | | or decrease. I have done =o1/p1*100 and it does show as a percent but not
| | | whether it is an increase or a decrease. Please help
| | |
| | | Jane
| |
| |
| |
|
|
|

Jim,

I would probably put something like "NA"; not applicable.
Or just leave it blank.
Or an exclamation mark, meaning: "Something's going on here!"


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jim" wrote in message ...
| Niek,
|
| That is certainly the correct math answer. How would you handle that issue
| in an Excel report based on extracted data from another source? This would
| be one line in a multi page report. I think =if(a1=0,"undefined",(b1-a1)/a1)
| would produce more questions than good information.
|
| Jim
|
| "Niek Otten" wrote:
|
| Hi Jim,
|
| Such a change as a percentage is not defined.
| It's not 100% either. Just not defined.
| A percentage is just one way of expressing a value as a fraction of another value. And nothing, not even zero, is a defined
| fraction of zero.
|
| Think about what division means. You have a candy bar and want to divide it between 3 kids; each gets 1/3.
| Now you want to divide it between no kids. How much does each of them get?
| No definition for that.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "Jim" wrote in message ...
| | Niek,
| |
| | If A1 is a prior value and B1 is a current value how do *you* define the
| | change as a % when prior was 0? I've often thought of it as a 100% change,
| | but that is likely not good math theory. Just looking for your take on the
| | issue...
| |
| | Jim
| |
| | "Niek Otten" wrote:
| |
| | Hi Jim,
| |
| | What do you want the percentage to be? How many % is 200.000 of zero?
| |
| | Maybe you want to give "no" answer (empty cell):
| |
| | =IF(A1=0,"",(B1-A1)/A1)
| |
| | But something in the logic of your spreadsheet is wrong, I think, if you're trying to find out what the percentage of zero
is.
| |
| | Or can you give an example where this is meaningful?
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "Jim" wrote in message ...
| | | Niek,
| | |
| | | As a percentage of A1: =(B1-A1)/A1, Format as % - how do you handle A1=0?
| | |
| | | Jim
| | |
| | | "Niek Otten" wrote:
| | |
| | | Hi Jane,
| | |
| | | My standard blurp on percentages:
| | |
| | | ================================================== ===========
| | | About percentages in Excel
| | |
| | | Niek Otten, July 26 2006
| | |
| | | In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100% as 1. That makes it easy to calculate
| with;
| | just
| | | multiply a number with a percentage and you get what you need. No need to divide/multiply by 100. In fact, if you see
a
| | | calculation with percentages which has the number 100 somewhere in the formula; be very careful, it might be wrong or
at
| least
| | use
| | | percentages in a way they weren't meant to be used in Excel.
| | | The conversion to a fraction happens automatically if you enter the % sign: if you enter "15%" (without the
quotes)
| the
| | | value will be 0.15 and it will be displayed as 15%. If you then enter 12 in the same cell, two things can happen: It
will
| be
| | the
| | | number 12 or 12%. What happens in your case depends on a setting:
| | | ToolsOptions, Edit tab, "Enable automatic percent entry" (only Excel2000 and newer).
| | | All built-in functions of Excel and all the functions in Analysis Toolpak use this representation of
percentages: be
| | careful
| | | when supplying parameters to these functions; never use whole numbers (like 8), always use fractions (like 0.08 or,
even
| | better,
| | | 8%).
| | |
| | | Frequently Asked Questions:
| | |
| | | Q:
| | | I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
| | | A:
| | | Formula in C1: =B1/A1, Format as %
| | |
| | | Q:
| | | I have A1 and B1. How do I show the difference as a percentage in C1?
| | | A:
| | | As a percentage of A1: =(B1-A1)/A1, Format as %
| | | As a percentage of B1: =(B1-A1)/B1, Format as %
| | | ================================================== ===========
| | |
| | |
| | |
| | | --
| | | Kind regards,
| | |
| | | Niek Otten
| | | Microsoft MVP - Excel
| | |
| | | "JR Hove" <JR wrote in message
...
| | | |I am looking at turnover and don't know how to show the percentage increase
| | | | or decrease. I have done =o1/p1*100 and it does show as a percent but not
| | | | whether it is an increase or a decrease. Please help
| | | |
| | | | Jane
| | |
| | |
| | |
| |
| |
| |
|
|
|