asked on

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.

Jacques Geday

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

barry houdini

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

Jacques Geday

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

jmac001

ASKER

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

Jacques Geday

Which are the cells you want to fix the formula for ?
gowflow

barry houdini

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

jmac001

ASKER

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.

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacques Geday

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

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmac001

ASKER

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.