Calculation of weighed average
in [Functions]
|
Prev: How do I filter cells based on character case in Excel
Next: Multiple variables to sort and sum, return values<0 with sum refer
From: jkrons on 9 Apr 2010 03:45 I have a number of cells in a row, D3:K3 (actually the row will contain columns up to FZ). These cells can either contain a number o4r be empty. An example could be D E F G H I J K 2 4 7 2 - 2 - 7 The "-" indicates an empty cell. Now what I need is a formula, that takes the sum of all the cells and multiply the sum by by 2 (the easy part :-)), and divides it with the number of cells, multiplied by two, if the cell is not empty and multiplied by 1 if the cell isempty. In the above example the multiplied by 2 sum is 48. And this should be divided by 14 as there are 6 values (mulitplied by two) and two empty cells. COUNT will count the cells with content Jan
From: jkrons on 9 Apr 2010 03:48 Sorry for the trouble. It just came to me :-( SUM(D3:K3)*2/(COUNT(D3:K3)*2+COUNTBLANK(D3:K3)) Jan
From: Rick Rothstein on 9 Apr 2010 03:52 Try this formula... =SUMPRODUCT((D1:K1)*(2-(D1:K1=""))) -- Rick (MVP - Excel) "jkrons" <jkr(a)knord.dk> wrote in message news:94ed1f03-50cb-43dd-8c74-6d3821ebb8ec(a)z7g2000yqb.googlegroups.com... > I have a number of cells in a row, D3:K3 (actually the row will > contain columns up to FZ). These cells can either contain a number o4r > be empty. An example could be > > D E F G H I J K > 2 4 7 2 - 2 - 7 > > The "-" indicates an empty cell. > > Now what I need is a formula, that takes the sum of all the cells and > multiply the sum by by 2 (the easy part :-)), and divides it with the > number of cells, multiplied by two, if the cell is not empty and > multiplied by 1 if the cell isempty. > > In the above example the multiplied by 2 sum is 48. And this should be > divided by 14 as there are 6 values (mulitplied by two) and two empty > cells. COUNT will count the cells with content > > Jan
From: Jarek Kujawa on 9 Apr 2010 03:54 =2*SUM($D$1:$K$1)/SUM(IF(ISBLANK($D$1:$K$1),1,2)) CTRL+SHIFT+ENTER this formula as this is an array-formula pls click YES if this helped On 9 Kwi, 09:45, jkrons <j...(a)knord.dk> wrote: > I have a number of cells in a row, D3:K3 (actually the row will > contain columns up to FZ). These cells can either contain a number o4r > be empty. An example could be > > D E F G H I J K > 2 4 7 2 - 2 - 7 > > The "-" indicates an empty cell. > > Now what I need is a formula, that takes the sum of all the cells and > multiply the sum by by 2 (the easy part :-)), and divides it with the > number of cells, multiplied by two, if the cell is not empty and > multiplied by 1 if the cell isempty. > > In the above example the multiplied by 2 sum is 48. And this should be > divided by 14 as there are 6 values (mulitplied by two) and two empty > cells. COUNT will count the cells with content > > Jan
From: Jarek Kujawa on 9 Apr 2010 04:01
cute I like that one ;-) On 9 Kwi, 09:52, "Rick Rothstein" <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: > Try this formula... > > =SUMPRODUCT((D1:K1)*(2-(D1:K1=""))) > > -- > Rick (MVP - Excel) > > "jkrons" <j...(a)knord.dk> wrote in message > > news:94ed1f03-50cb-43dd-8c74-6d3821ebb8ec(a)z7g2000yqb.googlegroups.com... > > > > > I have a number of cells in a row, D3:K3 (actually the row will > > contain columns up to FZ). These cells can either contain a number o4r > > be empty. An example could be > > > D E F G H I J K > > 2 4 7 2 - 2 - 7 > > > The "-" indicates an empty cell. > > > Now what I need is a formula, that takes the sum of all the cells and > > multiply the sum by by 2 (the easy part :-)), and divides it with the > > number of cells, multiplied by two, if the cell is not empty and > > multiplied by 1 if the cell isempty. > > > In the above example the multiplied by 2 sum is 48. And this should be > > divided by 14 as there are 6 values (mulitplied by two) and two empty > > cells. COUNT will count the cells with content > > > Jan- Ukryj cytowany tekst - > > - Poka¿ cytowany tekst - |