Adding if to Average If Array function
 

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the explanation.

I've now been asked to add to this formula so that if either Q3 or Q4 is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and I'm
thinking that the If part is non array but the average part still needs to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy

Adding if to Average If Array function
 

Try

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score)))

--
Jacob


"Diddy" wrote:

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the explanation.

I've now been asked to add to this formula so that if either Q3 or Q4 is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and I'm
thinking that the If part is non array but the average part still needs to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy

Adding if to Average If Array function
 

Hi Jacob,

I'm probably doing something wrong but I get #VALUE!. Am I supposed to enter
as an array formula?

Cheers
Diddy

"Jacob Skaria" wrote:

Try

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score)))

--
Jacob


"Diddy" wrote:

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the explanation.

I've now been asked to add to this formula so that if either Q3 or Q4 is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and I'm
thinking that the If part is non array but the average part still needs to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy

Adding if to Average If Array function
 

Yes.
--
David Biddulph

"Diddy" wrote in message
...
Hi Jacob,

I'm probably doing something wrong but I get #VALUE!. Am I supposed to
enter
as an array formula?

Cheers
Diddy

"Jacob Skaria" wrote:

Try

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score)))

--
Jacob


"Diddy" wrote:

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the
explanation.

I've now been asked to add to this formula so that if either Q3 or Q4
is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and
I'm
thinking that the If part is non array but the average part still needs
to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy

Adding if to Average If Array function
 

Hi Jacob,

How dumb am I ? Thank you!

"Jacob Skaria" wrote:

Try

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score)))

--
Jacob


"Diddy" wrote:

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the explanation.

I've now been asked to add to this formula so that if either Q3 or Q4 is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and I'm
thinking that the If part is non array but the average part still needs to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy

Adding if to Average If Array function
 

Thank you once again David.

Cheers
Diddy

"David Biddulph" wrote:

Yes.
--
David Biddulph

"Diddy" wrote in message
...
Hi Jacob,

I'm probably doing something wrong but I get #VALUE!. Am I supposed to
enter
as an array formula?

Cheers
Diddy

"Jacob Skaria" wrote:

Try

=IF(COUNTIF(Q3:Q4,"4"),MAX(3,AVERAGE(IF(score0,s core))),
AVERAGE(IF(score0,score)))

--
Jacob


"Diddy" wrote:

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Thanks to Luke M, Pete UK and David Biddulph who provided the
explanation.

I've now been asked to add to this formula so that if either Q3 or Q4
is
greater than 4 then the average cannot be lower than 3.
Q1:Q35 is the named range score.

This is really complicated because I still need to have an average and
I'm
thinking that the If part is non array but the average part still needs
to be
an array.

Can you mix and match or is it not possible?

Many thanks
Diddy


.