Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

splicing formulas together

Status
Not open for further replies.

pkhoo4

MIS
Joined
May 26, 2009
Messages
6
Location
US
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%
 


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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
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
 
skip,
obviously, year2 would be sumproduct(--c2:c5=2009, a2:a5, b2:b5, b2:b5) missed that before i post

thank you
peter
 



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
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
 


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

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 


[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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
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.

 



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
skip,

thank you so much, you are a life saver!

peter
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top