Adding or to previously provided Average If Array function

[Diddy]

Hi,

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

=IF(COUNTIF(Q3:Q4,">4"),MAX(3,AVERAGE(IF(score>0,score))),
AVERAGE(IF(score>0,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

 

[Joe User]

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(score>0,score))),
AVERAGE(IF(score>0,score)))

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

=IF(OR(Q3>4, Q4>4, Q5>3, Q6>3),
MAX(3,AVERAGE(IF(score>0,score))),
AVERAGE(IF(score>0,score)))

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

MAX(3*OR(Q3>4, Q4>4, Q5>3, Q6>3), AVERAGE(IF(score>0,score)))

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


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

 

[T. Valko]

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")),MAX(3,AVERAGE(IF(score>0,score))),
AVERAGE(IF(score>0,score)))

 

[T. Valko]

=IF(OR(Q3>4, Q4>4, Q5>3,

Q6>3),MAX(3,AVERAGE(IF(score>0,score))),AVERAGE(IF(score>0,score)))

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

=IF(OR(Q3:Q4>4,Q5:Q6>3),MAX(3,AVERAGE(IF(score>0,score))),AVERAGE(IF(score>0,score)))

 

[Harlan Grove]

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

=IF(OR(Q3:Q4>4,Q5:Q6>3),MAX(3,AVERAGE(IF(score>0,score))),AVERAGE(IF(score>0,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:Q4>4,Q5:Q6>3),3,-1E307),AVERAGE(IF
(score>0,score))),"")