calculating averages and using conditional "if" statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

achieve - 08 Mar 2007 20:56 GMT

i am attempting to average a range of cells, in which each cell value cannot
be above a certain quantity. if a cell value is above the set quantity, i
want for excel to calculate the average, excluding the cell reference that
contains the value above the set qunatity. in words, this is what i want to
say (in the numbers/quantitative statement):

if any cells in the range c8:du8 are <4, calculate the average of cells
c8:du8; however, if any cells in this range are >4, calculate the average of
the range, BUT exclude those cells (with values greater than 4) from the
formula.

Reply to this Message

T. Valko - 08 Mar 2007 21:22 GMT

If the values are <4 do this
If the values are >4 do that

What if the values ARE 4?

Biff

>i am attempting to average a range of cells, in which each cell value
>cannot
[quoted text clipped - 9 lines]
> the range, BUT exclude those cells (with values greater than 4) from the
> formula.

Reply to this Message

Dave F - 08 Mar 2007 21:34 GMT

=SUMIF(...)/COUNTIF(...) allows you to specify the constraint <4.

Recall that AVERAGE is merely SUM/COUNT.

Dave

Signature

A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

> i am attempting to average a range of cells, in which each cell value cannot
> be above a certain quantity. if a cell value is above the set quantity, i
[quoted text clipped - 6 lines]
> the range, BUT exclude those cells (with values greater than 4) from the
> formula.

Reply to this Message