Badly need help with a function

[slfwalsh]

How do i create a function to give me the following mathematical formula:

(a+b+c+d+e+f)/N * 6/N

Where a-f are numbers between 0-9 inclusive and importantly N = 6 - (the
number of values a-f that are '0')

example

numbers = 1 3 5 7 0 8

Then N= 5

So answer is 24/5 *6/5 = 5.76

Thanks

Simon

 

[Tom-S]

Say your 6 numbers a to f are in cells A1 to F1.

N can be calculated as a formula, say in G1, =COUNTIF(A1:F1,"<>0")

Then your final answer can be calculated as a formula, say in H1,
=(SUM(A1:F1)/G1)*(6/G1)

Note that this expression can be simplified to:
=(SUM(A1:F1)*6)/G1

Also note, if N is zero then you will get a division by zero error, so you
might want to trap that by using a modified formula for your final expression:
IF(G1=0,"N = zero",(SUM(A1:F1)*6)/G1)

Regards,

Tom

 

[Tom-S]

Ooops - sorry - the simplified expression should be:
=(SUM(A1:F1)*6)/(G1^2)

And the error trapped one:
IF(G1=0,"N = zero",(SUM(A1:F1)*6)/(G1^2))

Tom

 

[FSt1]

hi
try...
=SUM(A1:A6)/COUNTIF(A1:A6,">0")*(6/COUNTIF(A1:A6,">0"))

regards
FSt1

 

[Don Guillett]

=SUM(I2:N2)/COUNTIF(I2:N2,">0")*6/COUNTIF(I2:N2,">0")