Calculating Geomean

**dmw3293** · 11-13-2011, 12:03 AM

i need to calculate geomean for approximately 20000 cells in a column. the problem is there are blank spaces within the rows. i am not sure if there are any zeroes however i know this can be avoided with the if function and im pretty sure there arent any negatives. i know that the data is probably too much to use geomean alone and i used a formula array along the lines of =exp(average(ln(a1:a20000))) and used ctrl shift enter, yet i still get the #### error. what do i do?

**shg** · 11-13-2011, 01:14 AM

Try

=geomean(if(a1:a20000>0, a1:a20000))

... confirmed with Ctrl+Shift+Enter.

**dmw3293** · 11-13-2011, 02:12 AM

Originally Posted by shg

Try

=geomean(if(a1:a20000>0, a1:a20000))

... confirmed with Ctrl+Shift+Enter.

i tried this and got #### error again =/

**shg** · 11-13-2011, 03:39 PM

The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

**martindwilson** · 11-13-2011, 04:25 PM

#### error whats that? afaik ### only shows if your column is too narrow or you are using negative dates

**dmw3293** · 11-13-2011, 06:47 PM

Originally Posted by martindwilson

#### error whats that? afaik ### only shows if your column is too narrow or you are using negative dates

it says a value used in the formula is of the wrong data type. shouldnt this be covered if i input if(a1:a20000>0,a1:a20000)? and i used ctrl shift enter to put in the array formula