I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."

So this needs to change:
=IFERROR(G18/F18*(100),"")

http://www.excelfunctions.net/Excel-...-Function.html

On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html

Thanks Jim although I still cant work it out from that.
I need to add something to my formula so that a blank cell does not
contain an invisible zero which is then picked up and used to count my
averages. Excuse my terminology but not too clever with excel stuff.

On 06/05/2015 16:24, ss wrote:
On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html

Thanks Jim although I still cant work it out from that.
I need to add something to my formula so that a blank cell does not
contain an invisible zero which is then picked up and used to count my
averages. Excuse my terminology but not too clever with excel stuff.

You can do a countblank (or 'if ', etc), and simply deduct or add that
to your subtotal. I tend to do this sort of thing using a load of clumsy
equations in brackets. Works best for me as I don't use Excel that often.

--
Cheers, Rob

On 06/05/2015 16:46, RJH wrote:
On 06/05/2015 16:24, ss wrote:
On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html

Thanks Jim although I still cant work it out from that.
I need to add something to my formula so that a blank cell does not
contain an invisible zero which is then picked up and used to count my
averages. Excuse my terminology but not too clever with excel stuff.

You can do a countblank (or 'if ', etc), and simply deduct or add that
to your subtotal. I tend to do this sort of thing using a load of clumsy
equations in brackets. Works best for me as I don't use Excel that often.



I'm lost, because the OP seems to be making this rather more complicated
than it seems to need to be. What's wrong with a formula like this one,
using the correct function for the job?

=COUNTIF(B1:B19,"0")

On Wed, 06 May 2015 17:31:35 +0100, GB wrote:

On 06/05/2015 16:46, RJH wrote:
On 06/05/2015 16:24, ss wrote:
On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html

Thanks Jim although I still cant work it out from that.
I need to add something to my formula so that a blank cell does not
contain an invisible zero which is then picked up and used to count my
averages. Excuse my terminology but not too clever with excel stuff.

You can do a countblank (or 'if ', etc), and simply deduct or add that
to your subtotal. I tend to do this sort of thing using a load of
clumsy equations in brackets. Works best for me as I don't use Excel
that often.



I'm lost, because the OP seems to be making this rather more complicated
than it seems to need to be. What's wrong with a formula like this one,
using the correct function for the job?

=COUNTIF(B1:B19,"0")

Because that simply gives you the number of cells that are 0, rather
than the avereage of the contents of cells that are 0.

"ss" wrote in message
...
On 06/05/2015 16:02, JimK wrote:
http://www.excelfunctions.net/Excel-...-Function.html

Thanks Jim although I still cant work it out from that.
I need to add something to my formula so that a blank cell does not
contain an invisible zero which is then picked up and used to count my
averages. Excuse my terminology but not too clever with excel stuff.

http://excelribbon.tips.net/T007729_...ro_Values.html

On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."


Normally when doing this kind of thing I would compute the cells you are
averaging conditionally:

=IF( expression 0, expression, " ")

That will fill any zero results with a blank cell rather than a zero.


--
Cheers,

John.

/================================================== ===============\
| Internode Ltd - http://www.internode.co.uk |
|-----------------------------------------------------------------|
| John Rumm - john(at)internode(dot)co(dot)uk |
\================================================= ================/

On 06/05/2015 18:13, John Rumm wrote:
=IF( expression 0, expression, " ")

Maybe this will help explain it better.
The formula at the top should pick up under the % heading the number of
cells that have an entry. As you can see only 3 of those cells have an
entry which returns arrowed 58.75 which is the wrong answer.

The calculation should be 100+60+75 = 235 / 3 = correct answer of 78.3


http://i64.photobucket.com/albums/h1...psql11doqn.jpg

http://i64.photobucket.com/albums/h1...psql11doqn.jpg

Why does that link start a Google playstore app and take my android tablet to a game download?

Jim K

On 06/05/2015 21:45, JimK wrote:
http://i64.photobucket.com/albums/h1...psql11doqn.jpg

Why does that link start a Google playstore app and take my android tablet to a game download?

Jim K
I have no idea it takes me to the image.

On 06/05/2015 18:13, John Rumm wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."


Normally when doing this kind of thing I would compute the cells you are
averaging conditionally:

=IF( expression 0, expression, " ")

That will fill any zero results with a blank cell rather than a zero.


Agreed.

Also, if the formulae get complicated it is often worth adding one or
more intermediate columns with simpler formulae in them. Easier to
understand and debug. You can always hide them when it's working properly.

On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."

So this needs to change:
=IFERROR(G18/F18*(100),"")

The Excel newsgroups were always my first port of call. Not in the least
nerdy

microsoft.public.excel.worksheet.functions

I'm sure you'll get an elegant solution there ....if the group still exists

On 06/05/2015 19:09, stuart noble wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."

So this needs to change:
=IFERROR(G18/F18*(100),"")

The Excel newsgroups were always my first port of call. Not in the least
nerdy

microsoft.public.excel.worksheet.functions

I'm sure you'll get an elegant solution there ....if the group still exists

I used that a lot in the past but pretty dead these days.

In article , ss
writes
On 06/05/2015 19:09, stuart noble wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."

So this needs to change:
=IFERROR(G18/F18*(100),"")

The Excel newsgroups were always my first port of call. Not in the least
nerdy

microsoft.public.excel.worksheet.functions

I'm sure you'll get an elegant solution there ....if the group still exists

I used that a lot in the past but pretty dead these days.
But that's the advantage of newsgroups They can appear to be dead but
people can still lurk there with no overhead.
--
bert

On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.



If you change the formula to
=AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0")
it'll disregard the cells containing zero

NB the format is different to AVERAGEIF because the "average range" is
the first parameter, followed by pairs of "condition_range","condition"
parameters.

It looks like you may get 'divide by zero' errors, but you can use an
IFERROR condition to control that
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"")

On 06/05/2015 22:01, Jim Newman wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.



If you change the formula to
=AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0")
it'll disregard the cells containing zero

NB the format is different to AVERAGEIF because the "average range" is
the first parameter, followed by pairs of "condition_range","condition"
parameters.

It looks like you may get 'divide by zero' errors, but you can use an
IFERROR condition to control that
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"")

Thats it! spot on, problem solve.

Thank you all

On 06/05/2015 22:20, ss wrote:
On 06/05/2015 22:01, Jim Newman wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.



If you change the formula to
=AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0")
it'll disregard the cells containing zero

NB the format is different to AVERAGEIF because the "average range" is
the first parameter, followed by pairs of "condition_range","condition"
parameters.

It looks like you may get 'divide by zero' errors, but you can use an
IFERROR condition to control that
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"")

Thats it! spot on, problem solve.

Thank you all

That's what I love about spreadsheet queries. There's always a solution
that works and everybody moves on (unlike this place!)

On 06/05/2015 22:20, ss wrote:
On 06/05/2015 22:01, Jim Newman wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.



If you change the formula to
=AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0")
it'll disregard the cells containing zero

NB the format is different to AVERAGEIF because the "average range" is
the first parameter, followed by pairs of "condition_range","condition"
parameters.

It looks like you may get 'divide by zero' errors, but you can use an
IFERROR condition to control that
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"")

Thats it! spot on, problem solve.

Thank you all

Happy to help
The only suggestion I would make is that you amend it to 'fix' the check
against row 3, by using
=IFERROR(AVERAGEIFS(C18:AL18,$C$3:$AL$3,"%",C18:AL 18,"0"),"")
as then you could set it for the first row, and then drag down to
complete the rest of the column.