Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |