Karat is a fraction: 24 is pure, so 14k gold is 14/24 or 58.3 percent gold by weight. Multiply the piece's weight by that fraction and you have grams of pure gold; multiply by the spot price per gram and you have melt value. Every scrap purchase, trade-in and insurance estimate starts from this number, and the buyer's offer is some percentage of it.
A 12.4-gram 14k bracelet at $75 a gram spot: 12.4 × 14/24 = 7.233 g pure, times $75 = $542.50.
The example
Three pieces on the scale. The spot price lives in one cell below the table so it can be updated once a day and every row moves.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Piece | Grams | Karat | Pure grams | Melt value |
| 2 | Curb bracelet | 12.4 | 14 | 7.233 | $542.50 |
| 3 | Wedding band | 8.0 | 18 | 6.000 | $450.00 |
| 4 | Class ring | 3.2 | 10 | 1.333 | $100.00 |
| 6 | Spot $/gram | $75.00 |
The formula
Pure grams first — that number is useful on its own for refining — then dollars:
How it works
Three inputs, two multiplications:
C2/24converts karat to a purity fraction. 14/24 is 0.5833; 18/24 is 0.75; 10/24 is 0.4167.B2*C2/24is grams of pure gold in the piece. A 12.4 g 14k bracelet holds 7.233 g of gold and 5.167 g of alloy.*$B$6multiplies by the spot price per gram, held in one absolute-referenced cell. Spot is quoted per troy ounce; divide by 31.1035 to get per gram.- Offer a percentage of that. Scrap buyers pay 70 to 90 percent of melt depending on volume and refining costs; the melt value is the anchor, not the offer.
Stones, clasps and steel springs are not gold. Weigh the piece with them removed, or subtract an estimate, before it goes in the grams column.
Try it: interactive demo
Enter the weight, the karat stamp, and today's spot price per gram.
Variations
From spot per troy ounce
If you keep spot in dollars per ounce, divide by 31.1035 inside the formula.
Buy offer at a percentage of melt
Multiply by the percentage you pay. Keep it in its own cell so it can change with volume.
Pitfalls & errors
The karat stamp is a claim, not a fact. Acid-test or XRF anything over a few grams before you pay on it; a plated piece stamped 14k has a melt value of roughly nothing.
Pennyweights (dwt) are still common in the trade: 1 dwt is 1.5552 g. If the scale reads dwt, convert in a helper column rather than in your head.
Do not use 14/24 as 0.58. The rounding error is 0.6 percent, which on a $5,000 buy is $30 — either given away or under-paid, and either way it is a number a customer will check.
Practice workbook
Frequently asked questions
Why is the spot price in a separate cell?
Does this work for silver and platinum?
B2*925/1000 instead of C2/24. Platinum is usually 950 or 900.Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class