Averages with blank cells and specified criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 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
 
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
 
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
 
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)

MaryH said:
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

Peo Sjoblom said:
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
Click to expand...
Click to expand...
 
Back
Top