rounding up from zero

**xindaiel** · 12-15-2007, 02:38 PM

Okay i'm so confused and turned around I'm ready to tear my hair out! whew rant out of the way. I've spent an hour looking at other posts hoping that I wouldnt' be reposting a question.

I have in cell e6 the number of units we have left/over forecast

e6= 15 over
this is based off of d6-c6 ((12-12 = +1 rounded up from zero))
my next cell a3 is days left in the month ((this case: 3))

i need cell f6 so have the formula e6/a3 to calculate how many we need to sell per day. If the TO GO is positive I need to PER DAY to match the TO GO number and change from red to green respectively.

I am having trouble making cell f6 round up from zero to one when the TO GO is at +1.

I'm at my wits end! please help? hope this wasn't too confusing.

**shg** · 12-15-2007, 03:37 PM

Zero is the roundest number there is ...

If you have N days left in the month, and M units left to sell, then you need to sell M/N units per day. Since you can't sell less than zero (excluding returns, I guess), the units per day can't be less than zero.

Also, if there's no days left, the result doesn't mean anything.

requiredSalesPerDay = if(daysLeft = 0, "Too late!", max(0, (quota - soldSoFar) / daysLeft) )

**xindaiel** · 12-15-2007, 03:41 PM

once it hits zero the per day will switch to a postive when we have sold enough to meet our forecast. I just need for f6 to skip zero and go straight to a green one (positive) when that happens.