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

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

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

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

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

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