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: Rick Rothstein on 9 Apr 2010 04:19 I'm glad you liked it. We can actually shorten it by two character by removing the one unnecessary set of parentheses... =SUMPRODUCT(D1:K1*(2-(D1:K1=""))) And, if you don't mind array-entered** formulas, we can save another 7 characters using this array-entered** formula... =SUM(D1:K1*(2-(D1:K1=""))) **commit formula using Ctrl+Shift+Enter and not Enter by itself -- Rick (MVP - Excel) "Jarek Kujawa" <blinok(a)gazeta.pl> wrote in message news:7aa56e8f-0452-48e8-872d-1aa8b049f5d2(a)x3g2000yqd.googlegroups.com... > 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 - >
From: Jarek Kujawa on 9 Apr 2010 04:25
I definitely don't, Rick ;-) On 9 Kwi, 10:19, "Rick Rothstein" <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: > I'm glad you liked it. We can actually shorten it by two character by > removing the one unnecessary set of parentheses... > > =SUMPRODUCT(D1:K1*(2-(D1:K1=""))) > > And, if you don't mind array-entered** formulas, we can save another 7 > characters using this array-entered** formula... > > =SUM(D1:K1*(2-(D1:K1=""))) > > **commit formula using Ctrl+Shift+Enter and not Enter by itself > > -- > Rick (MVP - Excel) > > "Jarek Kujawa" <bli...(a)gazeta.pl> wrote in message > > news:7aa56e8f-0452-48e8-872d-1aa8b049f5d2(a)x3g2000yqd.googlegroups.com... > > > > > 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 -- Ukryj cytowany tekst - > > - Pokaż cytowany tekst - |