SUMPRODUCT

[Guest]

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$500),3)=2),--(D$13$500<0),D$13$500*D$14$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 D1315 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.

 

[Biff]

Hi!

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

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$500)-ROW(D$13),3)=0),--(D$13$500<0),D$13$500*D$14$501/365),2)

Biff

 

[Guest]

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 D1318 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]

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$18)-ROW(D$13),3)=1),--(D$13$18<0),D$13$18*D$14$19/365),2)

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

Biff

 

[Guest]

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

 

[Biff]

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