Not having luck with any examples of averaging...

[JoeQwerty]

Hi guys and gals. I've just spent 15 mins writing my message only to b
taken to the login page and it got wiped!!! It was so beautifull
explained!!! I'll try again:

So, I have 6 cells non-adjacent and they're all in percent. I need th
average of these 6 cells to be entered into cell A8 as an averag
percentage of all the percentages.

Apart from trying every formula in lots of forums that are remotly lik
my problem, I can't find anything that works including my formula whic
also is crook and thus follows:

=countif(I34+AC34+AW34+BQ34+CK34+DE34,">0")/6

Now, cell I34 has 85% in it, cell AC34 has 69% in it, but the other
cells have 0 in them and I don't want to include 0's. Why? Becaus
it's then making an average out of the 6 cells but only 2 cells hav
percentages in them so therefore the formula's not flexible to th
number of cells which have something in them. And averaging is abou
adding up the numbers and then dividing by the number of numbers
Right.

Let me talk you through my rubbish formula attempt so you know where I'
coming from: I used the CountIf becasue I'm trying to count every cel
which doesn't have a 0 in it. I'm adding the cells with a + sig
because these are non-adjacent cells so I can't use the : sign. I'
then trying to only include any percentage bigger than 0, hence the ">0
part. And then finally I'm under the impression that I need to divid
it all by 6 as there are 6 cells, even though nothing is to say that an
or all of the 6 cells must have percentages in them or not. Sometime
there could be only 1 cell with a percentage in and the rest 0's, o
purhaps 3 cells with percentages in and 3 with 0's, or purhaps all
cells may be filled with percentages. So there must be a way of tellin
Excel to only include the cells with percentages.

So far, if cell I34 has 85% and cell AC34 has 69% in it, then A8 cel
should be reading: (85%+69%)/2=77% but of course my formula's no
working so it doesn't know that its got to only divide by 2 as only
cells are full and not 6 as the other 4 cells have 0 in them.

Please help. I have already pulled out all of my dogs hair and I'v
almost pulled out all of my own hair with the frustration. Soon I'l
have to visit the wig hire shop, (yes, my Jack Russell has alread
visited the wig and costume shop and looks like a 1 foot tall Blue'
Brother, good job he doesn't give a ****)!

Joe... UK

 

[Don Guillett]

=SUMPRODUCT(sum(INDIRECT({"C2","C5","C9"}),">0"))/3
or
=SUMPRODUCT(sum(INDIRECT({"C2","C5","C9"}),">0"))/
=SUMPRODUCT(COUNTIF(INDIRECT({"C2","C5","C9"}),">0"))

depending on what you want

 

[JoeQwerty]

Hi Don, thanks for your message. I have to admit I have no idea wha
your 3 formula's mean. I'm not that advanced. So I have just chosen t
try each of your formula's and none work, so here's just an example o
the last formula you gave, but with my cells in:

=SUMPRODUCT(COUNTIF(INDIRECT({"i34","ac34","aw34","bq34","ck34","de34"}),">0"))

But it gives the answer: 200% which is obviously not right...

So I still don't understand at all? Sorry. Joe.

;1386704']=SUMPRODUCT(sum(INDIRECT({"C2","C5","C9"}),">0"))/3
or
=SUMPRODUCT(sum(INDIRECT({"C2","C5","C9"}),">0"))/
=SUMPRODUCT(COUNTIF(INDIRECT({"C2","C5","C9"}),">0"))

depending on what you want

Hi guys and gals. *I've just spent 15 mins writing my message only t be
taken to the login page and it got wiped!!! It was so beautifully
explained!!! I'll try again:

So, I have 6 cells non-adjacent and they're all in percent. *I nee the
average of these 6 cells to be entered into cell A8 as an average
percentage of all the percentages.

Apart from trying every formula in lots of forums that are remotl like
my problem, I can't find anything that works including my formul which
also is crook and thus follows:

=countif(I34+AC34+AW34+BQ34+CK34+DE34,">0")/6

Now, cell I34 has 85% in it, cell AC34 has 69% in it, but the other 4
cells have 0 in them and I don't want to include 0's. *Why? *Because
it's then making an average out of the 6 cells but only 2 cells have
percentages in them so therefore the formula's not flexible to the
number of cells which have something in them. *And averaging is about
adding up the numbers and then dividing by the number of numbers.
Right.

Let me talk you through my rubbish formula attempt so you know wher I'm
coming from: I used the CountIf becasue I'm trying to count ever cell
which doesn't have a 0 in it. *I'm adding the cells with a + sign
because these are non-adjacent cells so I can't use the : sign. *I'm
then trying to only include any percentage bigger than 0, hence th ">0"
part. *And then finally I'm under the impression that I need t divide
it all by 6 as there are 6 cells, even though nothing is to say tha any
or all of the 6 cells must have percentages in them or not *Sometimes
there could be only 1 cell with a percentage in and the rest 0's, or
purhaps 3 cells with percentages in and 3 with 0's, or purhaps all 6
cells may be filled with percentages. *So there must be a way o telling
Excel to only include the cells with percentages.

So far, if cell I34 has 85% and cell AC34 has 69% in it, then A8 cell
should be reading: (85%+69%)/2=77% but of course my formula's not
working so it doesn't know that its got to only divide by 2 as only 2
cells are full and not 6 as the other 4 cells have 0 in them.

Please help. *I have already pulled out all of my dogs hair and I've
almost pulled out all of my own hair with the frustration. *Soon I'll
have to visit the wig hire shop, (yes, my Jack Russell has already
visited the wig and costume shop and looks like a 1 foot tall Blue's
Brother, good job he doesn't give a ****)!

Joe... UK.