If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Badly need help with a function
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 |
#2
|
|||
|
|||
Badly need help with a function
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 |
#3
|
|||
|
|||
Badly need help with a function
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 |
#4
|
|||
|
|||
Badly need help with a function
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 |
#5
|
|||
|
|||
Badly need help with a function
=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 |
Thread Tools | |
Display Modes | |
|
|