| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding rows to the data array used in an Index function | Excel Discussion (Misc queries) | |||
| Adding error suppression to AVERAGE FUNCTION | Excel Worksheet Functions | |||
| Small Array is too big for AVERAGE Function? | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Hybrid Mode
