Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    2 Star Lounger
    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??

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    2 Star Lounger
    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!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    2 Star Lounger
    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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    2 Star Lounger
    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

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,474
    Thanks
    211
    Thanked 848 Times in 780 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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #12
    2 Star Lounger
    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
    Attached Files Attached Files

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #14
    2 Star Lounger
    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*(1-B5), and carry that down the result in c will be equal to the percent +or - that is shown in column B?

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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; 2014-07-29 at 06:25.

Page 1 of 2 12 LastLast

Posting Permissions

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