AverageIfs and Dynamic Range

I have the following formula:

=IFERROR(AVERAGEIFS($R:$R,$E:$E,"lm*",$H:$H,"*15",$R:$R,">30"),0)

Running into a problem with circular reference because there is data in column R.  Or the average is off because there is other data in the  column that should not be counted.  The data to average starts on row 20 and additional lines will be added throughout the year.
jmac001Asked:
Who is Participating?
 
gowflowCommented:
Here it is I made a sample for Col P which is values of LM. you can do the same for all other columns. Basically here is how it works.

1. Record Maximum rows in D5

I have put a formula in D5 that will always capture the maximum row in this workbook it happen to be 38 and will use this number when building the formula.
=MATCH(LOOKUP(2,1/(R:R<>""),R:R),R:R,0)

2. Build the formula in P9 now showing in T9

Then we need to build each formula that you have and making reference to $D$5 to get the maximum row and basically this is how it works in your formulas you have Ranges like
R:R so here it is replaced by $R$20:$R$38 this would be an implicit formula specifying 38 but if we want it dynamic then it will be like:
$R$20:INDIRECT("$R$"&$D$5)
So the formula in P9 should be as shown in T9 and is:
=IFERROR(AVERAGEIFS($R$20:INDIRECT("$R$"&$D$5),$E$20:INDIRECT("$E$"&$D$5),"lm*",$H$20:INDIRECT("$H$"&$D$5),"*13",$R$20:INDIRECT("$R$"&$D$5),"<=30"),0)

You will notice I added T10,T11 that are reflection of R10,R11.

Pls check the attached file and see if it is fine.
gowflow
EE-Sample-AveageIF.xlsx
0
 
gowflowCommented:
Can you post a sample of this workbook ? as you may need a tweak in the formula but not sure what is the limit downward of the formula. You will need an OFFSET with this not a full column H:H ...

gowflow
0
 
barry houdiniCommented:
What sort of data do you have in column H? You can't use wildcards with numbers in these type of formulas - so if "*15" is designed to match the last two digits of a number (or date) that won't work - if you are trying to get dates in 2015 then use two conditions like this:

=IFERROR(AVERAGEIFS($R:$R,$E:$E,"lm*",$H:$H,">="&DATE(2015,1,1),$H:$H,"<"&DATE(2016,1,1),$R:$R,">30"),0)

regards, barry
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gowflowCommented:
As barry mentioned you cannot do something like *15 on a number but could do >15 and a sample data would help us demystify your issue.

gowflow
0
 
jmac001Author Commented:
Posting a sample of the workbook.  column H is the season which can be FYY (F15) or SYY (S15).  One of the metrics that I am looking for is that current year to all others.
EE-Sample-AveageIF.xlsx
0
 
gowflowCommented:
Which are the cells you want to fix the formula for ?
gowflow
0
 
barry houdiniCommented:
As you say, you will get a circular reference with a formula in column R that references that whole column - why not make your references start at row 20 - I get 0 with your formula because none of the rows satisfy the criteria but something like this will give you a non-zero result for R9, adjust as required

=IFERROR(AVERAGEIFS($R$20:$R$100,$E$20:$E$100,"lm*",$H$20:$H$100,"*13",$R$20:$R$100,"<=30"),0)

regards barry
0
 
jmac001Author Commented:
Gowflow I would like to start the count on row 20, however the end row will grow as projects are completed and added to the worksheet.  

Barry I have been using the formula that you suggested, however is there a way to make the range dynamic? I have couple different version of this workbook where the number of completed projects is going surpass 100 and it is a little time consuming to update all of the formulas.
0
 
gowflowCommented:
Now this is my suggested version of the formula to make it dynamic now if it is fine with you then we can maybe build a VBA code lateron and get the formulas modified automatically. Presume this would be under a different scoope as will need to study carefully how the formulas are built and account for each scenario as I see that you have examples where the range is specified and where others it is not.

gowflow
0
 
barry houdiniCommented:
however is there a way to make the range dynamic?

Personally I would just make the range large enough to cope with any future expansion, e.g. finish at row 1000 or 5000.

With the "IFS" family of functions, e.g. SUMIFS/COUNTIFS/AVERAGEIFS the formula is clever enough to only use the "used range" (unlike other functions like SUMPRODUCT) so there's no particular performance downside to using a larger range than that required - in fact it's probably slower using a dynamic range simply through the additional cost of calculating that!

regards, barry
0
 
jmac001Author Commented:
Gowflow thanks for the formula. I have thought about VBA however we currently going through a system upgrade and not sure how fields are going to change.

Barry... will keep in mind what you state about how the ifs function work, gowflow solution will work better in the event that I have to hand the report off to someone who is not as proficient in using functions and formulas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.