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

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.

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.

On 06/05/2015 19:00, Bob Eager wrote:
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.



But he says "What thats doing for me is giving me the number of cells
that have a number in them". So, I assume he just wants to know the
number of cells? The average can be done with sumif and countif, of course.

It might help if the OP simply says what he wants to achieve?

It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.

On 06/05/2015 20:57, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.



yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")

On 06/05/2015 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.



yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")



Sorry, the other way around is what you are doing, so
=IF(O180,P18/O18,"")

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 Wed, 06 May 2015 20:57:22 +0100, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.

Then isn't it just AVERAGEIF(rangem "0") ??

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

"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 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.



yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")


Q18 =IFERROR(P18/O18*(100),"")

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

O

yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")



Sorry, the other way around is what you are doing, so
=IF(O180,P18/O18,"")

That still gives thr wrong answer. In the cell I require the answer I
have =AVERAGEIF(C3:AL3,"%",C18:AL18)

On 06/05/2015 22:00, ss wrote:
On 06/05/2015 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.



yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")


Q18 =IFERROR(P18/O18*(100),"")

If you change it to:
=IF(O180,P18/O18,"")

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 22:06, GB wrote:
On 06/05/2015 22:00, ss wrote:
On 06/05/2015 21:31, GB wrote:
On 06/05/2015 20:57, ss wrote:


It might help if the OP simply says what he wants to achieve?

I have 12 columns for months each month containing 3 columns
apps/sales/% In the rpws for those columns various products.
At the bottom od the % column is the % for that month.

So in all I have 36 columns If for example we are 3 months gone, then I
want to add the totals for each of those % columns and divide by 3.

This will give me an average %. In doing this I want cells that are
zero
to be shown as blank With the formula I have although some cells look
blank they do in fact contain a zero which the formula picks up on.

A few posts down I have a link to an image of the sheet.



yES, IT'S CLEAR NOW WHAT YOU WANT TO DO, AND WHAT'S GOING WRONG. (Ooops
caps.)

What's the formula in cell Q18?

Have you tried:
=IF(P180,O18/P18,"")


Q18 =IFERROR(P18/O18*(100),"")

If you change it to:
=IF(O180,P18/O18,"")


Maybe this will help (I cant really understand it)
Apparently my formula is correct but for, and I quote:

"The only way to get to 58.75 is if one of the % columns contains a 0.

Looks like you are using formulas to calculate the percentage and format
away zero values, but they are still in the cells. If you use formulas
to calculate the percentage column, return a blank instead of a zero to
have it ignored in the average.

Just thinking maybe its the cell I want the average in that he is
referring to?

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