Excel/Google Sheets Formula Help
May 18, 2017 2:54 AM   Subscribe

I am not sure how to ask this, so it is probably easily available on Google and I just can't find it. However, I am trying to make a Google Sheet (or Excel Doc, it doesn't matter which) that does something specific.

I have three rows of numbers (let's say 1, 2, 3), and a variable number of columns (A, B, C and so on). What I want to find is a formula that will do something like:

=((A1/A2)*A3)*((B1/B2)*B3)*((C1/C2)*C3)/[Number of columns]

As you can see, if I had a definite number of columns I could just make the formula as above. However, I would like to make something that can automatically adapt to new columns being added, and that also wouldn't become exceedingly long if I were to manually enter new columns. Is there a formula that can do this?
posted by Literaryhero to Computers & Internet (6 answers total)
 
If the number of rows is fixed, you could place your base 'n1 / n2 * n3' formula in each column, then use the 'product' and 'count' formulas to figure the end result; something like '=product (An:Cn) / count (An:Cn)'. Pretty sure this would do it...
posted by jmfitch at 3:27 AM on May 18 [2 favorites]


That does answer the question, but I was hoping to avoid the extra step. Oh well, if this is the best I can do I can live with it.
posted by Literaryhero at 5:06 AM on May 18


=PRODUCT(A:D)/COUNTA(A1:D1)

This would multiply all the values in those four columns and divide by the total number of columns with a value in the top row. Make it as wide as you expect to potentially add columns (G, X, AA, whatever) at the beginning and you shouldn't have to redo it.
posted by Apoch at 5:22 AM on May 18


There's nothing in the example formula that doesn't multiply through, i.e. what you wrote column by column is equal to
(A1*B1*C1*...)*(A3*B3*C3*...)/(A2*B2*C2*...)/number of columns).


For excel formulas, that can be written as
PRODUCT(1:1)*PRODUCT(3:3)/(PRODUCT(2:2)*COUNT(1:1))

The key piece of Excel vocabulary here is using (1:1) to mean "everything in row 1"
posted by aimedwander at 7:09 AM on May 18 [3 favorites]


Gah! I wrote the formula wrong. Shame shame shame on me. I want to average the results from each column so they should be added:

=((A1/A2)*A3)+((B1/B2)*B3)+((C1/C2)*C3)/[Number of columns]

So you guys are right but I asked the wrong question. :(
posted by Literaryhero at 2:21 PM on May 18


In that case yes, you probably do have to have row 4 where A4=A3*A1/A2 in every column. But then you can just say =AVERAGE(4:4) and that will include as many columns as you happen to have.
posted by aimedwander at 9:35 AM on May 19 [1 favorite]


« Older Apartment of Banister   |   Synonyms that rhyme Newer »

You are not logged in, either login or create an account to post comments