What if you take A1 and make it -522.25 and add 4378.87 to it? I would expect that you'd get 4901.12 if you wanted the *difference* instead of the sum.
-----Original Message----- From: Charlie Kaiser [mailto:[email protected]] Sent: Tuesday, June 29, 2010 12:40 PM To: NT System Admin Issues Subject: RE: Excel question If I open a new workbook, format column A as currency, and enter this: A1: 522.25 A2: -4378.87 Then go to another cell, and enter this formula: =(A1+A2) My result is 3856.62, as expected. You are adding a negative to a positive. You'll get valid results. Perhaps there's a cell format issue that isn't recognizing the negative numbers somehow? *********************** Charlie Kaiser [email protected] Kingman, AZ *********************** > -----Original Message----- > From: John Aldrich [mailto:[email protected]] > Sent: Tuesday, June 29, 2010 9:29 AM > To: NT System Admin Issues > Subject: RE: Excel question > > 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/> ~ ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~
