Finding the Percentage of two compounds made up of different ratios of two variables

[Frosty1419]

Hello all,

I am trying to find a simple way to calculate the Percentage of two compounds made up of different ratio's of two variables.

So for example, I am given an amount of A and B and would like to calculate how much of the compound AB2 (0.87 ratio of A to B2) and the compound AB (1.74 ratio of A to B) would be necessary to use up all of the given A and B.

Thanks in advance for any help!

 

[Mackers]

Hello all,

I am trying to find a simple way to calculate the Percentage of two compounds made up of different ratio's of two variables.

So for example, I am given an amount of A and B and would like to calculate how much of the compound AB2 (0.87 ratio of A to B2) and the compound AB (1.74 ratio of A to B) would be necessary to use up all of the given A and B.

Thanks in advance for any help!

You could use =MIN(quantity_of_A/ratio_of_A, quantity_of_b/ratio_of_B)

So if you have 12 A and 15 B, and the ratio of A used to B is 0.87 A to 1 B:

=MIN( 12 / 0.87, 15 / 1 )
=MIN( 13.793... , 15)
=13.793...

This means you used up 13.793...*0.87 of A and 13.793... * 1 of B, so the A ran out first (as 13.793...*0.87 = 10) and you have 15 - (13.793...*1) B left.

Does that help?

 

[Frosty1419]

Mackers

Thanks for your help! When I tried this for a few examples the totals did not add up to the original amounts of A and B at the end. Since one ratio is less than 1 and one ratio is greater than one, there must be a perfect ratio between the two that will utilize all of A and B. Using just one or the other will result in the other ratio not being maintained at the end.

 

[Mackers]

Oh, what you want is a combination of your two compounds that will fully use up both A and B, right?

Note that this is not always possible, for example trivially if you have 0 A and 10 B and both combos use both A and B you will not be able to devise a way to solve this.

What you need is to determine the ratio of A to B you are given, then determine the ratio of A and B used by each of the compounds, then determine the best mid-point, right? That is more a maths question than an Excel one, but shouldn't be too hard to work out.

 

[Frosty1419]

Yes that the idea and you're right about it not being always possible.

I just didn't know if there were any functions in excel that would facilitate finding the "ratio of the ratio's" if you will.

If there's nothing special in excel I will just work out the math to get there.

Thanks for your help.