Basic math still rules. I believe what you are looking for is year over year change. In which case you subtract. YoY=current year minus previous year. So, if current year is negative, -4378.87. Prior year is positive, 4868.52, you subtract -4378.87-4868.52 for a result of -9247.39. Your sales are down 9247.39 compared to this time last year, and down is negative. The basic excel subraction works.
Now reverse it. If this time last year sales were down -4868.52 and this year sales are at 4378.87, you still subtract current minus previous. This time the result is positive indicating that sales are up 9247.39 compared to the previous year. On Tue, Jun 29, 2010 at 12:29 PM, John Aldrich <[email protected] > wrote: > I don’t think so… Here’s a real-world example from one sales region… > > Last Month: $0 > > YTD – this year: -$4378.87 > > Same Month 2009: $522.25 > > YTD 2009: $4,868.52 > > > > I want to see what the difference is between the two sets of numbers. Would > I not want to **subtract** the 2009 YTD from the current 2010 YTD? In > which case I’d end up **adding** the two for a difference of $9247.39. It > didn’t work if the 2009 YTD was negative, which is why we put the > conditional in. > > > > > > > > [image: John-Aldrich][image: Tile-Tools] > > > > *From:* Kevin Lundy [mailto:[email protected]] > *Sent:* Tuesday, June 29, 2010 12:13 PM > *To:* NT System Admin Issues > *Subject:* Re: Excel question > > > > Why do you need an IF statement. Enter the number as a negative. Sum > them. 1 plus -1 equals 0. > > On Tue, Jun 29, 2010 at 12:05 PM, John Aldrich < > [email protected]> wrote: > > I’ve got a spreadsheet I update once a month for one of our sales managers. > What it shows is the sales for the previous month, the sales for > year-to-date, the sales for the same period last year and the sales for the > year-to-date last year. Sometimes one of the numbers is a negative number > (i.e. if we had to bring the carpet back due to a defect or something.) I’ve > got it working partially, but sometimes the math doesn’t seem to work. How > would I go about writing my formula to test whether either number in a > matched set (i.e. last month and the same period last year) are negative and > then either add or subtract based on which number is negative? > > > > Here’s the current formula: =IF(C148<0,C148+G148,C148-G148) > > I’d like to test to see if G148 is negative (in this case, it is) and if > C148 is negative (in this case it is NOT.) Sometimes both will be negative, > sometimes one will be negative. I want to do the math properly depending on > which is negative. There are cases where it is pretty obviously NOT working > correctly, but I’m not sure how to correct the formula. > > > > Thanks… > > > > [image: John-Aldrich][image: Tile-Tools] > > > > > > > > > > > > > > > > > > ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
<<image002.jpg>>
<<image001.jpg>>
