Calculation of weighed average
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 |
Calculation of weighed average
Sorry for the trouble. It just came to me :-(
SUM(D3:K3)*2/(COUNT(D3:K3)*2+COUNTBLANK(D3:K3)) Jan |
Calculation of weighed average
Try this formula...
=SUMPRODUCT((D1:K1)*(2-(D1:K1=""))) -- Rick (MVP - Excel) "jkrons" wrote in message ... 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 |
Calculation of weighed average
=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 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 |
Calculation of weighed average
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 ... 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 - |
Calculation of weighed average
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 ... 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 ... 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 - |
Calculation of weighed average
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 ... 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 .... 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 - |
All times are GMT +1. The time now is 01:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com