Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
Hi
I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
Hi ub,
Try: =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) -- Cheers macropod [MVP - Microsoft Word] "ub" wrote in message ... Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
Hi
Which worksheet function should I apply this logic. Ex: Should I use IF function. Please advise "macropod" wrote: Hi ub, Try: =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) -- Cheers macropod [MVP - Microsoft Word] "ub" wrote in message ... Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
You don't need a worksheet function. Just use the formula you were given.
=(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) could alternatively be written as =(OR(A10,A80)*C1+OR(A40,A80)*C4+C6)/(OR(A10,A80)+OR(A40,A80)+1) -- David Biddulph "ub" wrote in message ... Hi Which worksheet function should I apply this logic. Ex: Should I use IF function. Please advise "macropod" wrote: Hi ub, Try: =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) -- Cheers macropod [MVP - Microsoft Word] "ub" wrote in message ... Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
Hi
Can you please tell me how this syntax works in Excell for my future refernce. Or Do you have any link that you can send me to to find more information about how this formula works "David Biddulph" wrote: You don't need a worksheet function. Just use the formula you were given. =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) could alternatively be written as =(OR(A10,A80)*C1+OR(A40,A80)*C4+C6)/(OR(A10,A80)+OR(A40,A80)+1) -- David Biddulph "ub" wrote in message ... Hi Which worksheet function should I apply this logic. Ex: Should I use IF function. Please advise "macropod" wrote: Hi ub, Try: =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) -- Cheers macropod [MVP - Microsoft Word] "ub" wrote in message ... Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate Average
The first formula doesn't have any Excel functions in it at all, so there's
nothing to tell, except that a Boolean TRUE evaluates as 1 when arithmetic is applied, and Boolan FALSE evaluates as zero. In the second option, the only Excel function used is OR, which does the same as ordinary Boolean arithemetic. OR(FALSE, FALSE) returns FALSE OR(FALSE, TRUE) returns TRUE OR(TRUE, FALSE) returns TRUE OR(TRUE, TRUE) returns TRUE If you are still struggling to see what the formula is doing, break it down to manageable chunks and see what those return. =(OR(A10,A80)*C1+OR(A40,A80)*C4+C6) will give the sum of C1, C4, C6 with your conditions applied. =(OR(A10,A80)+OR(A40,A80)+1) will give the number of entries which satisfy your conditions. Hence dividing one by the other gives the average. -- David Biddulph "ub" wrote in message ... Hi Can you please tell me how this syntax works in Excell for my future refernce. Or Do you have any link that you can send me to to find more information about how this formula works "David Biddulph" wrote: You don't need a worksheet function. Just use the formula you were given. =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) could alternatively be written as =(OR(A10,A80)*C1+OR(A40,A80)*C4+C6)/(OR(A10,A80)+OR(A40,A80)+1) -- David Biddulph "ub" wrote in message ... Hi Which worksheet function should I apply this logic. Ex: Should I use IF function. Please advise "macropod" wrote: Hi ub, Try: =(((A10)+(A80)0)*C1+((A40)+(A80)0)*C4+C6)/(((A10)+(A80)0)+((A40)+(A80)0)+1) -- Cheers macropod [MVP - Microsoft Word] "ub" wrote in message ... Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a10 or a80, similarly cell c4 should be counted in average if cell a40 or a80 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Calculate Average | Excel Discussion (Misc queries) | |||
How do I calculate an average if | Excel Discussion (Misc queries) | |||
CALCULATE WITH AVERAGE ???? | Excel Discussion (Misc queries) | |||
If/Then calculate the average | Excel Worksheet Functions | |||
I want to calculate the average of best 5 out of 7 numbers? | Excel Discussion (Misc queries) |