Dynamicly calulation based on a input value

[Dendras]

Hi!
I've got a challenge I hope all you skilled excel users can help me with.

In the enclosed file, in Sheet1, I've made dropdown menu with week numbers.
I need help to make a dynamic calucaltion based on the value in "Weeks" (Sheet1!B5)
and input the value in G6

Scenario:
- I select week 2015-25. (In sheet1)
- I want then excel to: In sheet2, SUM (F5:F12) / (G5:G12)
- The value should be visible on Sheet1!G6
- No mather what kind of week I select from the drop down list, it will dynamicly calulate from week 2015-18 to 2015-xx

I've tried with Index and match, but have not managed to range-calculate it, it only gives the value for the certain week, but I want from 2015-18 to 2015-xx

I've also tried with OFFSET and SUMIF without any luck.

Please help me, in advance, thanks!

 

[Luke M]

I think you want this:
=SUM(OFFSET(Sheet2!$F$5,,,MATCH(B5,WEEKS,0),1))/
SUM(OFFSET(Sheet2!$G$5,,,MATCH(B5,WEEKS,0),1))

or non-volatile solution:
=SUM(Sheet2!F5:INDEX(Sheet2!F5:F57,MATCH(B5,WEEKS,0)))/
SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0)))

 

[Dendras]

I think you want this:
=SUM(OFFSET(Sheet2!$F$5,,,MATCH(B5,WEEKS,0),1))/
SUM(OFFSET(Sheet2!$G$5,,,MATCH(B5,WEEKS,0),1))

or non-volatile solution:
=SUM(Sheet2!F5:INDEX(Sheet2!F5:F57,MATCH(B5,WEEKS,0)))/
SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0)))

Many thanks, this solved my issue! :-D

My next question is, how can one learn oneself to solve these problems? How did you solve it ?

 

[Luke M]

Good question. I'm not completely sure how to answer it...personally, I spend a lot of time reading XL blogs so that I can stay up to date on the latest tricks and techniques. The other part is spending time in forums. This presents me with essentially tons of "homework problems" where I can actively practice what I've learned. This keeps the info fresh in my head, and I also get a chance to see what other XL ninjas come up with.

For this particular problem, I've done a lot of work with dynamic ranges for charts (check out peltiertech.com) and from there, it was pretty similar technique to include the range in a SUM function.

 

[Somendra Misra]

Hi,

Just another option, if you can test this on your real data.

=SUMPRODUCT((WEEKS<=Sheet1!$B$5)*Sheet2!$F$5:$F$57)/SUMPRODUCT((WEEKS<=Sheet1!$B$5)*Sheet2!$G$5:$G$57)

Regards,

 

[Dendras]

Hi again!

I've encounterd a new problem.

When i divide negative / negative, i dont get the real results. I've inserted some dummbies figures and enclosed the file.

Store 1; the forumla (Luke M) above, gives me 28,6% in margin YTD, but the real figure is -71,4%

Store 2: gives 79,3% growth in margin, while the correct is 20,7% growth.

How can i tackle this? I've tried some with ABS, but that didnt work the way i wanted.

 

[Chihiro]

This is the calculation you are looking for. You were on right track with ABS

For G6: =(SUM(Sheet2!F5:INDEX(Sheet2!F5:F57,MATCH(B5,WEEKS,0)))-SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0))))/ABS(SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0))))

 

[Dendras]

This is the calculation you are looking for. You were on right track with ABS

For G6: =(SUM(Sheet2!F5:INDEX(Sheet2!F5:F57,MATCH(B5,WEEKS,0)))-SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0))))/ABS(SUM(Sheet2!G5:INDEX(Sheet2!G5:G57,MATCH(B5,WEEKS,0))))

Awesome, thanks!