Any abs() in that forumula, too? Sorry, about the multiple responses, head cold slowing my thinking...
On Tue, Jun 29, 2010 at 12:43 PM, Jonathan Link <[email protected]>wrote: > Also, what happens if you hard key any of the numbers? > > On Tue, Jun 29, 2010 at 12:41 PM, Jonathan Link > <[email protected]>wrote: > >> YTD must not actually be negative. I've never seen Excel not treat a sign >> correctly. >> >> >> >> 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/> ~
<<image001.jpg>>
<<image002.jpg>>
