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
|
|||
|
|||
Help - I've tried all kinds of things but no luck.
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|