Percent change over time

[wordsnotnumbers]

Excel doesn't have a percent change over time function, but it's imperative I get this right and am hoping for help, even if this boils down to math help. I am using a spreadsheet to figure out different variables in changing a person's salary. I have two numbers, one salary before a law change and one salary after a law change. I want to show the percentage the law changed the value of the salary.

So before the law change, the salary was worth $20,418.75. After the law change, the salary was worth $96,375.19. So to find how much the law changed the salary, I did a formula: =((96,375.19 - 20,418.75)/20,418.75)*100. I get 371.9936, which I take to mean a 372% increase. I also did the reverse: =((20,418.75 - 96,375.19)/96,375.19)*100 and got -78.8132702 which I take to mean if it had happened in reverse order, the salary would've gone down about 79%. I don't understand. Why isn't it going both ways -- shouldn't it either go up or down the same number? i.e. shouldn't the salary go up 372% or down 372% depending on the chronological order? How could it be 372% or 79%? Also, can someone confirm I am correct when I say: by increasing from $20,418.75 to $96,375.19, the salary increased by 372%?

I am doing a spreadsheet full of these percent changes over time to show the percentage numbers went up or down after law changes and must be accurate. Any help on ensuring accurate percent changes over time would be great.

Thank you so much.

 

[Nalani]

Maybe this will help you see:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">20418.75</TD><TD style="TEXT-ALIGN: right">96375.19</TD><TD style="TEXT-ALIGN: right">371.99%</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">20418.75</TD><TD style="TEXT-ALIGN: right">96375.19</TD><TD style="TEXT-ALIGN: right">-371.99%</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">110</TD><TD style="TEXT-ALIGN: right">10.00%</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">110</TD><TD style="TEXT-ALIGN: right">-10.00%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>=(B3-A3)/A3</TD></TR><TR><TD>C5</TD><TD>=(A5-B5)/A5</TD></TR><TR><TD>C7</TD><TD>=(B7-A7)/A7</TD></TR><TR><TD>C9</TD><TD>=(A9-B9)/A9</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Column C formated with Percent before the formula is entered.

As we all know 10 is 10% of 100.

 

[wordsnotnumbers]

edit: Confused again.

 

[Ralajer]

Your calculated percentage are right. The sign (negative(-)/positive(+/blank) is the direction of the change with the number being the magnitude. For the decrease percentage look at like this if the salary went from $96,375.19 to $0 one would have lost 100% of their salary. To loose 372% of $96,375 the employee would owe the employer $262134, so not feasible.

 

[wordsnotnumbers]

Your calculated percentage are right the negative is the direction of the change with the number being the magnitude. For the decrease percentage look at like this if the salary went from $96,375.19 to $0 one would have lost 100% of their salary. To loose 372% of $96,375 the employee would owe the employer $262134, so not feasible.

Are you saying Nalani's chart is wrong? Is my original math correct? Very confused!

Oh so a number can double many times over (100%, 200%, 300% etc), but it can only be the opposite of doubled once, so there, anytime a second number is smaller than the first, it will be some percentage of 100% but when a number is bigger than the first, it can be as infinitely a percentage larger. Yes? Would you say my original 372% increase or 79% decrease are correct?

 

[wordsnotnumbers]

Your calculated percentage are right. The sign (negative(-)/positive(+/blank) is the direction of the change with the number being the magnitude.

Cool, thank you. I am so bad at math but when you pointed out that $96k to $0 would be a 100% loss, that made total sense. So the format will always be ((A-B)/B)*100 or ((B-A)/A)*100... that is good to know. Thanks.

 

[Ralajer]

Based on your requirements it looks so. But to be clear you want the change in salary from the pre-law to post-law correct? If so

Excel Workbook
	A	B	C
1	Pre Law Salary	Post Law Salary	Change / Pre Law Salary
2	20,418.75	96,375.00	372%
3	96,375.00	20,418.75	-79%
...

Cell Formulas
Range	Formula
C2	=(B2-A2)/A2
C3	=(B3-A3)/A3

In the case of salaries they can increase by any amount but the can only be reduced up to zero so a max negative change of 100%.

 

[Nalani]

.

 

[wordsnotnumbers]

You are dividing by the same number, regardless of whether that number is being subtracted from or subtracted to. That seems wrong to me.

.

 

[Nalani]

My bad,

Ralajer posted the correct formula for you.