Adding or to previously provided Average If Array function
 

Hi,

Jacob Skaria helped me out by providing the following formula. Thank you Jacob

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score))) entered as an array.
but I'm here again!

I need to add an OR (I think!) 'cos now I've been asked to amend this so
that a score of 4 in either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.
Any of these cells can contain a zero.

Q1:Q35 is the named range score.

If anyone can help me that would be more than brilliant!
Cheers
Diddy

Adding or to previously provided Average If Array function
 

"Diddy" wrote:
I've been asked to amend this so that a score of 4 in
either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.

Following Jacob's paradigm, you could write the following array formula:

=IF(OR(COUNTIF(Q3:Q4,"4"), COUNTIF(Q5:Q6,"3")),
MAX(3,AVERAGE(IF(score0,score))),
AVERAGE(IF(score0,score)))

But perhaps the following straight-forward array formula would seem less
mysterious:

=IF(OR(Q34, Q44, Q53, Q63),
MAX(3,AVERAGE(IF(score0,score))),
AVERAGE(IF(score0,score)))

If the average will always be zero or more, you might consider the following
array formula:

MAX(3*OR(Q34, Q44, Q53, Q63), AVERAGE(IF(score0,score)))

Finally, note that your original formula fails to account for the
possibility that all scores are zero. Is that a problem?


----- original message -----

"Diddy" wrote in message
...
Hi,

Jacob Skaria helped me out by providing the following formula. Thank you
Jacob

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score))) entered as an array.
but I'm here again!

I need to add an OR (I think!) 'cos now I've been asked to amend this so
that a score of 4 in either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.
Any of these cells can contain a zero.

Q1:Q35 is the named range score.

If anyone can help me that would be more than brilliant!
Cheers
Diddy

Adding or to previously provided Average If Array function
 

Not really sure I understand but see if this does what you want.

Array entered:

=IF(OR(COUNTIF(Q3:Q4,"4"),COUNTIF(Q5:Q6,"3")),MA X(3,AVERAGE(IF(score0,score))),
AVERAGE(IF(score0,score)))

--
Biff
Microsoft Excel MVP


"Diddy" wrote in message
...
Hi,

Jacob Skaria helped me out by providing the following formula. Thank you
Jacob

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score))) entered as an array.
but I'm here again!

I need to add an OR (I think!) 'cos now I've been asked to amend this so
that a score of 4 in either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.
Any of these cells can contain a zero.

Q1:Q35 is the named range score.

If anyone can help me that would be more than brilliant!
Cheers
Diddy

Adding or to previously provided Average If Array function
 

=IF(OR(Q34, Q44, Q53,
Q63),MAX(3,AVERAGE(IF(score0,score))),AVERAGE(I F(score0,score)))

I didn't even think of that. Since it's already an array formula we could
also do this:

=IF(OR(Q3:Q44,Q5:Q63),MAX(3,AVERAGE(IF(score0,s core))),AVERAGE(IF(score0,score)))

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message
...
"Diddy" wrote:
I've been asked to amend this so that a score of 4 in
either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.

Following Jacob's paradigm, you could write the following array formula:

=IF(OR(COUNTIF(Q3:Q4,"4"), COUNTIF(Q5:Q6,"3")),
MAX(3,AVERAGE(IF(score0,score))),
AVERAGE(IF(score0,score)))

But perhaps the following straight-forward array formula would seem less
mysterious:

=IF(OR(Q34, Q44, Q53, Q63),
MAX(3,AVERAGE(IF(score0,score))),
AVERAGE(IF(score0,score)))

If the average will always be zero or more, you might consider the
following array formula:

MAX(3*OR(Q34, Q44, Q53, Q63), AVERAGE(IF(score0,score)))

Finally, note that your original formula fails to account for the
possibility that all scores are zero. Is that a problem?


----- original message -----

"Diddy" wrote in message
...
Hi,

Jacob Skaria helped me out by providing the following formula. Thank you
Jacob

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score))) entered as an array.
but I'm here again!

I need to add an OR (I think!) 'cos now I've been asked to amend this so
that a score of 4 in either Q3 or Q4 OR a score of 3 in either Q5 or Q6
means that the average cannot be lower than 3.
Any of these cells can contain a zero.

Q1:Q35 is the named range score.

If anyone can help me that would be more than brilliant!
Cheers
Diddy

Adding or to previously provided Average If Array function
 

"T. Valko" wrote...
=IF(OR(Q34, Q44, Q53,Q63),
MAX(3,AVERAGE(IF(score0,score))),AVERAGE(IF(sco re0,score)))

I didn't even think of that. Since it's already an array formula we could
also do this:

=IF(OR(Q3:Q44,Q5:Q63),MAX(3,AVERAGE(IF(score0, score))),AVERAGE(IF(score0,score)))
....

Somewhat on a tangent, should this return a number when there are no
numbers at all in the range named score? If not, then maybe the
following array formula.

=IF(COUNT(score),MAX(IF(OR(Q3:Q44,Q5:Q63),3,-1E307),AVERAGE(IF
(score0,score))),"")