Dynamic Formula

[JRF7928]

I have a set of formulas like this: =AVERAGEIFS(I2:I37, K2:K37, "Y") where the row number is variable (37). Tried playing a round with different formula options but to no avail.

So I37 and K37 could be I254 and K254 respectively. I know how to calculate the 254 value but want to insert that calculation into the formula somehow. Make sense?

Thanks!

[hoyasaxa215]

I'm having trouble understanding. Could you upload a sanitized sample file showing what you're trying to accomplish?

[63falcondude]

Why not this?

=AVERAGEIFS(I:I,K:K,"Y")

[JRF7928]

The formula listed is static. If I want to change how many rows are included in the formula (either more or less than 37 used in the example) I'm wondering how to accomplish that. I tried "building the Rows" with a formula like this: =AVERAGEIFS(I2:I&(NamedRange*10+1)... In other words, use a variable to build I37 and K37

Nothing really to upload

Thanks for replying

[JRF7928]

I like the idea - it will mostly work. Assumes I want to use all rows in columns I & K though

Thanks! I replied to this earlier but I don't see it - sorry if it shows up twice!

[63falcondude]

Assumes I want to use all rows in columns I & K though

That's correct. If your ranges will be changing, then I assume that nothing is below the data that would affect the formula.

You could also format your data into a table and refer to the entire table column (instead of the entire sheet column).

[protonLeah]

AvgRng =OFFSET($I$2,0,0,COUNTA(I:I))
CritRng ="OFFSET($K$2,0,0,COUNTA(K:K))"
=AVERAGEIFS(AvgRng, CritRng, "Y")

[JRF7928]

I'll need to review this one. Will require VBA it appears. You have one Range in Quotes and the other not.

Thanks so much for the suggestion. I will check it out.

[63falcondude]

I believe that the suggestion from post #7 was to create dynamic named ranges. This does not require VBA.

[FDibbins]

JRF7928 welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[JRF7928]

Ok sorry. I don't know what I would change my thread title to say actually, so please edit as you see fit. There are 2 solutions prescribed by members but I don't know which is best actually but I will mark it solved

Thanks