Average formula combined with IF formula

[cpisthedbb]

Hi,

What formula would I use for the following. I have 4 or 5 percentages which I need to average. If any of these 4 or 5 values are 0% then the average would need to be zero.

For example if I have 100% 0% 75% 100% - I need the average top show as 0% and not 68.75%

Can anyone please help, I can't find the answer anywhere.

 

[Joe4]

If you four values are in cells A1:D1, try this formula:

=IF(COUNTIF(A1:D1,0)>0,0,AVERAGE(A1:D1))

 

[Fluff]

How about
=IF(COUNTIF(I4:I7,0%)>0,0,AVERAGE(I4:I7))

 

[cpisthedbb]

How about
=IF(COUNTIF(I4:I7,0%)>0,0,AVERAGE(I4:I7))

Thank for the reply. For some reason when I put this in I get the 'there's a problem with this formula'. The values are in a dropdown box, would this make a difference?

 

[cpisthedbb]

Thank for the reply. For some reason when I put this in I get the 'there's a problem with this formula'. The values are in a dropdown box, would this make a difference?

Just tried it with the dropdown boxes removed and get the same error.

 

[Fluff]

You may need to replace the commas with whatever your regional separator is.