Help - I've tried all kinds of things but no luck.

[slfwalsh]

Howdy,

Thanks for taking the time to read my post. I'm having problems creating a
custom function in VBA (I'm not an experienced user)

Basically i have a huge data sheet with just 6 columns but 60,000 rows. Each
of the 6 cells in a row have values (0-10). I need the average of the value
in each row, however if one (or more) of the cells contains 0, i need to do a
weighted average - in other words if the figures are 1,4,6,0,4,5 then instead
of adding and dividing by 6 i'd add and divide by 5 and then i need excel to
multiply by 6/5 - to weight it for a '6' cell average.

Another example if its 3,4,0,0,5,6

then need it to sum them and divide by 4 then multiply by 6/4

In maths terms its

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

Any ideas - you'd helping in some medical research!!

 

[Jason]

Howdy,

Thanks for taking the time to read my post. I'm having problems creating a
custom function in VBA (I'm not an experienced user)

Basically i have a huge data sheet with just 6 columns but 60,000 rows. Each
of the 6 cells in a row have values (0-10). I need the average of the value
in each row, however if one (or more) of the cells contains 0, i need to do a
weighted average - in other words if the figures are 1,4,6,0,4,5 then instead
of adding and dividing by 6 i'd add and divide by 5 and then  i need excel to
multiply by 6/5 - to weight it for a '6' cell average.

Another example  if its 3,4,0,0,5,6

then need it to sum them and divide by 4 then multiply by 6/4

In maths terms its

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

Any ideas - you'd helping in some medical research!!

The following formula should accomplish what you're wanting to do:

=(SUM(A1:F1)/COUNTIF(A1:F1,">0"))*(6/COUNTIF(A1:F1,">0"))

Hope this helps.
Jason