Averages with blank cells and specified criteria

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Peo Sjoblom]

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

--

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

 

[Guest]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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
If not use

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

entered with ctrl + shift & enter

--

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