AVERAGE(IF(AND(...?

[mvatoi]

Hi all

I try combine AVERAGE(IF(AND..., But it doesn't work, any recommendation?

I only have 1 column in A, for example

A1: A10 will be #s from 1 to 10, And criteria B1=2, C1=8


I want to take average of all #s less than 8

{=AVERAGE(IF(A1:A10<=C1),A1:A10)} <<<< this WORKS



But when I want to take the average of all #s from 2-8

{=AVERAGE(IF(AND(A1:A10>=B1),(A1:A10<=C1)),A1:A10)} <<< Does NOT WORK

any help would be appreciated

 

[Gerald Higgins]

It works for me . . . returns 5.

Have you remembered to CTRL-SHIFT-ENTER ?

 

[jim may]

change B1 to 4 and C1 to 9; It seems to be fixed on 5, regardless...
answer s/b 6.5

 

[Greg Truby]

This works for me:<ul>[*]{=AVERAGE(IF(((A1:A10>=B1)*(A1:A10<=C1)),A1:A10))}[/list]

 

[jim may]

Right On Greg!!

 

[mvatoi]

I came up with a long formula but it also workS

{=(SUM(IF(A1:A10<=C1,A1:A10))-SUM(IF(A1:A10<B1,A1:A10)))
/(COUNTIF(A1:A10,"<="C1)-COUNTIF(A1:A10,"<"&B1))}


Greg formula seems to work also.

Gerald, I did use CONTROL+SHIFT+ENTER but somehow it didn't work for me

THANKS GUYS, ya'all GREAT!!!


***I have to edit this post twice, not sure why the formula I wrote above didn't posted in full, missing a couple parenthesis or missing part of the formula. but basically the formula is

(Sum (<8) - Sum (<2)) / (Count (<8)-count (<2))

 

[Greg Truby]

...not sure why the formula I wrote above didn't posted in full, missing a couple parenthesis or missing part of the formula...
~mvatoi

The board software sometimes gets overzealous about parsing and considers stuff between a less than sign and a greater than sign to be a tag. You can avoid this by

• Placing the formula inside CODE tags.
• Checking the disable HTML in this post checkbox prior to clicking Submit.