Every day when I get home I empty out my pockets of loose change and put them into a large glass waterbottle that sits behind my desk. Eventually this bottle becomes too heavy to move and I empty it out to reclaim some cash for toys.

It occurred to me that there might be a way to estimate how much money was in the bottle by averaging out weights a little bit. Since my current ‘hottest toy’ want is a new Canon digital SLR camera, my ‘go for it’ point is somewhere around $750. The Coinstar machines now support converting your cash directly into a credit at Amazon without the 9% ‘fee’ they normally extract. Yay! But do I have enough?

I started by finding out the weight of an empty glass bottle (the web knows all – 15lbs). Then I weighed the bottle with all the coins in it (67lbs), which gives me about 52lbs of coinage to work with.

Because I don’t really have a distribution chart showing how many coins of each type I have, I assumed an even distribution. There’s probably more pennies than quarters, but I’ve never sorted things out. When I run the Coinstar dump, it’ll tell me how many of each coin I have, and that will make this more accurate the next time I do it.

Given that, some more googling gave me coin weights, so I was able to work it out…

Coin Value Weight(g) Penny 0.01 2.5g Nickel 0.05 5g Dime 0.10 2.68g Quarter 0.25 5.6g Average weight: 3.945g Average value: 0.10 Approximately .10 per 3.945grams or 0.025 cents per gram

Cool. My bottle has 52lbs of coins in it, which is around 23kilograms, which works out to around $544.00 worth of coins

Not quite there yet, but if I wait much longer, I may not be able to move the bottle. Maybe Zach and I will make an excursion to the Coinstar machine and shovel coins into it for a half hour, and see how accurate my numbers are.

That’s a neat way to guesstimate. I’ll be curious to hear how it works out.

I can tell you that 500 lbs of coin has an average value of approx. $3500. The amount can varry greatly though, from around $2500 to over $5500 depending mostly on the pennies and quarters. So, a pound of coin can have a value of between $5 and $11 with an average of $7. So, with an average mix you likely have $364, but it could be as high as $572. Of course this is just a guess, but I bet I am pretty close!

I helped clean coins from my mother’s house. She and my uncle had been collecting them for a couple years. I could carry the container but it was heavy. It totally forget the breakdown but there were at least 1,000 pennies. I had to give the receipt to the cashier to get money. Coinstar counted it at about $290. There was $5 in $1 coins it wouldn’t count.

Cool. Do you happen to have an application for that calculation, or know where I can find one? Like Java or something, to let the user just enter the weight (and maybe approximate distribution of coins, just in case someone wants to calculate how much they have of just certain types of coins)?

Although what I’d really like is an application that lets me enter the number of coins I’ve got of whatever type, and calculate the approximate weight FOR me.

See, I’ve got this popcorn tin, and I piled out my quarters on the bottom to see how many would fill the bottom, then stacked them to see how many it would take to reach the top (assuming neat stacks), then multiplied, coming to about a 10,000 quarter capacity, and I’d like to know how much that popcorn tin would weigh if I ever managed to fill it (yeah, right, maybe when I reach RETIREMENT age….)

how did u get $544, cos if u times 23 kg (23000g) by 0.025 u get $575, unless u took some off this value to compensate for the lower values

Hi Tamie, tried to make a post for the code but it was jumbling all the words up and had 2 copies of everything when I previewed the post, so im going to be making a few posts below with all the info i was goin to put in the original post.

Hi Tamie, ive made a program in VB that lets u enter the number of coins, and you have the choice of puttin it in grams or kg’s (when i say choice theres two different functions for you to choose). The code will be in the next post for the reason above. Also there will be parts with ‘/n’, this means a new line as when i preview the post it decides to put all the code on one line instead of how it shud be. So if you copy the code, make a new line where you see ‘/n’ and then delete the ‘/n’ as you go along.

Public Function MoneyGram(Penny As Double, Nickel As Double, Dime As Double, Quarter As Double, HalfDollar As Double, Dollar As Double) ‘/n’

Dim PennyW As Double ‘/n’

Dim NickelW As Double ‘/n’

Dim DimeW As Double ‘/n’

Dim QuarterW As Double ‘/n’

Dim HalfDollarW As Double ‘/n’

Dim DollarW As Double ‘/n’

PennyW = 2.5 ‘/n’

NickelW = 5 ‘/n’

DimeW = 2.268 ‘/n’

QuarterW = 5.67 ‘/n’

HalfDollarW = 11.34 ‘/n’

DollarW = 8.1 ‘/n’

MoneyGram = ((Penny * PennyW) + (Nickel * NickelW) + (Dime * DimeW) + (Quarter * QuarterW) + (HalfDollar * HalfDollarW) + (Dollar * DollarW)) ‘/n’

End Function ‘/n’

Public Function MoneyKG(Penny As Double, Nickel As Double, Dime As Double, Quarter As Double, HalfDollar As Double, Dollar As Double) ‘/n’

Dim PennyW As Double ‘/n’

Dim NickelW As Double ‘/n’

Dim DimeW As Double ‘/n’

Dim QuarterW As Double ‘/n’

Dim HalfDollarW As Double ‘/n’

Dim DollarW As Double ‘/n’

PennyW = 2.5 ‘/n’

NickelW = 5 ‘/n’

DimeW = 2.268 ‘/n’

QuarterW = 5.67 ‘/n’

HalfDollarW = 11.34 ‘/n’

DollarW = 8.1 ‘/n’

MoneyKG = ((Penny * PennyW) + (Nickel * NickelW) + (Dime * DimeW) + (Quarter * QuarterW) + (HalfDollar * HalfDollarW) + (Dollar * DollarW)) ‘/n’

MoneyKG = MoneyKG / 1000 ‘/n’

End Function

ok lol, so it did put them on there own lines, stupid preview thing, ahwell the ‘/n’ can still be deleted neway altho not necessary for the program to work as it has no affect wotsoever due to when you put a ‘ in front off nethin in VB, it treats that as a comment, not part of the program.

Time for the rest of the original post.

If you open up MS Excel, and then press Alt+F11, the Visual Basic Editor will appear (alternatively go on Tools->Macro->Visual Basic Editor). Then click Insert->Module. Copy the code n paste it into the module and then close the Visual Basic Editor.

Now in Excel, choose any cell and type =MoneyGram(0,0,0,0,0,0) . Make sure there are 6 zeros, each separated by a comma. Or use the =MoneyKG(0,0,0,0,0,0) to obtain the value in KG.

Now type in any value you want for however many coins there are. The first is Penny, second Nickel, third Dime, fourth Quarter, fifth HalfDollar, sixth Dollar, any coins u ave none of leave as zero. Note that in the code I have changed the wieght for Dime as it was wrong in the original value posted by the person who wanted the camera.

i obtained all values from http://www.usmint.gov/about_the_mint/index.cfm?action=coin_specifications

please also note that im from england so for the dollar, as the weights for the 2 are the same (on the site), i just used that figure n didnt specify which dollar it is (as i haven’t a clue which is in circulation!))

Hope this helps Taime, and for ne1 else reading this,

Andrew