Calculation of weighed average

[jkrons]

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

 

[jkrons]

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

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

Jan

 

[Rick Rothstein]

Try this formula...

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

 

[Jarek Kujawa]

=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

 

[Jarek Kujawa]

cute

I like that one

;-)

Try this formula...

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

--
Rick (MVP - Excel)











- Poka¿ cytowany tekst -

 

[Rick Rothstein]

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

 

[Jarek Kujawa]

I definitely don't, Rick

;-)

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)







- Pokaż cytowany tekst -