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
--
simon

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


"slfwalsh" wrote:

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
--
simon

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


"Tom-S" wrote:

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


"slfwalsh" wrote:

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
--
simon

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

regards
FSt1

"slfwalsh" wrote:

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
--
simon

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"slfwalsh" wrote in message
...
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
--
simon