Re: averaging based on multiple criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Julz (jhbiz_at_austin.rr.com)
Date: 08/01/04 

Date: Sun, 01 Aug 2004 07:01:38 -0500

#1 as an array
{=AVERAGE(IF(D2:D19000="n",K2:K19000))}

#2
=SUMPRODUCT(--(D2:D19999="n"),--(K2:K19999<16))/COUNTIF(D2:D19999,"n")

row 1 contains column headers which is why I'm using D2, K2, etc.

also, this spreadsheet is populated with new data weekly where there is
a variance between how many rows will contain data, which is why I'm
using 19999.

Hope this helps.

Frank Kabel wrote:

> Hi
> sorry the second one had a missing bracket. Should read:
> =SUMPRODUCT(--(D1:D100="n"),--(K1:K100<16))/COUNTIF(D1:D100,"n")
>
> But they shouldn't return a #REF error
> What are the exact formulas you have used
>
>
>

Relevant Pages

  • Rounding Issues
    ... I have to tally the total amount ... charged to each agency. ... The spreadsheet is sorted by account number and I can't ... array are then multiplied, ...
    (microsoft.public.excel.worksheet.functions)
  • Totals on Forms
    ... There doesn't seem to be a way put the *fields* into an array for easy ... just cycling through the cells themselves. ... The advantage of the control source method, as I see it, ... through every cell to put the information back into the spreadsheet. ...
    (microsoft.public.excel.programming)
  • Re: Rounding issues
    ... I have to tally the total amount ... charged to each agency. ... spreadsheet is sorted by account number and I can't alter ... array are then multiplied, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Adding a row to a table (again)
    ... I have a spreadsheet for tracking mileage on my car. ... This array, then, is B13:D20. ... All references the last row be updated to the enw last row ...
    (microsoft.public.excel.newusers)
  • RE: VLOOKUP Function using Data Ranges.
    ... an external reference to an array in another spreadsheet (assume the ... spreadsheet is in the same directory on the same computer). ... > What is very time consuming is this "coding" part. ... > according to column 2 in my ranged data table. ...
    (microsoft.public.excel.worksheet.functions)