splicing formulas together

[pkhoo4]

hi
need help here. I need to sum the product of sales and retention. For year 1, I would use sumproduct(a2:a3,b2:b3),
for year 2, I would use sumproduct(a2:a3,b2:b3,b2:b3) but now I need to sum it to the nth year in a formula and seem to figure it out. is it possible to raise b2:b3 to a power? or use sumproduct(a2:a3,[add code based on a vlookup])??? thank you for any directions

A B
sales retention%
10 75%
20 66%

 

[SkipVought]

Hi pkhoo4, and welcome to Tek-Tips.

The example that you posted has no date-related component. How can you aggregate by date without dates?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pkhoo4]

hi skip,
thank you for catching my error. Here's my question again
For year 1, I would use sumproduct(--c2:c5=2008,a2:a5, b2:b5), for year 2, I would use sumproduct(--c2:c5=2008, a2:a5, b2:b5, b2:b5) but now I need to sum it to the nth year in a formula and seem to figure it out. is it possible to raise b2:b5 to a power? or use sumproduct(a2:a3,[add code based on a vlookup])??? thank you for any directions

A B C
sales retention% Year
10 75% 2008
20 66% 2008
30 60% 2009
40 60% 2009

peter

 

[pkhoo4]

skip,
obviously, year2 would be sumproduct(--c2:c5=2009, a2:a5, b2:b5, b2:b5) missed that before i post

thank you
peter

 

[SkipVought]

lets suppose that you enter the FROM year into E1 and the THRU year into E2
[tt]
=sumproduct((c2:c5>=E1)*(c2:c5<=E2)*(a2:a5)*(b2:b5))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pkhoo4]

skip,

this will not solve problem.
year 1 (2008) the sales * retention %
year 2 (2009) the sales * retention % * retention %

i tried sumproduct((c2:C5=yr),a2:a5,(b2:b5^yrnum))
where yr = current select year
and yrnum = 2 for year 2

Alternatively, i was thinking of putting a 20 rows of formula text, and subsitute it into the formula as needed. For some reason, i thought that is possible. I have not done this for some time. thank you for taking the time.

peter

 

[SkipVought]

I don't understand this...
[tt]
year 2 (2009) the sales * retention % * retention %
[/tt]
Why the second retention %???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

[tt]
=SUMPRODUCT((Year>=E1)*(Year<=E2)*(sales)*(retention)^(Ycnt))
[/tt]
[tt]
sales retention% Year Ycnt
10 75% 2008 1
20 66% 2008 1
30 60% 2009 2
40 60% 2009 2
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pkhoo4]

skip,

this is for an analysis of channel distribution. I had left out the product and channel information. the second year would have less of a retention than the first year.
how can i clip data for you to see? I think I am not stating the problem correctly.

 

[SkipVought]

Copy 'n' paste. Use TGML tags to pretty it up as I did in my examples. Replace TABS with SPACES manually if necessary to line up columns. VIEW your post before you send.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pkhoo4]

skip,

thank you so much, you are a life saver!

peter