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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
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 |