IFSum(Vlookup...

[J Shrimps, Jr.]

Need to divide a value in a column by a set of
running sum values in an adjacent column.
Data looks like this:

Product Month Bal RemBal Result
Calculation:
100 01 $10.00 $10 100%
10/10
100 02 $5.00 $15 25%
5/(10+15)
100 03 $2.00 $12 32%
12/(10+15+12)
......
100 60 $9.00 $9 .01%
9/(10+15+12+ 60 months of balances )
400 01 $8.00 $8 100%
8/8
400 02 $5.00 $35 19%
5/(8+35)

now we have a new value and the month's sequence starts at 1 (01).

Currently the formula is:
row 8 =P8/SUM(R$7:R8)
row 9 =P9/SUM(R$7:R9)
row 10 =P10/SUM(R$7:R10)
row 11 =P11/SUM(R$7:R11)
in this case, the new product started at row 7 and might continue
for 60 more rows, or just 6 more, where the $R$7 part of the formula would
change again.

Usually, every 60 or so lines, (the # of months is different, depending
on the product) l have to re-start the formula (replacing $R$7 with $R$67
for example) so the SUM starts with month
0 and continues summing the contents of column R ,
until the last month for that product.

I have over 24,000 lines of this, it is getting very tedious to have to
re-copy the
formula every time there is a new product., making sure my formula captures
a running sum starting with the first month and divides the value in the
current row
into that running sum.
I'm planning on starting with "IF(P8<p7" - ie a new product has started
so start the forumula using the current row as the cell placed in the
formula
"sum($R$7", start the running sum up,
and divide the contents of the current row into the running sum values until
a new product, where the process starts all over again.
Is it possible to "Anchor" a running sum formula based on changing criteria
with some kind of vlookup/Sumif (or something else)?

[Biff]

Hi!

Hmmm....

The formula to do this is fairly simple but I'm not getting the results you
have posted.

10/10 = 100% that one's OK
5/(10+15) = 25% this one is not correct, should be 20%

Everything below that one for product 100 is incorrect.

Based on the pattern shouldn't this:

> 12/(10+15+12)

Be:

2/(10+15+12)

Here's the formula:

=P7/SUMIF(Product_column$7:Product_column7,Product_column7,R$7:R7)

Replace Product_column with the letter of the actual product column. I can't
tell from your table what that might be!!!

Copy down as needed.

Biff

"J Shrimps, Jr." <[email protected]> wrote in message
news:[email protected]...
> Need to divide a value in a column by a set of
> running sum values in an adjacent column.
> Data looks like this:
>
> Product Month Bal RemBal Result
> Calculation:
> 100 01 $10.00 $10 100%
> 10/10
> 100 02 $5.00 $15 25%
> 5/(10+15)
> 100 03 $2.00 $12 32%
> 12/(10+15+12)
> .....
> 100 60 $9.00 $9 .01%
> 9/(10+15+12+ 60 months of balances )
> 400 01 $8.00 $8 100%
> 8/8
> 400 02 $5.00 $35 19%
> 5/(8+35)
>
> now we have a new value and the month's sequence starts at 1 (01).
>
> Currently the formula is:
> row 8 =P8/SUM(R$7:R8)
> row 9 =P9/SUM(R$7:R9)
> row 10 =P10/SUM(R$7:R10)
> row 11 =P11/SUM(R$7:R11)
> in this case, the new product started at row 7 and might continue
> for 60 more rows, or just 6 more, where the $R$7 part of the formula would
> change again.
>
> Usually, every 60 or so lines, (the # of months is different, depending
> on the product) l have to re-start the formula (replacing $R$7 with $R$67
> for example) so the SUM starts with month
> 0 and continues summing the contents of column R ,
> until the last month for that product.
>
> I have over 24,000 lines of this, it is getting very tedious to have to
> re-copy the
> formula every time there is a new product., making sure my formula
> captures
> a running sum starting with the first month and divides the value in the
> current row
> into that running sum.
> I'm planning on starting with "IF(P8<p7" - ie a new product has started
> so start the forumula using the current row as the cell placed in the
> formula
> "sum($R$7", start the running sum up,
> and divide the contents of the current row into the running sum values
> until
> a new product, where the process starts all over again.
> Is it possible to "Anchor" a running sum formula based on changing
> criteria
> with some kind of vlookup/Sumif (or something else)?
>
>
>

[J Shrimps, Jr.]

I did the math myself, so of course it's not correct.

The question is:

>> Is it possible to "Anchor" a running sum formula based on changing
>>replacing $R$7 with $R$67 for example) IN THE FORMULA,
there are 24,000 rows and every 60 or 70 rows, the SUM
formula has to be anchored all over again.
> > criteria with some kind of vlookup/Sumif (or something else)?



"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Hmmm....
>
> The formula to do this is fairly simple but I'm not getting the results
you
> have posted.
>
> 10/10 = 100% that one's OK
> 5/(10+15) = 25% this one is not correct, should be 20%
>
> Everything below that one for product 100 is incorrect.
>
> Based on the pattern shouldn't this:
>
> > 12/(10+15+12)
>
> Be:
>
> 2/(10+15+12)
>
> Here's the formula:
>
> =P7/SUMIF(Product_column$7:Product_column7,Product_column7,R$7:R7)
>
> Replace Product_column with the letter of the actual product column. I
can't
> tell from your table what that might be!!!
>
> Copy down as needed.
>
> Biff
>
> "J Shrimps, Jr." <[email protected]> wrote in message
> news:[email protected]...
> > Need to divide a value in a column by a set of
> > running sum values in an adjacent column.
> > Data looks like this:
> >
> > Product Month Bal RemBal Result
> > Calculation:
> > 100 01 $10.00 $10 100%
> > 10/10
> > 100 02 $5.00 $15 25%
> > 5/(10+15)
> > 100 03 $2.00 $12 32%
> > 12/(10+15+12)
> > .....
> > 100 60 $9.00 $9 .01%
> > 9/(10+15+12+ 60 months of balances )
> > 400 01 $8.00 $8 100%
> > 8/8
> > 400 02 $5.00 $35 19%
> > 5/(8+35)
> >
> > now we have a new value and the month's sequence starts at 1 (01).
> >
> > Currently the formula is:
> > row 8 =P8/SUM(R$7:R8)
> > row 9 =P9/SUM(R$7:R9)
> > row 10 =P10/SUM(R$7:R10)
> > row 11 =P11/SUM(R$7:R11)
> > in this case, the new product started at row 7 and might continue
> > for 60 more rows, or just 6 more, where the $R$7 part of the formula
would
> > change again.
> >
> > Usually, every 60 or so lines, (the # of months is different, depending
> > on the product) l have to re-start the formula (replacing $R$7 with
$R$67
> > for example) so the SUM starts with month
> > 0 and continues summing the contents of column R ,
> > until the last month for that product.
> >
> > I have over 24,000 lines of this, it is getting very tedious to have to
> > re-copy the
> > formula every time there is a new product., making sure my formula
> > captures
> > a running sum starting with the first month and divides the value in
the
> > current row
> > into that running sum.
> > I'm planning on starting with "IF(P8<p7" - ie a new product has started
> > so start the forumula using the current row as the cell placed in the
> > formula
> > "sum($R$7", start the running sum up,
> > and divide the contents of the current row into the running sum values
> > until
> > a new product, where the process starts all over again.
> > Is it possible to "Anchor" a running sum formula based on changing
> > criteria
> > with some kind of vlookup/Sumif (or something else)?
> >
> >
> >
>
>

[Richard Buttrey]

Hi,

One solution is as follows. It assumes your data is sorted by product
and month. i.e. all product 100 (say) are listed underneath each other
and the months are ascending.

Use a helper column (say col F) if your data below is in cols A:E. It

Put the following formula in col F, and copy it down.

=IF(A10<>A9,ROW(),F9)

This recognises when a product changes and results in the first row of
every change in product number being recorded for that product range.

Then your result formula in column E will be

=INDIRECT("C$"&ROW())/SUM(INDIRECT("$D$"&F10):INDIRECT("D"&ROW()))


This assumes your data starts in row 10. Adjust accordingly

HTH



On Thu, 6 Oct 2005 21:49:01 -0400, "J Shrimps, Jr."
<[email protected]> wrote:

>Need to divide a value in a column by a set of
>running sum values in an adjacent column.
>Data looks like this:
>
>Product Month Bal RemBal Result
>Calculation:
>100 01 $10.00 $10 100%
>10/10
>100 02 $5.00 $15 25%
>5/(10+15)
>100 03 $2.00 $12 32%
>12/(10+15+12)
>.....
>100 60 $9.00 $9 .01%
>9/(10+15+12+ 60 months of balances )
>400 01 $8.00 $8 100%
>8/8
>400 02 $5.00 $35 19%
>5/(8+35)
>
>now we have a new value and the month's sequence starts at 1 (01).
>
>Currently the formula is:
>row 8 =P8/SUM(R$7:R8)
>row 9 =P9/SUM(R$7:R9)
>row 10 =P10/SUM(R$7:R10)
>row 11 =P11/SUM(R$7:R11)
>in this case, the new product started at row 7 and might continue
>for 60 more rows, or just 6 more, where the $R$7 part of the formula would
>change again.
>
>Usually, every 60 or so lines, (the # of months is different, depending
>on the product) l have to re-start the formula (replacing $R$7 with $R$67
>for example) so the SUM starts with month
>0 and continues summing the contents of column R ,
>until the last month for that product.
>
>I have over 24,000 lines of this, it is getting very tedious to have to
>re-copy the
>formula every time there is a new product., making sure my formula captures
>a running sum starting with the first month and divides the value in the
>current row
>into that running sum.
>I'm planning on starting with "IF(P8<p7" - ie a new product has started
>so start the forumula using the current row as the cell placed in the
>formula
>"sum($R$7", start the running sum up,
>and divide the contents of the current row into the running sum values until
>a new product, where the process starts all over again.
>Is it possible to "Anchor" a running sum formula based on changing criteria
>with some kind of vlookup/Sumif (or something else)?
>
>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Biff]

Hi!

My suggestion does what you want.

Biff

"J Shrimps, Jr." <[email protected]> wrote in message
news:[email protected]...
>I did the math myself, so of course it's not correct.
>
> The question is:
>
>>> Is it possible to "Anchor" a running sum formula based on changing
>>>replacing $R$7 with $R$67 for example) IN THE FORMULA,
> there are 24,000 rows and every 60 or 70 rows, the SUM
> formula has to be anchored all over again.
>> > criteria with some kind of vlookup/Sumif (or something else)?
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Hmmm....
>>
>> The formula to do this is fairly simple but I'm not getting the results
> you
>> have posted.
>>
>> 10/10 = 100% that one's OK
>> 5/(10+15) = 25% this one is not correct, should be 20%
>>
>> Everything below that one for product 100 is incorrect.
>>
>> Based on the pattern shouldn't this:
>>
>> > 12/(10+15+12)
>>
>> Be:
>>
>> 2/(10+15+12)
>>
>> Here's the formula:
>>
>> =P7/SUMIF(Product_column$7:Product_column7,Product_column7,R$7:R7)
>>
>> Replace Product_column with the letter of the actual product column. I
> can't
>> tell from your table what that might be!!!
>>
>> Copy down as needed.
>>
>> Biff
>>
>> "J Shrimps, Jr." <[email protected]> wrote in message
>> news:[email protected]...
>> > Need to divide a value in a column by a set of
>> > running sum values in an adjacent column.
>> > Data looks like this:
>> >
>> > Product Month Bal RemBal Result
>> > Calculation:
>> > 100 01 $10.00 $10 100%
>> > 10/10
>> > 100 02 $5.00 $15 25%
>> > 5/(10+15)
>> > 100 03 $2.00 $12 32%
>> > 12/(10+15+12)
>> > .....
>> > 100 60 $9.00 $9 .01%
>> > 9/(10+15+12+ 60 months of balances )
>> > 400 01 $8.00 $8 100%
>> > 8/8
>> > 400 02 $5.00 $35 19%
>> > 5/(8+35)
>> >
>> > now we have a new value and the month's sequence starts at 1 (01).
>> >
>> > Currently the formula is:
>> > row 8 =P8/SUM(R$7:R8)
>> > row 9 =P9/SUM(R$7:R9)
>> > row 10 =P10/SUM(R$7:R10)
>> > row 11 =P11/SUM(R$7:R11)
>> > in this case, the new product started at row 7 and might continue
>> > for 60 more rows, or just 6 more, where the $R$7 part of the formula
> would
>> > change again.
>> >
>> > Usually, every 60 or so lines, (the # of months is different, depending
>> > on the product) l have to re-start the formula (replacing $R$7 with
> $R$67
>> > for example) so the SUM starts with month
>> > 0 and continues summing the contents of column R ,
>> > until the last month for that product.
>> >
>> > I have over 24,000 lines of this, it is getting very tedious to have to
>> > re-copy the
>> > formula every time there is a new product., making sure my formula
>> > captures
>> > a running sum starting with the first month and divides the value in
> the
>> > current row
>> > into that running sum.
>> > I'm planning on starting with "IF(P8<p7" - ie a new product has started
>> > so start the forumula using the current row as the cell placed in the
>> > formula
>> > "sum($R$7", start the running sum up,
>> > and divide the contents of the current row into the running sum values
>> > until
>> > a new product, where the process starts all over again.
>> > Is it possible to "Anchor" a running sum formula based on changing
>> > criteria
>> > with some kind of vlookup/Sumif (or something else)?
>> >
>> >
>> >
>>
>>
>
>

[J Shrimps, Jr.]

Perfect!!
Just did all 24,000 rows with your formula.
Have never used - or heard of - the Row() or Indirect() functions.
Verified your formula by comparing your result with
with a few hundred rows that I knew were correct,
also caught many examples where
I had cut-and-pasted the old formula incorrectly, or pasted right
past month 0 for that product. With your formula, I started
@ row 1, and pasted all the way down to row 24,000.

On this side of the pond, we say you are a G E N I U S.
I can't believe we ever revolted.



"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> Hi,
>
> One solution is as follows. It assumes your data is sorted by product
> and month. i.e. all product 100 (say) are listed underneath each other
> and the months are ascending.
>
> Use a helper column (say col F) if your data below is in cols A:E. It
>
> Put the following formula in col F, and copy it down.
>
> =IF(A10<>A9,ROW(),F9)
>
> This recognises when a product changes and results in the first row of
> every change in product number being recorded for that product range.
>
> Then your result formula in column E will be
>
> =INDIRECT("C$"&ROW())/SUM(INDIRECT("$D$"&F10):INDIRECT("D"&ROW()))
>
>
> This assumes your data starts in row 10. Adjust accordingly
>
> HTH
>
>
>
> On Thu, 6 Oct 2005 21:49:01 -0400, "J Shrimps, Jr."
> <[email protected]> wrote:
>
> >Need to divide a value in a column by a set of
> >running sum values in an adjacent column.
> >Data looks like this:
> >
> >Product Month Bal RemBal Result
> >Calculation:
> >100 01 $10.00 $10 100%
> >10/10
> >100 02 $5.00 $15 25%
> >5/(10+15)
> >100 03 $2.00 $12 32%
> >12/(10+15+12)
> >.....
> >100 60 $9.00 $9 .01%
> >9/(10+15+12+ 60 months of balances )
> >400 01 $8.00 $8 100%
> >8/8
> >400 02 $5.00 $35 19%
> >5/(8+35)
> >
> >now we have a new value and the month's sequence starts at 1 (01).
> >
> >Currently the formula is:
> >row 8 =P8/SUM(R$7:R8)
> >row 9 =P9/SUM(R$7:R9)
> >row 10 =P10/SUM(R$7:R10)
> >row 11 =P11/SUM(R$7:R11)
> >in this case, the new product started at row 7 and might continue
> >for 60 more rows, or just 6 more, where the $R$7 part of the formula
would
> >change again.
> >
> >Usually, every 60 or so lines, (the # of months is different, depending
> >on the product) l have to re-start the formula (replacing $R$7 with $R$67
> >for example) so the SUM starts with month
> >0 and continues summing the contents of column R ,
> >until the last month for that product.
> >
> >I have over 24,000 lines of this, it is getting very tedious to have to
> >re-copy the
> >formula every time there is a new product., making sure my formula
captures
> >a running sum starting with the first month and divides the value in the
> >current row
> >into that running sum.
> >I'm planning on starting with "IF(P8<p7" - ie a new product has started
> >so start the forumula using the current row as the cell placed in the
> >formula
> >"sum($R$7", start the running sum up,
> >and divide the contents of the current row into the running sum values
until
> >a new product, where the process starts all over again.
> >Is it possible to "Anchor" a running sum formula based on changing
criteria
> >with some kind of vlookup/Sumif (or something else)?
> >
> >
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

[Richard Buttrey]

On Fri, 7 Oct 2005 18:32:29 -0400, "J Shrimps, Jr."
<[email protected]> wrote:

>Perfect!!
>Just did all 24,000 rows with your formula.
>Have never used - or heard of - the Row() or Indirect() functions.
>Verified your formula by comparing your result with
>with a few hundred rows that I knew were correct,
>also caught many examples where
>I had cut-and-pasted the old formula incorrectly, or pasted right
>past month 0 for that product. With your formula, I started
>@ row 1, and pasted all the way down to row 24,000.
>
>On this side of the pond, we say you are a G E N I U S.
>I can't believe we ever revolted.

Wasn't it the tea you didn't like?

Not to worry, I'm sure Tony Blair is even now planning how best to
re-annexe you all.

:-)

Cheers.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Biff]

http://img70.imageshack.us/img70/6198/sumif2un.jpg

Biff

"J Shrimps, Jr." <[email protected]> wrote in message
news:[email protected]...
> Perfect!!
> Just did all 24,000 rows with your formula.
> Have never used - or heard of - the Row() or Indirect() functions.
> Verified your formula by comparing your result with
> with a few hundred rows that I knew were correct,
> also caught many examples where
> I had cut-and-pasted the old formula incorrectly, or pasted right
> past month 0 for that product. With your formula, I started
> @ row 1, and pasted all the way down to row 24,000.
>
> On this side of the pond, we say you are a G E N I U S.
> I can't believe we ever revolted.
>
>
>
> "Richard Buttrey" <[email protected]> wrote in
> message news:[email protected]...
>> Hi,
>>
>> One solution is as follows. It assumes your data is sorted by product
>> and month. i.e. all product 100 (say) are listed underneath each other
>> and the months are ascending.
>>
>> Use a helper column (say col F) if your data below is in cols A:E. It
>>
>> Put the following formula in col F, and copy it down.
>>
>> =IF(A10<>A9,ROW(),F9)
>>
>> This recognises when a product changes and results in the first row of
>> every change in product number being recorded for that product range.
>>
>> Then your result formula in column E will be
>>
>> =INDIRECT("C$"&ROW())/SUM(INDIRECT("$D$"&F10):INDIRECT("D"&ROW()))
>>
>>
>> This assumes your data starts in row 10. Adjust accordingly
>>
>> HTH
>>
>>
>>
>> On Thu, 6 Oct 2005 21:49:01 -0400, "J Shrimps, Jr."
>> <[email protected]> wrote:
>>
>> >Need to divide a value in a column by a set of
>> >running sum values in an adjacent column.
>> >Data looks like this:
>> >
>> >Product Month Bal RemBal Result
>> >Calculation:
>> >100 01 $10.00 $10 100%
>> >10/10
>> >100 02 $5.00 $15 25%
>> >5/(10+15)
>> >100 03 $2.00 $12 32%
>> >12/(10+15+12)
>> >.....
>> >100 60 $9.00 $9 .01%
>> >9/(10+15+12+ 60 months of balances )
>> >400 01 $8.00 $8 100%
>> >8/8
>> >400 02 $5.00 $35 19%
>> >5/(8+35)
>> >
>> >now we have a new value and the month's sequence starts at 1 (01).
>> >
>> >Currently the formula is:
>> >row 8 =P8/SUM(R$7:R8)
>> >row 9 =P9/SUM(R$7:R9)
>> >row 10 =P10/SUM(R$7:R10)
>> >row 11 =P11/SUM(R$7:R11)
>> >in this case, the new product started at row 7 and might continue
>> >for 60 more rows, or just 6 more, where the $R$7 part of the formula
> would
>> >change again.
>> >
>> >Usually, every 60 or so lines, (the # of months is different, depending
>> >on the product) l have to re-start the formula (replacing $R$7 with
>> >$R$67
>> >for example) so the SUM starts with month
>> >0 and continues summing the contents of column R ,
>> >until the last month for that product.
>> >
>> >I have over 24,000 lines of this, it is getting very tedious to have to
>> >re-copy the
>> >formula every time there is a new product., making sure my formula
> captures
>> >a running sum starting with the first month and divides the value in
>> >the
>> >current row
>> >into that running sum.
>> >I'm planning on starting with "IF(P8<p7" - ie a new product has started
>> >so start the forumula using the current row as the cell placed in the
>> >formula
>> >"sum($R$7", start the running sum up,
>> >and divide the contents of the current row into the running sum values
> until
>> >a new product, where the process starts all over again.
>> >Is it possible to "Anchor" a running sum formula based on changing
> criteria
>> >with some kind of vlookup/Sumif (or something else)?
>> >
>> >
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>
>