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
| 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
| >
| >
| > | 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
| > | >
| > | > | Niek,
| > | > |
| > | > | re: 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:
| > | > | > Tools>Options, 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
| > | > | >
| > | > | > |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
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
