View Single Post
  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Can you AVERAGE IF and not null?

Will there ever be any negative numbers?

--
Biff
Microsoft Excel MVP


"klic33" wrote in message
...
My situation is similar but I haven't been able to customize this to work
like I thought I should be able to.
I have one cell in multiple tabs I that want to include in the average, as
long as they <0. If anyone of them <0, then I do not want that
particular
cell to be factored into the result because it skews the average (because
it's a month that hasn't occured yet so the data is 0). Here's my
(nonworking) formula if anyone can help:

=average(if(jundata!b2,juldata!b2,augdata!b2,sepda ta!b2,octdata!b2,novdata!b2,decdatab2<""),jundata !b2,juldata!b2,augdata!b2,sepdata!b2,octdata!b2,no vdata!b2,decdata!b2)


"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((A3:A45="Photo")*(H3:H45<""),H3:H45))

In article ,
"Spottkitty" wrote:

=AVERAGE(IF($A$3:$A$45="Photo",H3:H45))

This is the array I'm using to try to determine an average.
Problem...if
the field is blank it's counting it as zero and lowering the results.
It
figured 79% when it should have been 94%. 94% was returned using the
simple
average formula. I'm guessing I need to nest something to not count
nulls?
Help!!!