I need to have the Totals column in the % change for week over week automatically take the last two weeks when I insert a new week. I'm using the Index-Match w/ Max function. For ex, Row 5, I have the correct answers. Row 6 uses the index formula, which is computing incorrectly. I cannot find where it is wrong. Please help.
The spreadsheet is attached. Thank you in advanced!
My formula is in the gray cells. It should stay correct even when you insert new rows directly above it.
WoW Percent Change Auto-Update 7-28.xlsx
If you putthe calcs at the top, above your headings....
-68% -11% -51% 14% -44%
then you can use this in B1, copied across...
=-INDEX(B$3:B$1000,MATCH(99^99,B$3:B$10000,1)-1)/INDEX(B$3:B$1000,MATCH(99^99,B$3:B$10000,1))+1
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for your inputs. That formula is still spitting out the wrong numbers though. For example, column B is supposed to be -40%, column C = -10%, column D = -34%, column E = 17%, column F = -30. I got those answers from the simple % change formula: (new-old)/old. What do you think the discrepancy is between the formula you used?
In B2 copied right (See attachment)
=(INDEX(B$3:B$1000, MATCH(9^99,B$3:B$1000))-INDEX(B$3:B$1000, MATCH(9^99,B$3:B$1000)-1))/INDEX(B$3:B$1000, MATCH(9^99,B$3:B$1000)-1)
I would use dynamic defined names instead of setting the large range. If you'd prefer to do it that way,
http://www.contextures.com/xlNames01.html#Dynamic
Any questions?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks! That's exactly it. Slightly confused though...in the match function-is the 9^99:B$1000 doing the same thing as the Max function does? And Dynamic Named ranges would work great. Which part would I make that?
Thanks!
Yes, yours would have worked but you forgot a -7 in the denominator.
For Dynamic named ranges, for example,
Clicks = OFFSET($B$3,0,0, COUNT($B$3:$B$1000),1)
then my formula becomes
=(INDEX(Clicks, MATCH(9^99,Clicks))-INDEX(Clicks, MATCH(9^99,Clicks)-1))/INDEX(Clicks, MATCH(9^99,Clicks)-1)
It makes formulas easier to read and write. The disadvantage is it wouldn't allow you to copy right.
Impressions would be set as
=OFFSET(Clicks,0,1)
and so on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks