# 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?

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?

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

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

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

For excel formulas, that can be written as

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]

*(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

=((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

posted by aimedwander at 9:35 AM on May 19 [1 favorite]

*=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]

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

posted by jmfitch at 3:27 AM on May 18 [2 favorites]