ExcelBanter - Average IF Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Average IF Function (https://www.excelbanter.com/excel-worksheet-functions/262807-average-if-function.html)

SandiVaughn

Average IF Function

How can you test a range of cells to be greater than or less than another
cell using the average if function?

Ex: =AVERAGEIFS(A1:A4,A1:A4,"A6",A1:A4,"A7")

Keep getting the #DIV/0! error!

HELP!!!!! Thank you!

Ashish Mathur[_2_]

Average IF Function

Hi,

See if it works by removing the double quotes. Else, try this

=sumproduct((A1:A4=A6)*(A1:A4<=A4)*(A1:A4))/sumproduct((A1:A4=A6)*(A1:A4<=A4))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"SandiVaughn" wrote in message
...
How can you test a range of cells to be greater than or less than another
cell using the average if function?

Ex: =AVERAGEIFS(A1:A4,A1:A4,"A6",A1:A4,"A7")

Keep getting the #DIV/0! error!

HELP!!!!! Thank you!

ozgrid.com

Average IF Function

Add "<0" as the 1st criteria.

--
Regards
Dave Hawley
www.ozgrid.com
"SandiVaughn" wrote in message
...
How can you test a range of cells to be greater than or less than another
cell using the average if function?

Ex: =AVERAGEIFS(A1:A4,A1:A4,"A6",A1:A4,"A7")

Keep getting the #DIV/0! error!

HELP!!!!! Thank you!

T. Valko

Average IF Function

Try it like this...

=AVERAGEIFS(A1:A4,A1:A4,"="&A6,A1:A4,"<="&A7)

--
Biff
Microsoft Excel MVP

"SandiVaughn" wrote in message
...
How can you test a range of cells to be greater than or less than another
cell using the average if function?

Ex: =AVERAGEIFS(A1:A4,A1:A4,"A6",A1:A4,"A7")

Keep getting the #DIV/0! error!

HELP!!!!! Thank you!

All times are GMT +1. The time now is 06:25 PM.