| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))),"") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding if to Average If Array function | Excel Worksheet Functions | |||
| 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 | |||
| Can a cell refer to range name/array of data previously set? | Excel Discussion (Misc queries) | |||
Hybrid Mode
