Excel 2003: rounding down and capping

[seanof30306]

OK, I use a spreadsheet to track my daily tips.

Issue #1: I must contribute 10% of my tips to a tip pool for non-tipped employees, but the maximum contribution is 20.00 per day. Additionally, those contributions are always rounded down to the nearest whole dollar, never up.

So, if I make 183.00 in tips, 10% of that is 18.30, and rounds down to 18.00.

If I make 253.00 in tips, 10% of that is 25.30, but, since my maximum contribution to the tip pool is 20.00 per day, that's what I kick up.

Here is the formula I'm currently using: "=G13*0.1" (G13 is where I enter my gross tips for the day)

After the formula does the calculation, I go back and manually round it down/cap it at 20.00. Is there a way to get excel to do this for me?



Issue#2: After my tip pool contribution is taken out, 35% is withheld for taxes. Again, it is always rounded down to the nearest whole dollar, and unfortunately, there is no cap.

So, if I make 183.00 in tips, 18.00 is taken out for the tip pool. Of the remaining 165.00, 35% comes to 57.75. That is rounded down to 57.00, and I go home with the rest.

If I make 253.00 in tips, 20.00 is taken out for the tip pool. Of the remaining 233.00, 35% comes to 81.55. That is rounded down to 81.00 and I go home with the rest.

The formula I'm currently using is: "=(G13-J13)*0.35" (G13 is where I enter my gross tips for the day, J13 is where the tip pool contribution shows up)

After the formula does the calculation, I go back and manually round it down.
Can excel do this for me?

Thanks

EDIT: OK, I've solved the rounding down issue with the following formulae:

Tip Pool: "=ROUNDDOWN(G13*0.1,0)"

Taxes: "=ROUNDDOWN((G13-J13)*0.35,0)"

It works perfectly. I still need to know how to cap the tip pool contribution at 20.00, though

[Ron Coderre]

EDIT: OK, I've solved the rounding down issue with the following formulae:

Tip Pool: "=ROUNDDOWN(G13*0.1,0)"

Taxes: "=ROUNDDOWN((G13-J13)*0.35,0)"

It works perfectly. I still need to know how to cap the tip pool contribution at 20.00, though

Try this:

Please Login or Register  to view this content.

Does that help?

[clownfish]

for the first issue:

=MIN(20,ROUNDDOWN(G13,0)*.1)

for the second:

=IF(G13*.1>20,(ROUNDDOWN(G13,0)-20)*.35,(ROUNDDOWN(G13,0)-ROUNDDOWN(G13,0)*.1)*.35)

[seanof30306]

Try this:

Please Login or Register  to view this content.

Does that help?

That is perfect! Thank you so much!

[VBA Noob]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save