Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average IF with calculation | Excel Worksheet Functions | |||
Weighed Average | Excel Worksheet Functions | |||
Average Calculation | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
Weighed average if the adjecent column contains blanks | Excel Discussion (Misc queries) |