Badly need help with a function

S

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
 
T

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top