Using Excel to find Geometric Mean

Welcome to the forum. You might benefit from reading the part of the forum rules headed "How to get your question answered quickly."

There's a link in the menu bar: http://www.excelforum.com/forum-rule...rum-rules.html. A portion of that reads,

Want to get your question answered quickly?

•Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers.

•On the other hand, skip irrelevant details. Be descriptive and concise. Short, direct, and to-the-point questions with apt thread titles are almost always answered promptly.

•Keep the scope reasonably narrow. Questions like, "How do I set up an accounting system in Excel?" might be a long time waiting.

•Explain what you've already tried. ("Calculation is set to automatic, but formulas still don't compute") so helpers don't waste your time or theirs.

•Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

•If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

•Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers.

•On the other hand, skip irrelevant details. Be descriptive and concise. Short, direct, and to-the-point questions with apt thread titles are almost always answered promptly.

•Keep the scope reasonably narrow. Questions like, "How do I set up an accounting system in Excel?" might be a long time waiting.

•Explain what you've already tried. ("Calculation is set to automatic, but formulas still don't compute") so helpers don't waste your time or theirs.

•Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

•If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

So are you saying that you have a range of cells, let's say A1:A10 and you want to calculate the geometric mean?

=PRODUCT(A1:A10)^(1/COUNT(A1:A10)
Does that work for you?

Hi ChemistB,

Not exactly. Let me try to explain further.

Date A B C D E F G H
21/09/2014 0 0 0 0 0 0 0 0
22/09/2014 0 10 0 0 0 0 0 0
23/09/2014 0 0 0 0 0 0 0 0
24/09/2014 0 0 0 0 0 0 0 0
25/09/2014 0 0 0 0 0 0 0 0
26/09/2014 0 25 0 35 0 0 0 0
27/09/2014 0 0 0 0 0 0 0 0
28/09/2014 0 0 0 0 0 0 0 0
29/09/2014 0 0 0 0 0 0 0 0
30/09/2014 0 18 0 0 0 26 0 0
01/10/2014 0 0 0 0 0 0 0 0
02/10/2014 0 0 0 0 0 0 0 0
03/10/2014 0 0 0 0 0 0 0 0
04/10/2014 0 0 0 0 0 0 0 0

Say for example I have 5 tables similar to the table above not having the same value in each cell. Please find a screen shot attached

Taking a particular example: I need to find the geometric mean of all available B:2 cells from every table.
Then find the geometric mean of all available B:3 cells from every table,
then find the geometric mean of all available B:4 cells from every table

having all these values will built a single matrix being filled with the geometric averages of the respective cells.

Does this explains better?Screen Shot 2014-11-12 at 23.18.28.png

Since there is not a built in "geometric mean if" function, here's how I would probably do it:

1) Create a helper column. Because the built in =GEOMEAN() function will ignore text values, I would use a function in this helper column that would return a text string (like "" or other) for those values that did not meet "criteria". Something like =IF(A2=date,B2,"notused") where date refers to a cell that contains the date of interest.
2) Then, another cell could be added with the built in GEOMEAN() function. =GEOMEAN(F2:F22), assuming F contained the helper column.

There are a lot of different variations around that kind of theme, so figure out the details of how you would like to do it, but that is the basic approach I would use.

Help file for GEOMEAN() https://support.office.com/en-us/art...3-0b38980e40d5