Average IF Function

's Computer Specifications

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!

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:B74ED6DC-6F56-4A28-94C4-(E-Mail Removed)...
> 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!

's Computer Specifications

Add "<>0" as the 1st criteria.

--
Regards
Dave Hawley
www.ozgrid.com
"SandiVaughn" <(E-Mail Removed)> wrote in message
news:B74ED6DC-6F56-4A28-94C4-(E-Mail Removed)...
> 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!

's Computer Specifications

Try it like this...

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

--
Biff
Microsoft Excel MVP

"SandiVaughn" <(E-Mail Removed)> wrote in message
news:B74ED6DC-6F56-4A28-94C4-(E-Mail Removed)...
> 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!

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Re: A useful AVERAGE calculation without using the built-in AVERAGE function	קובי	Microsoft Excel New Users	0	3rd Feb 2011 04:25 PM
Re: A useful AVERAGE calculation without using the built-in AVERAGE function	Roger Govier	Microsoft Excel New Users	0	16th Jan 2011 01:20 PM
Error Handling #N/A with AVERAGE Function - Average of values in Row	Sam via OfficeKB.com	Microsoft Excel Worksheet Functions	13	31st Jul 2005 03:59 PM
How do I nesting subtotal function within average function in Exc	=?Utf-8?B?QW15IFllaA==?=	Microsoft Excel Worksheet Functions	4	19th Jan 2005 06:23 PM
convert =IF(ISERROR(AVERAGE(V10:V13)),"",AVERAGE(V10:V13)) to a function	Niki Odolphi	Microsoft Excel Programming	5	20th Mar 2004 10:11 PM