Adding if to Average If Array function

Hi,

I recently had help understanding the following formula
{=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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
0
Utf
12/10/2009 1:23:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
725 Views

Similar Articles

[PageSpeed] 29

Try 

=IF(COUNTIF(Q3:Q4,">4"),MAX(3,AVERAGE(IF(score>0,score))),
AVERAGE(IF(score>0,score)))

-- 
Jacob


"Diddy" wrote:

> Hi,
> 
> I recently had help understanding the following formula
> {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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
0
Utf
12/10/2009 1:34:07 PM
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(score>0,score))),
> AVERAGE(IF(score>0,score)))
> 
> -- 
> Jacob
> 
> 
> "Diddy" wrote:
> 
> > Hi,
> > 
> > I recently had help understanding the following formula
> > {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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
0
Utf
12/10/2009 2:14:01 PM
Yes.
--
David Biddulph

"Diddy" <Diddy@discussions.microsoft.com> wrote in message 
news:70E05BAB-E0FA-41B3-97CC-B3F61FDFA1B1@microsoft.com...
> 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(score>0,score))),
>> AVERAGE(IF(score>0,score)))
>>
>> -- 
>> Jacob
>>
>>
>> "Diddy" wrote:
>>
>> > Hi,
>> >
>> > I recently had help understanding the following formula
>> > {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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 


0
David
12/10/2009 2:36:23 PM
Hi Jacob,

How dumb am I ? Thank you!

"Jacob Skaria" wrote:

> Try 
> 
> =IF(COUNTIF(Q3:Q4,">4"),MAX(3,AVERAGE(IF(score>0,score))),
> AVERAGE(IF(score>0,score)))
> 
> -- 
> Jacob
> 
> 
> "Diddy" wrote:
> 
> > Hi,
> > 
> > I recently had help understanding the following formula
> > {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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
0
Utf
12/10/2009 3:44:01 PM
Thank you once again David.

Cheers
Diddy

"David Biddulph" wrote:

> Yes.
> --
> David Biddulph
> 
> "Diddy" <Diddy@discussions.microsoft.com> wrote in message 
> news:70E05BAB-E0FA-41B3-97CC-B3F61FDFA1B1@microsoft.com...
> > 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(score>0,score))),
> >> AVERAGE(IF(score>0,score)))
> >>
> >> -- 
> >> Jacob
> >>
> >>
> >> "Diddy" wrote:
> >>
> >> > Hi,
> >> >
> >> > I recently had help understanding the following formula
> >> > {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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 
> 
> 
> .
> 
0
Utf
12/10/2009 3:45:02 PM
Reply:

Similar Artilces:

Adding if to Average If Array function
Hi, I recently had help understanding the following formula {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,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 ...

Searching for Image and adding path
I am working on a database that has several records. Each record has a different image associated with it (or will). I will not be the only person using this database, so I want to make it very simple to add images to records. I know that saving the image in the database isn't the best idea for Access 2003, so instead I want to have the path stored (which will make mail merging work too in Word). This is what I want: In the form, on the tab where the image information is, I want 3 things... - The image itself displayed, once the user adds it - The PATH of the image in a text b...

Averaging Selected Records
Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

I have added FrontPage 2003 and now I can't update my Pub2003 site
Ever since I installed FP 2003, I have not been able to update my Publisher site. Does FP cause a known conflict? -- Dedicated Virtual Assistant at http://www.world-wide-virtual.us Hi Damzish (msgroupsreply@world-wide-virtual.us), in the newsgroups you posted: || Ever since I installed FP 2003, I have not been able to update my || Publisher site. || || Does FP cause a known conflict? There are no known issues. What appears to be the problem? What is happening? Are you getting any error messages? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ T...

I am looking for a function for linear interpolation
I am looking for a function for linear interpolation Look in HELP for the LINEST() function -- Kind regards, Niek Otten Microsoft MVP - Excel "azad" <azad@discussions.microsoft.com> wrote in message news:C53A752C-DF76-4B9F-9145-6F39495088B7@microsoft.com... >I am looking for a function for linear interpolation ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

Sum function
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to put together a spreadsheet for a business plan and am having problems with the autsum and autofill functions. Firstly, even though I do a simple autosum, if I change a value in a cell, the sum does not change. Secondly, if I try to autofill across columns, the values of those columns simply mirror the value of the first. In other words it copies it. I am at a loss how to get round it as I have been working w Excel for years and have never had this problem. I have experimented with a new worksheet with ...

Need help with correct function or input for this problem/scenario
I have a situation where I am creating a borrowing base for a company. (I am going to shorten numbers to make it easier.) There is a $300 loan. Type A loan can borrow up to $300. Type B loan can borrow up to $150. Type C loan can borrow up to $50. I need a formula to input in excel that will give me a borrowing base for type B and C loans. For example, to borrow in type B loans, I have to check 1-what's outstanding on the entire loan. 2-what's outstanding on type B loans. 3-Take those two numbers and figure out what can be advanced under the answer to those questions. (I.E. $2...

sum function usin an IF statement
Bit short on detail, but maybe =IF(rng>10,rng) as an array formula, committed with Ctrl-Shift-Enter or =IF(rng1>some_val,rng2) again an array formula, where rng1 and rng2 are the same size. -- HTH RP (remove nothere from the email address if mailing direct) "jimk" <jimk@discussions.microsoft.com> wrote in message news:2873BFCF-598E-4956-808C-31C1638741A9@microsoft.com... > ...

Using Filter string with IF function
Is there a way to use a filter string with the IF function? I have a spreadsheet that looks like this: Invoice No. Invoice Date Invoice Currency Invoice Value 1 dd/mm/yyyy USD 1234.56 65 dd/mm/yyyy USD 2345.67 74 dd/mm/yyyy EUR 3456.78 88 dd/mm/yyyy USD 4567.89 when I filter on the currency: is there a function such as if(filter_string="USD";TRUE;FALSE)??? Thanks Aref Maybe this: Assuming row 1 are t...

adding numbers in an excell chart automatically
How to create an ongoing addition of numbers in an excell chart. Or sum of Numbers I should Say. I need the info for work. I think there is an easier way than how I am doing it. Worksheets are the things to use for adding numbers. That's not what a chart is designed to do. -- David Biddulph "sanicay" <sanicay@discussions.microsoft.com> wrote in message news:E94E1035-DC2C-4698-AC32-3439BDB2BDA6@microsoft.com... > How to create an ongoing addition of numbers in an excell chart. Or sum of > Numbers I should Say. I need the info for work. I think there is an >...

Using client PC to find the functnl level of my log-in AD domain
Using Windows XP service pack3 to login to AD domain. Running the GPresult from the command prompt returns the following: RSOP results for TMS\TABATAJ on PD9403355 : Logging Mode --------------------------------------------------------- OS Type: Microsoft Windows XP Professional OS Configuration: Member Workstation OS Version: 5.1.2600 Domain Name: ABC Domain Type: Windows 2000 Site Name: DATA-HUB-SITE Roaming Profile: Local Profile: C:\Documents and Settings\tabataj Connected over a slow link?: No -------------------- Can I conclude that the Functional level ...

Another Issue With A Function
Ron Rothstein suggested and I used this function in my Excel 2003 Workbook: Public Function QBRushYds(W, Q) QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10 End Function In Worksheet Totals Cell B56 thru B59 I have the following four calculations: =TRUNC(QBRushYds(1,1)) =TRUNC(QBRushYds(1,2)) =TRUNC(QBRushYds(1,3)) =TRUNC(QBRushYds(1,4)) In Worksheet Week1 Cells C3 thru C6 I have the following values: 725 814 283 9 The function correctly calculates the values: 72 81 28 0 If I change any of the values in C3 th...

And function into If function
I´m trying to get this function to work. But I´m getting an error message. =If(and(100/I6)*I7>101); I7<I6; 1; 0) What the function is supposed to do is to detect peak values in chart table that are measure errors from a measuring intrument and count every peak as 1. This is how the function is ment to operate: If the percent of the difference of the rise from the former cell to the next cell in the sequence of data is larger than 101% AND the value of this cell is smaller than the former cell then true=1 False=0 Im using a norwegian version of excel and I´m no...

add individual data points to a bar chart of averages
I have groups of data points in categories. I can easily calculate the average in each category and show these as a bar chart, but is it possible to show the individual data pioints in each category as well? Thanks, Hi, How are you plotting the data points - as columns or line? and also could you show us dummy data layed out so we can understand what you data looks like. If you plot a clustered column chart you could show the average for each cluster as a line within the cluster or as a single point. -- Thanks, Shane Devenshire "lenos" wrote: > I have groups of dat...

Sum Function (was summing 13 cells to the left)
Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense TIA Neil S. Hi see my reply to your old post -- Regards Frank Kabel Frankfurt, Germany Neil S. wrote: > Trying to compose a formula that will only sum 13 cells to the left > (13 months of data). When columns are added, I still only want to > sum thirteens months of data. Can this be done without constanty > changing my formulas? Make sense? ...

adding rows #2
How do I add rows without changing cell location in the formula. For example: The data for the formula is always located in cell B4 and if I add a row on top of B4, I want the formula to remain with B4. Right now, if I add a row, the cell in the formula goes down one row. Hope for help If I understand, you're adding a row higher than B4. This shifts B4 down, but you want the "new" B4 to be the data for the formula. If this is the case you could try this: =formula(OFFSET(A1,3,1,1,1)) That is, as long as you don't add a new top row. ...

Hlookup function
I am currently creating a page that has a column of calculations in it. The calculations are amounts of Future options. at the bottom of the column I calculate an average 6 month total by simply suming 6 months into the future. The question I have is how do I automate the Total sum number at the bottom, because every time I update my worksheet I have to redo all my calculations because the previous months value is now 0 because there is no future value anymore, so I have to rename my range and do my calculation over again. I originally thought of the Hlookup function but I can't get it to ...

Rank Function? #2
Why would my RANK function be returning #DIV/0! ???? Didn't know this was possible. Anybody run into this before? Thanks!! > Didn't know this was possible It isn't - so Excel is saying that you are trying to do the impossible :) Your code will probably need to check for and exclude zero values -- Message posted from http://www.ExcelForum.com ...

identical smtp address - more than one user object
I have just come back form a client site, inherited, where we were trying to work out why certain email addresses were not working as expected. Basically they had a user object in AD - user@domain.com - with the primary smtp set as user@domain.com with no delivery options set. However when I examined another user object in the same active directory I found the identical smtp address listed in their email address list. How is this possible? I was under the impression that you would not be allowed to create the smtp address as it was already in existence. SBS 2003 BTW. ...

Error msg. "Failed to grant access to regenerated view" when adding field to schema using deployment mgr.
Hi, I'm running into a error simply creating a new field in the schema for LEADS. The error tells me to go to the event viewer, which in turn tells me the following: Failed to grant access to the regenerated view Lead after inserting new attribute CFSepassword. Is the system trying to add the field to a view in addition to the table? How can I proceed with this? Any help would be appreciated. Phillip ...

averages
Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

Average days to pay #3
I just need a help from you all. Can any one of you please provide me the formula for caluclating average days to pay? And is debit memo's are considered for it? Please clarify. Thanks in advance. Hi Shree, Please find below the information on how the Average days to pay is calculated. TechKnowledge Content Issue: When and how is the Average Days to Pay calculated in the Customer Credit Summary window (Cards|Sales|Summary|Credit Summary)? Resolution: The Average Days to Pay field is updated when a debit document is fully applied. The average is recalculated as the document i...

Basic IF function help needed
This is a multi-part message in MIME format. ------=_NextPart_000_00AE_01C71C7B.8E24F3D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm using Excel 2002 for the first time and trying to place a function = into a cell. I 'm using IF(N44=3DL, M44-L44, L44-M44) . Which is supposed to mean If the letter L is in cell N44, then subtract = number in L44 from number in M44. Otherwise subtract M44 from L44. If have 2 main questions...First, how to get the result of the function = to show. Right now I see the function typed out in...