negative formula

**rphan** · 08-15-2011, 11:47 AM

I currently have this formula for a vacation accrual spreadsheet. How would you make it show a negative number if he has used more than he has available?

=IF(L251+N251+O251-SUM(P251:AA251)<0,0,(L251+N251+O251-SUM(P251:AA251)))

**ChemistB** · 08-15-2011, 11:54 AM

Just guessing without seeing what's in these cells but remove the IF statement

=L251+N251+O251-SUM(P251:AA251)
Does that work?

**rphan** · 08-15-2011, 01:51 PM

l251, n251 and 0251 are just days availabe from last year, earned this year and next yer. and p-aa are the months. I tried the formula but it does not come out right.

Say an employee has 15 days available for vacation, he took 16 days, it is suppose to be -1 but comes out to 0.

**ChemistB** · 08-15-2011, 02:39 PM

Okay, so
L251 + N251 + O251 are the total days the employee has accumulated (although not sure why next year is in there?)

P251:AA251 are vacation days taken?

**rphan** · 08-15-2011, 02:46 PM

next year is in there because they are allowed to use days they accumulate after their date of hire anniversary.

so his date of hire is jan 10th, 2011, after that date it was have bee about 7 months so he gets 7 months worth of the years vacation. So 7divide 12 is about 58% so 58% of 15 available days is about 9 days. So he has 9 days for next years vacation that he can use this year.

**arthurbr** · 08-16-2011, 02:51 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!