SUMPRODUCT
 

With assistance from one of the learned members, I used the following formula
to add together the sum of each pair of adjacent columns (this calculates
total daily interest for all accounts, where the balance and interest are
unique per account)

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:$GH4/365),2)

and its close cousin "0".

I have had to make 2 changes to deal with:-

1) a requirement to show both the ledger and adjusted balances for each
account, although the calculation is only on the adjusted i.e. the second,
balance

2) Excel's 256 column feature; my macro now returns the data in rows

Mainly by trial and error, I have got the following to work:-

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

To do this, I insert 12 rows at the top of my worksheet, and paste in a
series of formulae. The first instance of the above formula is in D5. The
figures in D13:D15 represent ledger bal/adjusted bal/rate, repeated for each
account down the page.

I then tried inserting a blank row as row 1 or 13, so I can insert dates.
The cell references increment as expected, but the resulting calculations are
dramatically wrong. Try as I might, I can't figure out what I need to do to
get this to work, although I'm sure it's really obvious.

Assistance greatly appreciated.

SUMPRODUCT
 

Hi!

I then tried inserting a blank row as row 1 or 13, so I can insert dates.

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

the resulting calculations are dramatically wrong.

When you insert rows before the formula cells that changes (screws up!) the
MOD( ) calculation.

Try this:

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500)-ROW(D$13),3)=0),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

Biff

"KeenKiwi" wrote in message
...
With assistance from one of the learned members, I used the following
formula
to add together the sum of each pair of adjacent columns (this calculates
total daily interest for all accounts, where the balance and interest are
unique per account)

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:$GH4/365),2)

and its close cousin "0".

I have had to make 2 changes to deal with:-

1) a requirement to show both the ledger and adjusted balances for each
account, although the calculation is only on the adjusted i.e. the second,
balance

2) Excel's 256 column feature; my macro now returns the data in rows

Mainly by trial and error, I have got the following to work:-

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

To do this, I insert 12 rows at the top of my worksheet, and paste in a
series of formulae. The first instance of the above formula is in D5. The
figures in D13:D15 represent ledger bal/adjusted bal/rate, repeated for
each
account down the page.

I then tried inserting a blank row as row 1 or 13, so I can insert dates.
The cell references increment as expected, but the resulting calculations
are
dramatically wrong. Try as I might, I can't figure out what I need to do
to
get this to work, although I'm sure it's really obvious.

Assistance greatly appreciated.

SUMPRODUCT
 

Thanks, Biff, but I'm not getting a sensible result either with or without
inserting a blank row. Sorry, but I don't understand the original or your
amended version well enough to figure out where it's going wrong.

With the contents of D13:D18 as:-

2447897.68
2547897.68
0.5
325101.03
-325101.03
0.7

The results should be -325101.03*(0.7/365) (<0 formula) and
2547897.68*(0.5/365) (0 formula).

The actual answers I'm getting are 0 and 16,798,088,054.91.

Any suggestions?

"Biff" wrote:

Hi!

I then tried inserting a blank row as row 1 or 13, so I can insert dates.

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

the resulting calculations are dramatically wrong.

When you insert rows before the formula cells that changes (screws up!) the
MOD( ) calculation.

Try this:

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500)-ROW(D$13),3)=0),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

Biff

"KeenKiwi" wrote in message
...
With assistance from one of the learned members, I used the following
formula
to add together the sum of each pair of adjacent columns (this calculates
total daily interest for all accounts, where the balance and interest are
unique per account)

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:$GH4/365),2)

and its close cousin "0".

I have had to make 2 changes to deal with:-

1) a requirement to show both the ledger and adjusted balances for each
account, although the calculation is only on the adjusted i.e. the second,
balance

2) Excel's 256 column feature; my macro now returns the data in rows

Mainly by trial and error, I have got the following to work:-

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

To do this, I insert 12 rows at the top of my worksheet, and paste in a
series of formulae. The first instance of the above formula is in D5. The
figures in D13:D15 represent ledger bal/adjusted bal/rate, repeated for
each
account down the page.

I then tried inserting a blank row as row 1 or 13, so I can insert dates.
The cell references increment as expected, but the resulting calculations
are
dramatically wrong. Try as I might, I can't figure out what I need to do
to
get this to work, although I'm sure it's really obvious.

Assistance greatly appreciated.

SUMPRODUCT
 

Based on your small sample:

-325101.03*(0.7/365) = -623.78
2547897.68*(0.5/365) = 3490.27

Using these formulas I get the above results and they're robust against row
insertions: (before the formula rows)

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$18)-ROW(D$13),3)=1),--(D$13:D$18<0),D$13:D$18*D$14:D$19/365),2)

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$18)-ROW(D$13),3)=1),--(D$13:D$180),D$13:D$18*D$14:D$19/365),2)

Biff

"KeenKiwi" wrote in message
...
Thanks, Biff, but I'm not getting a sensible result either with or without
inserting a blank row. Sorry, but I don't understand the original or your
amended version well enough to figure out where it's going wrong.

With the contents of D13:D18 as:-

2447897.68
2547897.68
0.5
325101.03
-325101.03
0.7

The results should be -325101.03*(0.7/365) (<0 formula) and
2547897.68*(0.5/365) (0 formula).

The actual answers I'm getting are 0 and 16,798,088,054.91.

Any suggestions?

"Biff" wrote:

Hi!

I then tried inserting a blank row as row 1 or 13, so I can insert
dates.

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

the resulting calculations are dramatically wrong.

When you insert rows before the formula cells that changes (screws up!)
the
MOD( ) calculation.

Try this:

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500)-ROW(D$13),3)=0),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

Biff

"KeenKiwi" wrote in message
...
With assistance from one of the learned members, I used the following
formula
to add together the sum of each pair of adjacent columns (this
calculates
total daily interest for all accounts, where the balance and interest
are
unique per account)

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:$GH4/365),2)

and its close cousin "0".

I have had to make 2 changes to deal with:-

1) a requirement to show both the ledger and adjusted balances for each
account, although the calculation is only on the adjusted i.e. the
second,
balance

2) Excel's 256 column feature; my macro now returns the data in rows

Mainly by trial and error, I have got the following to work:-

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

To do this, I insert 12 rows at the top of my worksheet, and paste in a
series of formulae. The first instance of the above formula is in D5.
The
figures in D13:D15 represent ledger bal/adjusted bal/rate, repeated for
each
account down the page.

I then tried inserting a blank row as row 1 or 13, so I can insert
dates.
The cell references increment as expected, but the resulting
calculations
are
dramatically wrong. Try as I might, I can't figure out what I need to
do
to
get this to work, although I'm sure it's really obvious.

Assistance greatly appreciated.

SUMPRODUCT
 

Thanks again, Biff. Surprise, surprise, this time it worked fine! I must have
been doing something dumb before!

"Biff" wrote:

Based on your small sample:

-325101.03*(0.7/365) = -623.78
2547897.68*(0.5/365) = 3490.27

Using these formulas I get the above results and they're robust against row
insertions: (before the formula rows)

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$18)-ROW(D$13),3)=1),--(D$13:D$18<0),D$13:D$18*D$14:D$19/365),2)

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$18)-ROW(D$13),3)=1),--(D$13:D$180),D$13:D$18*D$14:D$19/365),2)

Biff

"KeenKiwi" wrote in message
...
Thanks, Biff, but I'm not getting a sensible result either with or without
inserting a blank row. Sorry, but I don't understand the original or your
amended version well enough to figure out where it's going wrong.

With the contents of D13:D18 as:-

2447897.68
2547897.68
0.5
325101.03
-325101.03
0.7

The results should be -325101.03*(0.7/365) (<0 formula) and
2547897.68*(0.5/365) (0 formula).

The actual answers I'm getting are 0 and 16,798,088,054.91.

Any suggestions?

"Biff" wrote:

Hi!

I then tried inserting a blank row as row 1 or 13, so I can insert
dates.

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

the resulting calculations are dramatically wrong.

When you insert rows before the formula cells that changes (screws up!)
the
MOD( ) calculation.

Try this:

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500)-ROW(D$13),3)=0),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

Biff

"KeenKiwi" wrote in message
...
With assistance from one of the learned members, I used the following
formula
to add together the sum of each pair of adjacent columns (this
calculates
total daily interest for all accounts, where the balance and interest
are
unique per account)

=ROUND(SUMPRODUCT(--(MOD(COLUMN($K4:$GG4),2)=1),--($K4:$GG4<0),$K4:$GG4*$L4:$GH4/365),2)

and its close cousin "0".

I have had to make 2 changes to deal with:-

1) a requirement to show both the ledger and adjusted balances for each
account, although the calculation is only on the adjusted i.e. the
second,
balance

2) Excel's 256 column feature; my macro now returns the data in rows

Mainly by trial and error, I have got the following to work:-

=ROUND(SUMPRODUCT(--(MOD(ROW(D$13:D$500),3)=2),--(D$13:D$500<0),D$13:D$500*D$14:D$501/365),2)

To do this, I insert 12 rows at the top of my worksheet, and paste in a
series of formulae. The first instance of the above formula is in D5.
The
figures in D13:D15 represent ledger bal/adjusted bal/rate, repeated for
each
account down the page.

I then tried inserting a blank row as row 1 or 13, so I can insert
dates.
The cell references increment as expected, but the resulting
calculations
are
dramatically wrong. Try as I might, I can't figure out what I need to
do
to
get this to work, although I'm sure it's really obvious.

Assistance greatly appreciated.

SUMPRODUCT
 

"KeenKiwi" wrote in message
...
Thanks again, Biff. Surprise, surprise, this time it worked fine! I must
have
been doing something dumb before!

Nothing you did.......I changed the MOD test from 0 to 1. The earlier
version was a row off.

Thanks for the feedback!

Biff