Complicated Formula Problem

[Llobid]

I am creating a spreadsheet that will automate the process that ou
secretaries go through for each employee of the school district. Eac
employee receives a set amount of "New State" personal leave each year
They also receive varying amounts of "Local" leave each year...dependin
on the length of their contracts. The secretaries of each campus an
department keep a ledger showing the dates of leave taken for eac
employee in their campuses or departments.

The problem we have encountered is as follows:

There are actually 3 categories of leave...each allowing only specifi
types of absences (reasons for the absence). The absences must b
deducted from the categories in the following order and only for th
allowed types.

_First:_ Local Leave - Types 1, 2, 3, 4, and 8

_Second:_ New State Leave - Types 1, 2, 3 , 4, 5, 6, 7, and 8

_Last:_ Old State Sick Leave - Types 1, 2, 3, and 7

The leave types are Personal Illness, Bereavement, Personal Business
etc.

The spreadsheet is laid out as follows (PDF file attached if needed):

*Column A:* Date of Absence

*Columns B - I:* 8 absence types

*Column J:* "New Added Local Leave"...must be considered in the formul
as an addition.

*Column K:* "Local Leave"...row 10 holds the employee's beginnin
balance of leave...row 11 holds the formula.

*Column L:* "New Added State Leave"...must be considered in the formul
when adding leave to "Personal New State Leave".

*Column M:* "Personal New State Leave"...row 10 holds the employee'
beginning balance of leave...row 11 holds the formula.

*Column N:* "Old State Sick Leave"...grandfathered in for us old folks
so no new leave is ever added...when it's gone, it's gone. Row 10 hold
the employee's beginning balance...row 11 holds the formula.

I have figured the formulas for figuring Local and New Stat
successfully, since the New State comes after the Local and include
all the leave types of Local...plus some. The problem comes when tryin
to figure the formula for the Old State Sick Leave. Since it can only b
used for a portion of the types as the New State, the formula must tak
into consideration what has already been deducted in Local and Ne
State...without duplicating the deductions.

It would be much easier if the secretaries only put one absence pe
row...but that is not always the case. Sometimes they may put a dat
range...with multiple entries under more than one absence type, so th
formulas have to be foolproof, so that the data is accurate. If not
some employees may be left with less leave than they are entitled to.

I have it figured so that the allowed leave types are taken from Loca
when applicable...and only until the balance <= 0...then th
spreadsheet shows "0" and takes the remainder from New State...and s
on.

I have been pulling what hair I have left out. Just when I think I hav
a formula that seems to work, I discover a circumstance in which i
doesn't. To give you an example of the kinds of formulas I've bee
working with, below are the formulas I currently have for "Local" an
"New State" leave.

=IF(ISBLANK(A11),"",IF(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)<0),0,IF(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)>=0),K10+J11-SUM(B11:E11,I11),K10+J11)))

=IF(ISBLANK(A11),"",IF(OR(M10=0,M10=""),0,IF(AND(K10+J11-SUM(B11:E11,I11)>=0,M10+L11-SUM(F11:H11)>=0),M10+L11-SUM(F11:H11),IF(AND(K10+J11-SUM(B11:E11,I11)>=0,M10+L11-SUM(F11:H11)<0),0,IF(OR(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)<0,M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11)>0),AND(OR(F11>0,G11>0,H11>0),M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11)>=0)),M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11),0)))))

I just can't seem to come up with a foolproof formula for Old Stat
Sick Leave that would not sometimes deduct from the wrong type o
duplicate deductions. If there is no leave left in either Local or Ne
State, any non-allowable absences from Old State should do nothing.

I would certainly appreciate any help you could give me. I would pos
the original spreadsheet...but I see the xls attachment is not allowed.


+-------------------------------------------------------------------+
|Filename: LeaveForm.PDF |
|Download: http://www.excelforum.com/attachment.php?postid=2765 |
+-------------------------------------------------------------------+

 

[Dave O]

Hi, Llobid-
First, an FYI: the PDF didn't download when I clicked. There was some
type of invalid file message.

I tried to follow the logic flow of your problem, and I agree, it is
ugly! Does this suggest that you should take a different approach to
the problem? Rather than attempt to accommodate every type of
scenario a user might present to you (and remember, they're users:
they'll accidentally figure out a way to FUBAR it), is it feasible to
ensure that *data entry* is bulletproof, rather than *data
processing*?

By this I mean: figure out the most standardized yet robust method of
data entry, then set up an Excel data input sheet (using dropdowns,
etc) that the data entry folks can't mangle. That way you could
disallow multiple leave types on a single row, for instance, yet
provide the input folks with a quick way to repeat an entry for that
employee so they won't complain about extra work.

The downside to this approach is that you'd need to adapt all the old
data to accommodate the new system, so you'd need a project approach
that stipulates all old data will be cleaned up by a certain date.
The upside is that moving forward all data will be clean and
compliant. A little up-front pain may be well worth the effort, since
making a mistake on payroll/leave is ALWAYS a problem.

Is this feasible?