"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