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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.