Any excel geniuses?

[Gypsiyee]

I posted this on an excel help forum and I don't think they were getting what I was saying at all so I figure take 2, I'll try here

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.

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.

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.

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

I dono if that makes sense, but if anyone gets it let me know :p

[10sun]

Your link did not work.

You would want to use a countif statement. Something like this for just the boots:

=if(countif(sheet2!A:A, A1)>0, 125, 200)

This is assuming that the person's name is entered into column A & column A in sheet 2 contains the person's name.

I'm tired right now & my head is fuzzy, that is probably why I don't completely understand your set of rules for this game you play with employees... so try to get that link corrected as well.

[Menlaan]

Yep, you can definitely do it with a formula. I, too, am not completely clear on what you're trying to do, but if you fix the link, I try a few things, and we can see if any of them work for what you want.

[Zanchief]

Aye, if statements and countif statements should get it done.

syntax is =iif (two i's)

[Evermore]

you could cross link the appropriate cells on the pages and have it display the output where you deterimine

[Gypsiyee]

bizarre, the link worked before >< see if you can click on it from my gallery here:

http://www.ctwow.net/e107_plugins/coppe ... at=0&pos=1

[Gypsiyee]

Disclaimer: that workbook looks like absolute shit. Basically I'm taking it over from someone in another office who's been manual for 20 years and horribly inaccurate due to the amount of tedious work it takes to be accurate doing manual accounting anything and no one in either office has any computer knowledge; so most of that on there is slopped together.

[Menlaan]

link works

[Granh]

Couple things you can do. (if I understand this right)

You can create a conditional formating that'll change the color of the cell depending on the value. So green = full, yellow = half, red = $0. It'll let you easily track who has had deductions yadda yadda.

Another way of keeping track would be to create a pivot table. I've made a simple example here:

http://granheq.0catch.com/NT.xls

Basically insted of keeping each quarter on different tabs, keep all data on 1 and just put which quarter it relates to. The pivot table will then pull all the data, and sort it acordingly. You can change the layout if you want different information returned, but it lists who, when and how much. If you just have a couple employees it might be more work, but for large numbers of people, it's easier. Also, if you double click on their & value it'll drill down to where it's getting that info.

If these people are in multiple departments or teams you can add that too for a "page" value, and flip between each dept to find how one is doing vs the next. Lots of great stuff can be done with pivot tables.

[Menlaan]

Gyps, sorry my IM dropped off again. Let me know if you need anything additional.

For everyone else, it's a pretty complicated process, where basically, for each quarter, the aquired safety bonus is $50 minus the sum of violations. Then, at the end of the year, the bonus given is $200 if no violations and is otherwise $125 minus the deficit in any negative month. One exception to this is that if the negative violations cause the sum of acquired safety bonuses to enter negative territory, then the bonus is actually that negative amount.

I did the quarterly calc using sumproducts which are very similar to countif / sumif but more flexible. An example is "=50-SUMPRODUCT(($A53=$A$2:$A$50)*1,$I$2:$I$50)" which basically takes 50 minus the sum of column I wherever column A matches whatever is in cell A53 (in her case, the employee's name).

[Gypsiyee]

first time I've been able to get back - I think Menlaan was able to figure it all out

thanks heaps ^.^