How to calculate Average

[ub]

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 a1>0 or a8>0, similarly cell c4 should
be counted in average if cell a4>0 or a8>0

Thanks

 

[macropod]

Hi ub,

Try:
=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)

 

[ub]

Hi
Which worksheet function should I apply this logic.
Ex: Should I use IF function.

Please advise

Hi ub,

Try:
=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)

--
Cheers
macropod
[MVP - Microsoft Word]

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 a1>0 or a8>0, similarly cell c4 should
be counted in average if cell a4>0 or a8>0

Thanks

 

[David Biddulph]

You don't need a worksheet function. Just use the formula you were given.

=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)
could alternatively be written as
=(OR(A1>0,A8>0)*C1+OR(A4>0,A8>0)*C4+C6)/(OR(A1>0,A8>0)+OR(A4>0,A8>0)+1)
--
David Biddulph

Hi
Which worksheet function should I apply this logic.
Ex: Should I use IF function.

Please advise

Hi ub,

Try:
=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)

--
Cheers
macropod
[MVP - Microsoft Word]

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 a1>0 or a8>0, similarly cell c4
should
be counted in average if cell a4>0 or a8>0

Thanks

 

[ub]

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

You don't need a worksheet function. Just use the formula you were given.

=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)
could alternatively be written as
=(OR(A1>0,A8>0)*C1+OR(A4>0,A8>0)*C4+C6)/(OR(A1>0,A8>0)+OR(A4>0,A8>0)+1)
--
David Biddulph

Hi
Which worksheet function should I apply this logic.
Ex: Should I use IF function.

Please advise

Hi ub,

Try:
=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)

--
Cheers
macropod
[MVP - Microsoft Word]


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 a1>0 or a8>0, similarly cell c4
should
be counted in average if cell a4>0 or a8>0

Thanks

 

[David Biddulph]

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(A1>0,A8>0)*C1+OR(A4>0,A8>0)*C4+C6) will give the sum of C1, C4, C6 with
your conditions applied.
=(OR(A1>0,A8>0)+OR(A4>0,A8>0)+1) will give the number of entries which
satisfy your conditions.
Hence dividing one by the other gives the average.
--
David Biddulph

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

You don't need a worksheet function. Just use the formula you were
given.

=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)
could alternatively be written as
=(OR(A1>0,A8>0)*C1+OR(A4>0,A8>0)*C4+C6)/(OR(A1>0,A8>0)+OR(A4>0,A8>0)+1)
--
David Biddulph

Hi
Which worksheet function should I apply this logic.
Ex: Should I use IF function.

Please advise

:

Hi ub,

Try:
=(((A1>0)+(A8>0)>0)*C1+((A4>0)+(A8>0)>0)*C4+C6)/(((A1>0)+(A8>0)>0)+((A4>0)+(A8>0)>0)+1)

--
Cheers
macropod
[MVP - Microsoft Word]


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 a1>0 or a8>0, similarly cell c4
should
be counted in average if cell a4>0 or a8>0

Thanks