GEOMEAN with IF(AND statement

[koplover]

Hello

I am having issues with getting a Geomean Array to work. I have a list of dates in column A and results in column B on one sheet. I am trying to code a formula to that looks a date 1 and date 2(cell E1 and E2) and Geomeans all the results with dates between date 1 and date 2. Here is what I have

{=GEOMEAN(IF(AND($A$3:$A$500>=$E$1,$A$3:$A$500<=$E$2),$B$3:$B$500,""))}

This returns #value !

I believe the issue is with the AND statement, If I remove that and just look at 1 date field it works.

Thanks for your help

 

[Tetra201]

Try

=GEOMEAN(IF(($A$3:$A$500>=$E$1)*($A$3:$A$500<=$E$2),$B$3:$B$500,""))

 

[koplover]

Thanks for he quick response Tetra when you say * do you mean &? Either way did not work.

After spending some more time on it, I don't believe its the AND statement that is the issue (I might be wrong). If I narrow the range from 3-500 to a range that only has dates inside of date 1 and 2 it works. As soon as it encounters a date outside of that, it fails.

 

[Aladin Akyurek]

@ koplover

AND of Excel delivers just a single value result, i.e. a logical value, not an array result. Thus a TRUE or a FALSE, not a bunch TRUE or FALSE, each corresponding to an evaluatin.

An AND can be written in terms of a multiplication or in terms of IF...

1.

{=GEOMEAN(IF(($A$3:$A$500>=$E$1)*($A$3:$A$500<=$E$2),$B$3:$B$500))}

2.

=GEOMEAN(IF($A$3:$A$500>=$E$1,IF($A$3:$A$500<=$E$2,$B$3:$B$500)))