Formula question-there are safety checks.

Good morning,

Not sure if this is the forum I should be posting in, but I have a bit of a tricky question - not even sure if excel will be able to do this, but here's the place to find out =)

Basically, I work for a small plumbing company that offers reimbursement for boots once a year. The amount you get for boots is based on a few things, and I'm hoping there is a formula to calculate this without too much of a headache.

Every quarter, there are safety checks. Throughout the quarter, there are miscellaneous things that, if violated, subtract from your safety bonus. Basically, everyone starts out with 50 bucks cash for the quarter, and money is deducted with every violation - this amount can go negative and you can lose money toward your boots as well. When it is time to write checks for boots, if you've had no violation and have received the max amount for bonuses, your boot check amount is 200 as a reward for no violations. If you have even one violation, you get the standard rate of 125. If you've lost money because of too many violations, it gets deducted from the flat 125.

There are forms being maintained in excel now that document the date, employee name, what the violation was, and the amount deducted from the bonus for the violation. There is a separate sheet in the same workbook that documents the employee and how much money they will be getting for boots.

My question is if there is a way to quickly document and calculate throughout the sheets if there will be a deduction on their boot reimbursement or if they will get the boot reimbursement bonus. There needs to be a way to document if they get the full 50 each quarter and that if they do not then they do not get the bonus at the end of the year, so a two way tracking of sorts.

I love excel and I know it's capable of many things, and it seems that it would be the program I would want to document this sort of thing in. If needed I of course can do it manually, but it seems to me that there might be a formula for this sort of thing.

I know that was awfully wordy; if there is any clarification needed, please let me know.

Any help is appreciated.

It sounds to me that this is definitley doable in Excel, but it may be a lot easier to lend a hand to you if you could possibly post up a copy of your workbook. You might want to first change the names to protect the innocent, though.

Jason

Okay - here's just a quick mock workup of our boot check authorization form. If you look at employee B you will see that he only gets 100 for his boots; usually if you are in the positive, you get the flat rate of 125; however, in this mock case, he went negative on one of the quarters which would put him at 100. If you go into the negative at all, it is deducted from your boot check. Like I said, the file won't make a whole lot of sense. If you look at D, he got a total of -25 for safety bonus, but for his boots he's at -100; ie not only does he pay for his own boots, he has a fine to pay to the company for the excessive violations. That's completely made up and never happens because obviously he'd be fired, but it's just an example to see how it works. This is because 1 violation or 4, you still get the flat rate of 125, but it chips away at the scale of 200 that you get for bonuses. so if you lose $75 in one quarter off the total of $200 in bonuses you start with, you still get the standard 125, but now you're at the point where you start chipping away at your reimbursement that you get standard if you make another violation because you've already had 75 bucks in violations.

i hope you can make any sense of it!

http://www.ctwow.net/e107_plugins/co...est%20file.xls

that link should work - if not just let me know.

any luck on this, jason?

Are you stuck somewhere specific? It would seem like, depending on exactly how you are laid out, a simple SUMIF could be used to add up safety violations for each quarter. Then the annual value would be =200-SUM(quarterly safety violations). Then the check value would be =IF(annual value>=200,200,if(annual value>125,125,annual value). Substitute appropriate range references for "annual value" etc.

Well I thought of something similar to that, the problem being that the boot reimbursement won't necessarily be a full 200 minus whatever they got deducted or added for safety bonus. It's if they get no deductions they're at 200; if they get 1 or more deductions they're at 125; if they ever go into negative bonuses, then that deducts from the 125

Let me know if that makes it a bit more clear

So up to here makes sense?

It would seem like, depending on exactly how you are laid out, a simple SUMIF could be used to add up safety violations for each quarter. Then the annual value would be =200-SUM(quarterly safety violations).

I get the impression that your main question is in the IF function. Unfortunately, I still don't quite know exactly how you are making the determination. But it still seems like it should be doable with an IF function, it's just a matter of figuring out how you want to determine the condition. So extracting your last post into a pseudo-IF statement:

Please Login or Register  to view this content.

You should just need to fill in the parts in quotes with appropriate formulas to check the conditions. "negative bonus" might need to be another IF function:

Please Login or Register  to view this content.