Prev: Insert an invisible calender only when you click cell in excel
Next: HOW DO YOU FORMAT A DATE? I KEEP PUTTING ONE IN AND IT CHANGES
From: slfwalsh on 21 May 2010 11:12
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
From: Jason on 21 May 2010 14:09
On May 21, 10:12 am, slfwalsh <slfwa...(a)discussions.microsoft.com>
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
 | 
Pages: 1
Prev: Insert an invisible calender only when you click cell in excel
Next: HOW DO YOU FORMAT A DATE? I KEEP PUTTING ONE IN AND IT CHANGES