Help with excel formula

[ss]

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

[JimK]

[ss]

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.

[RJH]

You can do a countblank (or 'if

[GB]

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

[John Rumm]

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.

[ss]

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

[Bob Eager]

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

[stuart noble]

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

[ss]

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

[GB]

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?

[ss]

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.

[GB]

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(P18>0,O18/P18,"")

[GB]

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

[bert]

But that's the advantage of newsgroups They can appear to be dead but people can still lurk there with no overhead.

[Bob Eager]

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

[JimK]

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

Jim K

[Jacko]

[ss]

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

[Jim Newman]

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