[Solved] Cashout Formula

[spellcheck_just_why]

Hello, I'm sure this exists but I am having trouble finding it. when I close my store at the end of the day I need to do a bit of math on scratch sheets and know I could do it easier with a spreadsheet.

At the end of the day I need to count all of my money. I need to leave $100 in the resister and put the rest into the bank deposit bag. So I would like a tally of the number of bills of each denomination that go in drawer and the number of each that go in the bank bag.

Say A:1 is my count of my 100s, A:2 is the count of my 50s, A:3 is my twenties, and so on. I would like two more columns to be output by the formula... For example B:1 is the number of 100s that go in the bank bag and C:1 is the number of 100s that stay in the cash drawer. B:2 is the number of 50s that go in the bank bag and C:2 is the number of 50s that stay in the cash drawer. And so on.

Obviously I'd like to get rid of the larger denominations and keep the smaller ones for the next day to make change, so the formula would have to take the total and then figure out subtracting the minimum number of pennies, then the minimum number of nickles and so on up the line until an even $100 is left in the cash drawer.

I would imagine that this is very common among nearly all retail stores so I imagine there is already a template for it, but I just don't seem to find it. If anyone knows of one that would be great, or I am perfectly willing to build it. I'm just not sure how to build the formula that takes the total cash and leaves behind the maximum number of small denominations, while depositing the maximum number of large bills.... while showing the quantities of each so I do a minimum of counting and stacking actual cash.

[RusselB]

The simplest method would be to know that the number of coins/bills is going to match or exceed what you require for the float...however, that is unrealistic.
I am working on something, but will need a few days (at least) to get it all working.
There is a template for Excel, but it uses macros and functions that are not available in OpenOffice. I have not found a pre-made template like this for OpenOffice or LibreOffice.

[spellcheck_just_why]

I know how the process would go I just can't figure how to do it as a formula. So the total of column A would be your total cash on hand. So if A:1 was your hundreds. Subtract the maximum number of 100s listed in A:1 from the total cash on hand to give a total greater or equal to $100. Write that number in B:1. Then if A:2 is your fifties, subtract the maximum number of 50s listed in A:2 from the remainder of your last calculation to give a total greater or equal to $100. Write that number in B:2 and so on down through the pennies. That should give the total of the bank bag. Figuring the quantities in the drawer is then easy - just the subtraction of one column from the other.

What is sticking me is two things

1) Writing the expression to subtract the maximum number of each denomination to get a result greater or equal to $100.

2) Specifying the order in which the sheet does the calculations. ie start with hundreds, then do fifties , then do twenties. From what I've read about Excel there is no way without macros to specify a calculation order. I'm assuming Calc would be the same.So would I do this as one long formula with the 1st step in parentheses and then the next step in a wider set of parentheses and so on?

Any help I could get with either (or both) of these problems and I could easily code the rest of the sheet.

[FJCC]

I think this works but check it carefully. It is my bed time and I may not be thinking clearly. It certainly fails if there is less the 100 in the drawer to start with.

[spellcheck_just_why]

I think this works but check it carefully. It is my bed time and I may not be thinking clearly. It certainly fails if there is less the 100 in the drawer to start with.

Thank you so much. This is pretty much what I was looking for. I modified it slightly to express the numbers in currency. It works except there is one small bug here. It is leaving $100.01 in the cash drawer and shorting the bank by a penny. I plugged in some numbers and have attached a copy of the modified.

[spellcheck_just_why]

OK I think I got it worked out. I just tacked on +.01 to the formula in D11. Not the brainiest fix but it seems to be working unless anyone sees and issue with it.

[spellcheck_just_why]

I thought I had it all sorted out, but no. Adding +.01 at the end of the formula in D11 solves the extra penny left in the drawer for the night EXCEPT when there are zero pennies in the drawer to start with.

I still need help with that one answer. How to add an IF/Then statement to the end of the formula in D11 that adds .01 to the result if B11 is greater than zero, but does not add the .01 if it is zero.

I have tried all sorts of ways to add it. I'm not sure if I am doing the If/Then syntax wrong or if I am combining it into the original formula wrong.

[spellcheck_just_why]

OK I finally figured it out. Rather than tack on the "+.01" at the end of the formula in D11 which introduced the new problem when the pennies were zero I removed that part and changed the "100" in the original formula to "99.99" and it works like a charm.

[spellcheck_just_why]

Update on this. I thought the issue had been solved, but the more I tested it the more I was plagued by errors of one penny in the total being shifted to the wrong bag. Also on occasion it did not figure the optimum quantity of each bill.

In the end I finally solved it. There are rounding errors that occur in the simple version of the sheet and I needed to add a rounding formula to all denominations smaller than $50.00. I have attached the final version. Feel free to use it.