Totaling Paper Bills and Coins?

[cargopilot]

If I have a column of numbers (money amounts) is it possible that I could get a sum of all the paper bills and all the coins?

For example:

12.75
22.50
56.10

Can I have two separate cells with one showing me the paper bills I have collected along with the actual amount of coins?

Bills 90.00
Coins 1.35

Thanks!

 

[Joe4]

Welcome to the Board!

I don't see how you can do what you want, as you appear to be making the assumption that all dollar amounts means that they are all bills and no coins. But that doesn't have to be the case.
How do you know that $12.75 doesn't mean that $10 in bills and $2.75 in coins (11 quarters) was collected?

If you are making that assumption (right or wrong), you can get the number you show by entering the following Array formula for the Sum of the Bills:
{=SUM(INT(A1:A3))}
Note that the squiggly brackets aren't really there, it just implies that you enter the formula by pressing CTRL-SHIFT-ENTER simultaneously instead of just ENTER.

Then, to get the Coins, just take the total sum and subtract the value above. So if the formula above was in cell A4, it would look like:
=SUM(A1:A3)-A4

 

[admiral100]

Hi,

Ctrl+Shift+Enter NOT just Enter

To sum the 90.00 :

=SUM(INT(A1:A3)) or =SUM(TRUNC(A1:A3,0))

To sum 1.35 :

=SUM(MOD(A1:A3,1))

 

[cargopilot]

If you are making that assumption (right or wrong), you can get the number you show by entering the following Array formula for the Sum of the Bills:
{=SUM(INT(A1:A3))}
Note that the squiggly brackets aren't really there, it just implies that you enter the formula by pressing CTRL-SHIFT-ENTER simultaneously instead of just ENTER.

Then, to get the Coins, just take the total sum and subtract the value above. So if the formula above was in cell A4, it would look like:
=SUM(A1:A3)-A4

Thanks!!!!

This worked! Took me a minute to figure out the CTRL SHIFT ENTER thing. Yes, you are correct, I have to assume they are always bills and 99.9% of the time they are. I get the occasional Susan B. Anthony dollar coin though.

 

[cargopilot]

Thanks for the reply Admiral!

 

[Joe4]

Thanks!!!!

This worked! Took me a minute to figure out the CTRL SHIFT ENTER thing. Yes, you are correct, I have to assume they are always bills and 99.9% of the time they are. I get the occasional Susan B. Anthony dollar coin though.

You are welcome.

Its not just the Susan B. Anthony dollar though. Like if you received $1.00, how do you know it was a one dollar bill, and not four quarters?
All things being equal, and not knowing your clientele or business, I would tend to think that most of the time, it would be a one dollar bill, though 99.9% seems a bit high to me. But you know your business better than I do!
I just tend to always look for the "exceptions" (it is a big part of my job - trying to identify all the exceptions and determine what may go wrong!)

 

[cargopilot]

Its not just the Susan B. Anthony dollar though. Like if you received $1.00, how do you know it was a one dollar bill, and not four quarters?

Good point. Guess there wouldn't really be any way to delineate that in a formula. These worksheets are done on a monthly basis and it might happen just once or twice a month with the quarters and dollar coins. I supposed I could just make a note on the side and correct it in the end.

 

[Joe4]

Guess there wouldn't really be any way to delineate that in a formula.

Since it is unknown, and there are tons of combinations (i.e. $5 could be 5 $1 bills, or 4 $1 bills and 4 quarters, or 3 $1 bills and 8 quarters, etc), unless you were tracking the bills explicitly, you really cannot do much with a formula (since a formula cannot define the unknown!).

Like you said, just something to be aware of, as you may need to make some adjustments at the end.