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

Sorry for the trouble. It just came to me :-(

SUM(D3:K3)*2/(COUNT(D3:K3)*2+COUNTBLANK(D3:K3))

Jan

Try this formula...

=SUMPRODUCT((D1:K1)*(2-(D1:K1="")))

--
Rick (MVP - Excel)



"jkrons" <(E-Mail Removed)> wrote in message
news:94ed1f03-50cb-43dd-8c74-(E-Mail Removed)...
> 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

=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...@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

cute

I like that one

;-)


On 9 Kwi, 09:52, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Try this formula...
>
> =SUMPRODUCT((D1:K1)*(2-(D1:K1="")))
>
> --
> Rick (MVP - Excel)
>
> "jkrons" <j...@knord.dk> wrote in message
>
> news:94ed1f03-50cb-43dd-8c74-(E-Mail Removed)...
>
>
>
> > 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 -

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" <(E-Mail Removed)> wrote in message
news:7aa56e8f-0452-48e8-872d-(E-Mail Removed)...
> cute
>
> I like that one
>
> ;-)
>
>
> On 9 Kwi, 09:52, "Rick Rothstein"
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> Try this formula...
>>
>> =SUMPRODUCT((D1:K1)*(2-(D1:K1="")))
>>
>> --
>> Rick (MVP - Excel)
>>
>> "jkrons" <j...@knord.dk> wrote in message
>>
>> news:94ed1f03-50cb-43dd-8c74-(E-Mail Removed)...
>>
>>
>>
>> > 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 -
>

I definitely don't, Rick

;-)

On 9 Kwi, 10:19, "Rick Rothstein"
<rick.newsNO.S...@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...@gazeta.pl> wrote in message
>
> news:7aa56e8f-0452-48e8-872d-(E-Mail Removed)...
>
>
>
> > cute
>
> > I like that one
>
> > ;-)
>
> > On 9 Kwi, 09:52, "Rick Rothstein"
> > <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> >> Try this formula...
>
> >> =SUMPRODUCT((D1:K1)*(2-(D1:K1="")))
>
> >> --
> >> Rick (MVP - Excel)
>
> >> "jkrons" <j...@knord.dk> wrote in message
>
> >>news:94ed1f03-50cb-43dd-8c74-(E-Mail Removed)....
>
> >> > 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 -