Help - I've tried all kinds of things but no luck.
On May 21, 10:12*am, slfwalsh
wrote:
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!!
--
simon
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
|