=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm really
confused!!

One way:

=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUM((A30),(A70),(A110)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Richard" wrote in message
...
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the
corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if
there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm
really
confused!!

One way:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUMPRODUCT((A30)+(A70)+(A110)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Richard" wrote:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm really
confused!!

Thanks so much!! Is there a way to not show the #DIV/0! if the results are 0,
I thought using the =0,"", would prevent that but it still shows. Thanks so
much for your help!!!

"Sandy Mann" wrote:

One way:

=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUM((A30),(A70),(A110)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Richard" wrote in message
...
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the
corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if
there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm
really
confused!!

Thanks so much!! Is there a way to not show the #DIV/0! if the results are 0,
I thought using the =0,"", would prevent that but it still shows. Thanks so
much for your help!!!



"Max" wrote:

One way:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUMPRODUCT((A30)+(A70)+(A110)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Richard" wrote:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm really
confused!!

Try this:

=IF(AND(A3=0,A7=0,A11=0),"",SUM(A3,A7,A11)/SUMPRODUCT((MOD(ROW(A3:A11)-1,2)=0)*(A3:A110)))

Will *not* calculate accurately with negatives.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Richard" wrote in message
...
Thanks so much!! Is there a way to not show the #DIV/0! if the results are
0,
I thought using the =0,"", would prevent that but it still shows. Thanks
so
much for your help!!!

"Sandy Mann" wrote:

One way:

=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUM((A30),(A70),(A110)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Richard" wrote in message
...
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using
this
formula. It works fine as long as there are 3 values in the
corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the
corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if
there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm
really
confused!!

Richard,

I found an answer to this on these boards a little while ago, but I don't
know the original author.

The solution I've used is to use conditional formatting and make the text
white. The message is still there, but you can't see it:
- Write down/remember the top left cell of your range; for my example B3.
- highlight the range where the results are going to appear, starting at B3
- go to Format then Contitional Formatting
- Change the first drop down box from 'Cell is' to 'Formula is'
- In the second box enter =ISERROR(B3). (note that excel will try to use
$B$3 - you need to remove the dollar signs so that the formatting applies to
every cell in your range).
- Finally, use the format button to make the text white.

If you also need to print the spreadsheet without the values then follow the
online help for 'Replace Error Values When Printing'. I've copied it he
- Select the worksheet with the error you don't want to print.
- On the File menu, click Page Setup, and then click the Sheet tab.
- Under Print in the Cell errors as box, click <blank, -- , or #N/A.

Not sure if there is a more sophisticate way, but this works for me so far.
M-A

"Richard" wrote:

Thanks so much!! Is there a way to not show the #DIV/0! if the results are 0,
I thought using the =0,"", would prevent that but it still shows. Thanks so
much for your help!!!



"Max" wrote:

One way:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/SUMPRODUCT((A30)+(A70)+(A110)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Richard" wrote:
=IF(COUNT(A3,A7,A11)=0,"",SUM(A3,A7,A11)/COUNT(A3,A7,A11))
I'm having a problem with trying to get the correct percentage using this
formula. It works fine as long as there are 3 values in the corresponding
cells but sometimes there is a "0" value in 1 or 2 or 3 of the corresponding
cells. I need it to divide by 3 if there are 3 values, divide by 2 if there
are 2 values, and divide by 1 if only 1 value. Thanks in advance, I'm really
confused!!