Not having luck with any examples of averaging...
 

Hi guys and gals. I've just spent 15 mins writing my message only to 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 need 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 remotly like my problem, I can't find anything that works including my formula 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 where I'm coming from: I used the CountIf becasue I'm trying to count every 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 the "0" part. And then finally I'm under the impression that I need to divide it all by 6 as there are 6 cells, even though nothing is to say that 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 of 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.

Not having luck with any examples of averaging...
 

=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

On Feb 13, 7:11*pm, JoeQwerty
wrote:
Hi guys and gals. *I've just spent 15 mins writing my message only to 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 need 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 remotly like
my problem, I can't find anything that works including my formula 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 where I'm
coming from: I used the CountIf becasue I'm trying to count every 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 the "0"
part. *And then finally I'm under the impression that I need to divide
it all by 6 as there are 6 cells, even though nothing is to say that 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 of 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.

--
JoeQwerty

Hi Don, thanks for your message. I have to admit I have no idea what your 3 formula's mean. I'm not that advanced. So I have just chosen to try each of your formula's and none work, so here's just an example of 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.