Within a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Does anyone know a way where I can change the sheet used in an AVERAGEIFS
formula in Excel when I attempt to autofill the formula down the column? the
formula for cell

B2 is:AVERAGEIFS(jun96!AG:AG,jun96!AI:AI,5,jun96!AJ:AJ,5)
For B3 I would like it to be:
AVERAGEIFS(jul96!AG:AG,jul96!AI:AI,5,jul96!AJ:AJ,5)

Where my sheet names are jun96, jul96, aug96 etc? Currently, Excel just
copies the exact same formula when I attemp to autofill.

Thanks!
 
Do you want to increment from Jun96 to Dec96? This could be done a couple of
ways but neither is very efficient and one of the ways results in a rather
long formula. If you only need Jun to Dec I think you'd be better off just
copying your current formula and manually changing the other sheet names.
 
If there's a lot of those cells to fill, then I'd use this techique:

I'd build a formula that builds a string that looks like the formula.

With the first formula in row 1:

="=AVERAGEIFS(" &TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AG:AG,"
&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AI:AI,5,"
&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&"!AJ:AJ,5)"

You may have to adjust the row()+6 in all 3 spots if you're starting in a
different row.

Then drag the formua down the column.

Now you've got a bunch of results that look like your formula:

=AVERAGEIFS(Jul96!AG:AG,Jul96!AI:AI,5,Jul96!AJ:AJ,5)
=AVERAGEIFS(Aug96!AG:AG,Aug96!AI:AI,5,Aug96!AJ:AJ,5)
=AVERAGEIFS(Sep96!AG:AG,Sep96!AI:AI,5,Sep96!AJ:AJ,5)

But they're not formulas.

Select that range.
edit|copy
Edit|paste special|values

They're still text--one more thing to do.

Edit|Replace
what: = (equal sign)
with: =
replace all

Excel will see them as formulas!

Be careful. I'd suggest that you convert a couple first. If your formula that
builds the formula is wrong, you may spend a lot of time dismissing the "where's
this file" dialog.
 
Thanks for the repy! Unfortunately, I have to do this for every month until
june 2006 for various combinations of AI and AJ values. If I had a formula, I
could try to manipulate it for the other combinaitons.
 
If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is on.
(and it's robust against row insertions)
 
Thanks Valko! Will add that in.

T. Valko said:
If starting with the month of June, I would change this:

ROW()+6

To:

ROWS($1:6)

That way you don't have to worry about what specific row the formula is on.
(and it's robust against row insertions)
Click to expand...
 
It works! Thanks a lot Dave, that saved me a LOT of time. Would it be
possible to get an explanation for what the formula actually does? Its a
really handy techique an it would be great if I could work out the mechanics
of it. Thanks again Dave!
 
I see your point--especially starting with Jun--mine started with Jul (oops!).

But I'm not sure I'd bother with the robustness of the formula with this
technique--the formulas are only there for a small amount of time--they need to
be converted to values and then converted to formulas.

In this case, I'd use whatever I remembered <bg>.
 
First, I started with Jul, not Jun. So ignore that error.

This portion is the part that does the real work:

&TEXT(DATE(1996,ROW()+6,1),"mmmyy")&

=date(year,month,day) is the syntax for the =date() function.
In this case, year is 1996, month will resolve to 6, 7, 8, ...., and it'll aways
be the first of that month.

And excel is pretty smart. It knows the 13th month of 1996 is January of 1997.
And on and on and on...

And
=text(somedate,"mmmyy")
just formats that date to look like: Jun96, Jul96, ...

And that portion of the formula replaces your worksheet names (3 times).

But by using a formula, it'll adjust when you drag it down. But you have to do
the extra work of converting to values and converting the string to a formula.
 
Back
Top