Averages with blank cells and specified criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MaryH - 23 May 2006 22:24 GMT

Hi, I am trying to develop a formula to have attendance reports and averages.
I want to divide the groups into adults, youth and children. I have a column
identifying the group each person is in (A Y or C).

I have a worksheet for each program (wed, thurs, fri or sun) and i want to
tally the totals on another sheet and then make an overall statistics sheet.

I am using =SUMIF(Sunday!B:B,"A",Sunday!E:E) on the initial stats but it is
coming up as an error.

I am using =AVERAGE(IF('Initial Stats '!4:4>0, 'Initial Stats '!4:4,""))

I thought I had everything set fine and when I opened the program today to
start entering data there were errors everywhere.

Thanks, Mary

Reply to this Message

Bob Phillips - 23 May 2006 23:15 GMT

Mary,

That is an array formula, so you need to confirm those formulae with
Ctrl-Shift-Enter, not just Enter.

You can also use just

=AVERAGE(IF('Initial Stats '!4:4>0, 'Initial Stats '!4:4))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> Hi, I am trying to develop a formula to have attendance reports and averages.
> I want to divide the groups into adults, youth and children. I have a column
[quoted text clipped - 12 lines]
>
> Thanks, Mary

Reply to this Message

MaryH - 24 May 2006 03:42 GMT

Hi Bob,

It didn't work. If I put a comma between the 4 and >0 I get a wrong formula.

If I do not put the comma in, I have no results at all. Excell allows me to
enter the formula without an "error" but there are no results either.

Mary

Reply to this Message

Peo Sjoblom - 24 May 2006 03:55 GMT

Why don't you just copy Bob's formula, btw do you really have a trailing
space in the sheet name
If not use

=AVERAGE(IF('Initial Stats'!4:4>0, 'Initial Stats'!4:4))

entered with ctrl + shift & enter

Signature

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

> Hi Bob,
>
[quoted text clipped - 6 lines]
>
> Mary

Reply to this Message

MaryH - 24 May 2006 04:10 GMT

Hi Peo,

I needed to put in a third element and now it works.

=AVERAGE(IF('Initial Stats'!4:4>0, 'Initial Stats'!4:4,"")) I needed the
[value_if_false] which is the ""

I did copy Bob's formula and it did not work. This new one worked.

Thanks, I would not have been successful without your help.

Mary

> Why don't you just copy Bob's formula, btw do you really have a trailing
> space in the sheet name
[quoted text clipped - 14 lines]
> >
> > Mary

Reply to this Message

Bob Phillips - 24 May 2006 08:07 GMT

You don't need the ,"", it must have been the trailing space as Peo
suggested.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hi Peo,
>
[quoted text clipped - 27 lines]
> > >
> > > Mary

Reply to this Message