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" 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" > wrote: >> Try this formula... >> >> =SUMPRODUCT((D1:K1)*(2-(D1:K1=""))) >> >> -- >> Rick (MVP - Excel) >> >> "jkrons" 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" 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" 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" > > wrote: > >> Try this formula... > > >> =SUMPRODUCT((D1:K1)*(2-(D1:K1=""))) > > >> -- > >> Rick (MVP - Excel) > > >> "jkrons" 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 -