Ratios and amounts calculations

Hi all,

I'm (attempting) to design a special calculator for use in perfumery work. In simplified terms, one of the more systematic ways of finding the best combination of ingredients is to form a table with which to find ratios to try out. A basic form of this is naming the columns for the first material 9-1 and the row below (for the second thing) 1-9, thus bring the total number of parts to 10 at each step.
At this point I'll insert a screenshot so I can better explain what the whole point of my spreadsheet is...

Picture1.png

For example, I put 10g of Material 1 in a test tube; thus I want to find out how many grams of Material 2 I want to add in order to achieve a mixture in the ratio stipulated on the table (9:1, 8:2 etc), and vice-versa in the event that I put material 2 in the test tube first. In this example 10g=9parts and ?grams=1 part.

An important function of this sheet is for the ratio numbers (for lack of a better term) are changeable, so that one can calculate amounts for things like 19:5 ratios etc. I can also change the formatting a bit if that'll simplify things, for example having the whole ratio in a little box at the top, rather than separate numbers like now.

Alas, my high school algebra has leaked out of my head and I really can't figure this stuff out anymore.. doh!
I've had success with other spreadsheet that are based solely on percentages and a few handy functions I found somewhere else on here, but this ratios stuff has got me stumped.

Can anyone help me figure out a formula or functions that I can use for this? (I've attached the spreadsheet below)

Thanks in advance

Originally Posted by MrShorty

Here's a tutorial on proportions that should bring all that high school algebra back: http://www.purplemath.com/modules/ratio2.htm If you work all the way through to page 7, the author includes an example of mixing cement that looks almost exactly like your problem of mixing up perfumes.

Thanks! An yeah, its exactly the same problem

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.