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!!!

no, nothing less than zero

"T. Valko" wrote:

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!!!

Try this:

Create these named formulas...

InsertNameDefine
Name: SumSheets
Refers to:

=SUMIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\da ta")&"!B2"),"0")

Name: CountSheets
Refers to:

=COUNTIF(INDIRECT(TEXT(30*{6,7,8,9,10,11,12},"mmm\ data")&"!B2"),"0")

Then, you average formula is:

=SUMPRODUCT(SumSheets)/SUMPRODUCT(CountSheets)

Explanation:

Excel doesn't support *conditional* averaging across multiple sheets so we
need to trick it into doing so. We can't use the AVERAGE function in this
case. Since an average is the sum divided by the count that's what we're
doing with the above formula(s).

If you want to take the "easy" way out on this, on each sheet in the same
cell enter a formula like this:

=IF(B20,B2,"")

Let's assume those formulas are in cell B3. Then, you can use the AVAERAGE
function like this:

=AVERAGE(jundata:decdata!B3)

--
Biff
Microsoft Excel MVP


"klic33" wrote in message
...
no, nothing less than zero

"T. Valko" wrote:

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!!!