Results 1 to 15 of 22
Thread: what am I missing?

20140726, 18:24 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
what am I missing?
Hell all,
Attached is an excerpt of sheet I am working on and this one piece is driving me nuts. I realize it is not a far drive, however I would rather not be making it!!!
I have a given number in cell A2 which represents this years sales. In cell B2 I show another number that can be either positive or negative which represents the percent that sales have either gained or fallen off from last year. in cell C2 I am determining the last year sales using the formula shown in the cell. My problem is that if I do a test to prove the answer of last year sales number it shows 107.3% instead of the 106.8 which as you can see is .5% to high. What is wrong with the formula that makes it off by that amount???
all help appreciated.

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140726, 19:04 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,240
 Thanks
 202
 Thanked 796 Times in 729 Posts
cvbs,
The problem is the formula for Last years sales. It should be: =Round(A2/B2,0) which yeilds 6,384. Then =a2/c2
will yield 106.8%.
PercentSales.JPG
HTH

20140727, 05:15 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
The 2 different values comes due to what you are comparing. The value you calculated ($6354) is a 6.8% reduction from this year. But as RG points out, that is NOT what you looking at, you want to know the value from last year, that would give 6818 with a 6.8% increase, which is the value that RG calculates...
Steve

20140727, 07:13 #4
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you both for the replies.
A follow up to the "round". .. Was the original formula incorrect to get last years sales or is round used because we are looking for an already given percentage to be returned??

20140727, 11:10 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,240
 Thanks
 202
 Thanked 796 Times in 729 Posts
cvbs,
Neither! I always use round when dividing or multiplying by fractional amounts to insure that when I later calculate using that cell value I'm calculating with the displayed amount and don't have fractional parts I'm not visually aware of. It's the old Accountant in me has to balance! HTH

20140727, 11:29 #6
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you again .... I appreciate the tip and will use it going forward!

20140727, 12:38 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
I disagree with some of RG's response. The original calculation was incorrect, regardless of the rounding. It was calculating the value 6.8% lower than the current value. That is not what you wanted. As mentioned you wanted the value that when raised by 6.8% would give you the current value (which is what RG calculated).
Steve

20140727, 15:23 #8
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
Steve,
Thank you for the input.... what would you recommend for the formula to use ... even if it was less than 100% or even negative. Is there a formula you would use that could work for ALL potential % results that could be copied down a column covering different stores?

20140727, 16:29 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
RG's formula works in all cases. But remember your Col B percent is not really +/. The 6.8% increase is listed as 106.8% so a 6.8% decrease (a change of 6.8%) would be listed as 100%6.8% = 93.2%...
Steve

20140728, 15:36 #10
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sorry for the follow up here. however I am still not getting the results to come out to the percent that was given to me as the amount of change. Here is a pic of the formulas I have tried.
Thank you
sheet for RG.jpg

20140728, 16:15 #11
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,240
 Thanks
 202
 Thanked 796 Times in 729 Posts
cvbs,
You have errors in your table that need to be fixed. Look at line 18 where LY sales are a minus number? How can you have minus sales? Returns I guess but you're not going to get correct answers. The line you have highlighted in the example is the same number we fixed in post #2.
In the future please post live workbooks not pictures as it isn't fun typing all the numbers in to test plus we can't see the actual formulas.

20140728, 17:04 #12
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
trying again
Sorry ... I did not see the advance ... add attachment paper clip
I do appreciate all your help and please accept my apology for the error in posting and having you do all that work.
Thank you,
cvbs

20140728, 18:37 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
The formula:"
=A5*(1B5)
Will give you the values in C if you use 0.068 in row 16.
In row 21, AFAIK you can't use % change formulas if the sign changes. they must both be losses or must both be gains: one can't be a loss and the other a gain.
If the values in C are what you really want, you are calculating the change from the current. your number represents a decrease from the current value to get last year's value. [To me, this seems a very odd way to look at it. Typically you look at numbers that represent a gain (+) or a loss() from last year to get the current year]
Steve

20140728, 19:20 #14
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 163
 Thanks
 0
 Thanked 0 Times in 0 Posts
Steve,
It is column C (last years) sales I am trying to get by using the reported (this years) sales numbers (column A) and the percent of increase or decrease that is shown in column B ...
. So are you saying if I put your formula in column c, =A5*(1B5), and carry that down the result in c will be equal to the percent +or  that is shown in column B?

20140729, 03:07 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
Not if the values in your workbook for Cols A,B, and C are accurate, since it doesn't give all the correct results...
I am saying it depends on what your percent increase or decrease represents. Is it relative to the this year's numbers or last year's numbers?
Do you have some example numbers of actual values of this years and last years and the percentage change you believe is correct? That will help us understand what you want to calculate...
Steve
PS
Lets say Last year sales were 1000 and this years sales were 800. What would you call the change? I would call this years a 20% (a 20% decrease) from last year, or that last year's value was +25% higher than this year's. Your methodology seems to be to give it a 25% which just seems confusing it takes the value from comparing this year's, but the direction from last year's...Last edited by sdckapr; 20140729 at 05:25.