Yep. Thanks. I don't know why I was trying to go around the block to get next door. J For some reason the formulas never looked right. Now I know why. J
John-AldrichTile-Tools From: Andrew S. Baker [mailto:[email protected]] Sent: Tuesday, June 29, 2010 12:59 PM To: NT System Admin Issues Subject: Re: Excel question Excel *is* doing it correctly. Look at what Kevin Lundy posted. If the number is negative in one of the scenarios, then the "difference" between the two will be much greater than normal as per basic math rules. -ASB: http://XeeSM.com/AndrewBaker On Tue, Jun 29, 2010 at 12:53 PM, John Aldrich <[email protected]> wrote: Correct. How do I make Excel do it correctly? John-AldrichTile-Tools From: Kevin Lundy [mailto:[email protected]] Sent: Tuesday, June 29, 2010 12:47 PM To: NT System Admin Issues Subject: Re: Excel question 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. John-AldrichTile-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. John-AldrichTile-Tools ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
<<image001.jpg>>
<<image002.jpg>>
