# AVERAGE(IF(AND(...?

#### mvatoi

##### Board Regular
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Gerald Higgins

##### Well-known Member
It works for me . . . returns 5.

Have you remembered to CTRL-SHIFT-ENTER ?

#### jim may

##### Well-known Member
change B1 to 4 and C1 to 9; It seems to be fixed on 5, regardless...

#### Greg Truby

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

Right On Greg!!

#### mvatoi

##### Board Regular
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

##### MrExcel MVP
...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.

Replies
8
Views
298
Replies
3
Views
483
Replies
7
Views
505
Replies
2
Views
442
Replies
2
Views
337

1,191,191
Messages
5,985,216
Members
439,948
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back