Hi again,
It's late here ... but I can't see anything wrong with the
formula. I have retested your originals with Tom's changes and they work
fine. I then modified the original AVERAGE one to reflect your new one and
it also works OK: if I take out the '<0' test then 0s are included (as
expected) but removed if the test is included.



"marika1981" wrote:

Thank you all so much for your help.....I *very* much appreciate it.

However, I'm still unable to get the AVERAGE formula to ignore zero values.
I've checked the formula I entered four or five times - here it is on the tab
i'm currently working on:

=AVERAGE(IF((KREMonthRange=F$16)*(KREYearRange=F$1 5)*(KREAltaVista<0)*(KREAltaVista<""),(KREAltaVi sta)))

(I do get an answer - but it's the average including zeros)

What have I got wrong?????

Marika :)



"Tom Ogilvy" wrote:

=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<""))

I stand corrected on Toppers formula. I did not see the "IF" in the
formula. Anyway if you want to exclude zeros or blanks if a cell in
CNTXTOrders would otherwise be included, then:

=AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)*
(CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)*
(CNTXTOrders<""),CNTXTOrders)


--
Regards,
Tom Ogilvy


"marika1981" wrote in message
...
Thank you so much Vasant and Toppers - your suggestions *virtually* did
the
trick, though I have two small problems (one of which Tom alluded to)

1) When using the AVERAGE(IF) solution Toppers offered:

=AVERAGE(IF(--(CNTXTName="Shared

Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))

it does include all zero values.

2) When using the count solution from Vasant:

=SUMPRODUCT(--(CNTXTName="Shared

Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

it counts all zero values and all blank cells.

Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for
the
count formula???

I can't thank you enough - you've saved my day!

Marilka :)


"Tom Ogilvy" wrote:

That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus
why
use both mulitplication and double negatives?

--
Regards,
Tom Ogilvy

"Toppers" wrote in message
...
Average :

=AVERAGE(IF(--(CNTXTName="Shared


Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1
),CNTXTOrders))



"Vasant Nanavati" wrote:

(Untested):

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4))

should give you the average.

=SUMPRODUCT(--(CNTXTName="Shared


Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),--(CNTXTOrders<=10))

should give you the number of occurrences less than or equal to 10.

--

Vasant




"marika1981" wrote in message
...
I'm currently extracting data from a larger table to a smaller
summary
table
(on a different tab) using the SUMPRODUCT function in this form
(an
example):

=SUMPRODUCT(--(CNTXTName="Shared



Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1
4),CNTXTOrders)

to add the values of CNTXTOrders in the rows that meet the
preceeding
given
conditions in the formula.

QUESTION: I need to find a way to do the same operation but
AVERAGE
the
values (instead of summing them) and COUNT the occurence of values
that
are
equal to or less than 10.

Can anyone advise on a way to do this??? Unfortunately this is
part
of an
elaborate spreadsheet, so I'm structurally limited (can't
rearrange
the
data).

Any advice would be so much appreciated as I'm already past my
deadline.

Many, many thanks,

Marika :) :)