I want an excel formula that will subtotal a column of numbers if
certain conditions are met.

Using the subtotal function, (because I have hidden rows, and am using
filtering) calculate the average of numbers that are not outliers or
invalid
(text, #value errors, etc.)

For example

In a spreadsheet that has 300 rows, and rows 150 through 155 are visible
These numbers appear:
24
450
13
20
40

Calculate the average of the values that are less than 80
The answer should be 24.25 (97 / 4), not 109.4 (547 / 5)

I have experience with the SUBTOTAL function, and I can sum, count,
average the correct cells, but dont seem to be able to add conditions
(such as, if the value is <80 or >0, etc.).

I DO NOT want to have fixed formulas (like an array only for cells
155:160)that require me to select rows 155:160, because I may want to
filter on rows 133:175 next time, so the SUBTOTAL formula (or an
equivalent) is important.

I dont know much about VBA and Macros, so if the answer can fit into a
cell, that would be great

Thank you

---

Request for Question Clarification by maniac-ga on 12 Jul 2004 17:14 PDT

Hello Hammerhead222,

Based on what you've stated, I assume the filtering you do is
insufficient to remove all the outliers / invalid values. Please
confirm.

If the answer to that is yes - a macro may be required

A macro can be written as a function - so you would enter something like:
  =mysubtotal(min, max, reference)
in the place you currently use subtotal. The "min" and "max" values
would be entered to get rid of the outliers. The reference would be
the range of cells to process (including hidden cells). If I provided
a function to do that with step by step instructions to enter / use
it, would that be a suitable answer?

  --Maniac

Hello, just lurking and I think I have an answer for you...

Use the sumif and countif functions...

=SUMIF(A1:A5,"<80")/COUNTIF(A1:A5,"<80")

The criteria reference can be to an input cell or other calculated result.

Good luck.

Hi,
The main issue here is to compute on visible rows only. Besides
SUBTOTAL, i don't think there is any other function. Working with
multiple criteria is not a problem.
The SUMIF function totals hidden and visible rows, and for 1 criterion only.
The SUMPRODUCT totals hidden and visible rows with multiple criteria:
 (1)  = SUMPRODUCT ( (A1:A10<80) * (A1:A10>0) * (B1:B10) )
   --> sum (B1:B10) where 0<A1:A10<80
   or
 (2)  = SUMPRODUCT ( (A1:A10<80) * (A1:A10>0) * (B1:B10) ) / =
SUMPRODUCT ( (A1:A10<80) * (A1:A10>0) * 1 )
   ---> average of B1:B10 for rows where 0<A1:A10<80
Maybe a solution would be to look into defining the range to be summed
based on visble rows only, say VisRange that named range based on
column A:
 (1)  = SUMPRODUCT ( (VisRange<80) * (VisRange>0) * (OFFSET(VisRange,0,1)) )
To define VisRange, maybe look into Excel4 macro type. Many of these
functions cannot be used directly but some can be used as an
expression of a named range. Try defining VisRange this way.

I'll look into it next week when i have time.
Regards,
Sebastien

My suggestion would be to use another column to slect the numbers that are <80.

In column B enter the formula =if(a1<80,a1,"")

Then you can do the calculation using this column (which can be hidden)!

Don't know if this'll suit your needs though!

Cheers!

Don't know what version of Excel you're using, but it looks like
you're best served by using a macro.  At least, that's what Microsoft
says :)

"How to Use a VBA Macro to Sum Only Visible Cells"
http://support.microsoft.com/?kbid=150363

Should be straight forward.