# Thread: what am I missing?

1. ## 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.

2. 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

3. 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

4. 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??

5. 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

6. Thank you again .... I appreciate the tip and will use it going forward!

7. 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

8. 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?

9. 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

10. 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

11. 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.

12. ## 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

13. The formula:"
=A5*(1-B5)

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

14. 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*(1-B5), and carry that down the result in c will be equal to the percent +or - that is shown in column B?

15. 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...

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•