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?
Response by poster: 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, 2017
posted by Literaryhero at 5:06 AM on May 18, 2017
=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, 2017
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, 2017
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, 2017 [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, 2017 [3 favorites]
Response by poster: 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, 2017
=((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, 2017
Best answer: 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, 2017 [1 favorite]
posted by aimedwander at 9:35 AM on May 19, 2017 [1 favorite]
This thread is closed to new comments.
posted by jmfitch at 3:27 AM on May 18, 2017 [2 favorites]