combining ratios
 

I have a series of ratios that I need to combine by adding all the numerators
and then dividing by the sum of all the denominators. See below:

a/b c/d e/f sum=(a+c+e)/(b+d+f)

Unfortunately, the components are in single cells, e.g., cell A2 is "=a/b"
or else this would be an easy solution. (To make things worse, each
component (a, b, c, etc.) is actually a link to an external cell with the
actual data.) How can is extract the components in oder to perform the
operation? Thanks in advance.

combining ratios
 

columnA
columnB
row1 =IF(LinkSheet!A1<"",LinkSheet!A1,"")
row2 =IF(LinkSheet!A2<"",LinkSheet!A2,"") =SUM(A2+C2+E2)/(B2+D2+F2)

In the sheet you are working in, select an area where you can bring in the
data from the link (LinkSheet)to work with, as above,
Or can you use the formula directly to the link

=SUM(LinkSheet!A2+LinkSheet!C2+LinkSheet!E2)/(LinkSheet!B2+LinkSheet!D2+LinkSheet!F2)
HTH

"andy62" wrote:

I have a series of ratios that I need to combine by adding all the numerators
and then dividing by the sum of all the denominators. See below:

a/b c/d e/f sum=(a+c+e)/(b+d+f)

Unfortunately, the components are in single cells, e.g., cell A2 is "=a/b"
or else this would be an easy solution. (To make things worse, each
component (a, b, c, etc.) is actually a link to an external cell with the
actual data.) How can is extract the components in oder to perform the
operation? Thanks in advance.

combining ratios
 

I wasn't clear that the summing formula is created beforehand, and I wouldn't
know the exact text of the links in advance. So I need a formula that can
extract the numerators from the denominators by maybe extracting all the text
before the "/" sign separately from the text after it:

A1 = LinkSheetQ!B4/LinksheetQ!B5
A2 = LinkSheetW!C4/LinksheetW!C9
SUM = all the numerators (not yet coded)/all the denominators (not yet coded)

Is there any eaasy way, or would I use something like LEFT and FIND to
extract the tops and RIGHT and FIND to extract the bottoms? Thanks.

"ufo_pilot" wrote:

columnA
columnB
row1 =IF(LinkSheet!A1<"",LinkSheet!A1,"")
row2 =IF(LinkSheet!A2<"",LinkSheet!A2,"") =SUM(A2+C2+E2)/(B2+D2+F2)

In the sheet you are working in, select an area where you can bring in the
data from the link (LinkSheet)to work with, as above,
Or can you use the formula directly to the link

=SUM(LinkSheet!A2+LinkSheet!C2+LinkSheet!E2)/(LinkSheet!B2+LinkSheet!D2+LinkSheet!F2)
HTH

"andy62" wrote:

I have a series of ratios that I need to combine by adding all the numerators
and then dividing by the sum of all the denominators. See below:

a/b c/d e/f sum=(a+c+e)/(b+d+f)

Unfortunately, the components are in single cells, e.g., cell A2 is "=a/b"
or else this would be an easy solution. (To make things worse, each
component (a, b, c, etc.) is actually a link to an external cell with the
actual data.) How can is extract the components in oder to perform the
operation? Thanks in advance.