combining ratios

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
Back
Top