creating dynamic conditional statement for averageifs

**olsoniv** · 02-15-2011, 01:50 PM

this should be an easy one for you pros. I have an averageifs statement that I need to have a dynamic statement created. I am pretty sure I need to use INDIRECT but can't quite figure out how to put it together.

here's the desired statement:

=averageifs(AverageRange,CriteriaRange,">=1500", CriteriaRange,"<=2750")

Where 1500 is held in cell B7 and 2750 is held in cell B6. What I can't figure is how to build the ">=B7" and "<=B6" statements. I've tried using indirect:

=averageifs(AverageRange,CriteriaRange,Indirect(">="&B6), CriteriaRange,Indirect("<="&B7))

but get a Div/0 error. I'm assuming that I need to have the quote at the beginning and end of the statement but not sure how to put them in.

Thanks in advance.

**olsoniv** · 02-15-2011, 02:08 PM

searching around, I found a couple posts with indirect references in them. I tried adding a ' at the beginning and end of the indirect statement

INDIRECT("'>="&B7&"'")

but still no luck...

**olsoniv** · 02-15-2011, 05:23 PM

Maybe this wasn't such an easy problem?

**DonkeyOte** · 02-16-2011, 03:26 AM

No need for INDIRECT here - be wary of using Volatiles (esp. in large models)

=averageifs(AverageRange,CriteriaRange,">="&B6, CriteriaRange,"<="&B7)

in essence the >= and <= criteria values are "strings" so you use & to concatenate the constant (operator) with the variable (contained in a cell)

">="&B7 becomes ">=1500"