alloy percentages, spreadsheet

[rusty marlin]

I have an Excel spreadsheet I made up that allows the user to input how many pounds of various alloys to make a batch.
It then spits out the percentage of constituant alloys.

I would love to share, but I don't have time to e-mail everyone for the next week that wants it. Is there a way to post an excel file for available download?

Rusty

[garandsrus]

Rusty,

You can attach it to the message. You first need to "Go Advanced" in the message post to see the "Attach" option, which is a paperclip. Then, click on that and follow the button. Only certain file types can be uploaded, so you may need to put your spreadsheet into a .zip file.

I have attached the alloy calculation spreadsheet I did a while ago so you can see what an attached file looks like.

John

[rusty marlin]

Do you mind if I borrow some of your ideas and combine them into my spreadsheet? There are some great ideas there and with the combination of the two I think there is great potential for a very useful tool.

Mine is set up to allow the mixing of any number alloys to get the desired final percentage rather than just two alloys. Currently the formulas account for 7 different alloys, but any number can be added and accounted for.

[Jimlakeside]

Works great, thanks for the info.

[Woodtroll]

Garandsrus,

I (think) I have figured everything out except the green fields under the "Add (Ratio)" (G) column. I see that this is additive to result in the "New Alloy" (I) column, but am not sure how it is supposed to be used.

As an example, I want to use 95 pounds of pure lead and 50/50 solder to make a 20:1 lead/ tin mix (a true 20:1 where there are 21 "parts"). So in column B I put in 95 in the green field, 100 in the same column yellow field beside the lead %; in column E I put 50 in the yellow field beside both the lead % and tin %. Now I just have to keep fiddling with the green field in column E, and watch the values in column J until they line up with what I want?

This probably seems simple to most everyone else, but I figured I'd stick my neck out and ask the "dummy" question. This seems to be a useful tool, but I just am not sure I'm able to use it to the fullest extent <G>.

Thanks very much! Regan

[dominicfortune00]

+1

Thanks

[garandsrus]

Woodtroll,

The Add (Ratio) column is there because you can't necessarily get to where you want to go with the alloys you have on hand. The spreadsheet allows for the blending of two alloys with the addition of pure tin and lead as needed to balance out what you have with what you want. This is also why you see the "Add Tin" and "Add lead" amounts appear in the final "recipe".

To add in a third alloy, you can take the result from the first spreadsheet block and enter it into the "Start With" in the next block. Then include the third alloy using the "Add" column.

A true 20 part lead and 1 part tin results in an alloy with 1/21 = 4.76% tin, so that's what you want to put into column K as your goal, along with 100-4.76 = 95.24% for the lead. In your example, which is basically correct, since the numbers in green are only ratios, the numbers can be either 95 (pure lead) and 10 (50-50), since 50-50 is only 1/2 lead, or they can be 19 and 2, representing 21 "parts", 20 of which are lead and one tin. In this case, you wouldn't need the "Add (ratio)" column at all.

If you wanted to change the tin content of the final mix to say, 32:1 as some folks like, you would just change the numbers to (31 and 2) to represent 33 parts, of which 32 of them are lead. The "Add (ratio)" column is really only needed when you are trying to hit a set tin and antimony content. Based on your alloy, you will need to add pure tin, lead, or both to balance things out.

One really nice thing is that once you figure out how much alloy you want, you can just enter that amount in the light blue box (column H) and then the spredsheet will tell you how much of each component to add in lbs and oz so it's easy to make any sized batch, including just a pot full.

After playing with the spreadsheet for a while, you will find that you can arrive at the various percentages in multiple ways. My goal is to use as little pure tin as possible as that's the most expensive component, by far.


Rusty - You can borrow whatever you would like... I would be interested in seeing the spreadsheet that allows more alloy's to be mixed together.

I think that a person can probably get just about any alloy they want starting with two appropriate known alloys and adding pure tin or lead.

John

[Woodtroll]

NOW I see, John.

The Add (ratio) heading had me confused, as I assumed it was a proportional measurement or percentage. Now I see that its units are in pounds. Now it makes sense!

I have had fun tinkering with this spreadsheet, and sure do appreciate you posting it.

Take care, Regan

[garandsrus]

Woodtroll,

The Add (ratio) column is a proportional measurement... At the end of the process it gets converted to lbs and oz, based on the desired batch size.

John

[Woodtroll]

Well, that means I am lost again, sir...

Rather than bore everyone else with my questions, I will just send you a PM (if you have time to fool with me).

Hope everyone here has a great Labor Day!

Regan

[garandsrus]

Woodtroll,

I responded to your PM and certainly have time to help...

I think you are getting confused because you are creating a very simplistic alloy of 20:1. As a result, pounds and ratio's look very similar since there are only two components.

You may want to try using the spreadsheet to combine Linotype and WW to make #2 alloy (90-5-5). You will more than likely need to add pure lead and/or pure tin, using the Add(Ratio) column.

Using the standard ratio's for both that are included in the spreadsheet, I created two different ways to get to #2. There are certainly more! Here is what I ended up with:

1) Add Linotype at a ratio of 1, WW at a ratio of 5, and add .25 ratio of Tin. The .25 ratio of tin results in .8 lbs of tin for a 20 lb batch of alloy.
2) Add Linotype at a ratio of 1.3, WW at a ratio of 4, tin at .24, and lead at .71

John

[colbyjack]

wont open for me tried everything. owell sounds nice though. -chris

[montana_charlie]

wont open for me tried everything. owell sounds nice though. -chris
Do you mean the spreadsheet in the attachment won't open?

It is an Excel creation, and you need that software to use it.
Excel is part of the Microsoft Office package.

If you don't have Excel installed, your computer will not recognize the file.
CM

[colbyjack]

thats it charlie i dont have it. can it be copy and pasted to notepad or to a email? -chris