Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
I need a formula for money "out" money "In". If the money goes "Out" on a
specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
Any help - Can this be done?
"heater" wrote: I need a formula for money "out" money "In". If the money goes "Out" on a specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
Money goes "Out" on Feb 20, Feb 21, and Feb 28 . The Money that goes "Out"
on Feb 21, comes back "In" on Feb 27, so that money is back before the money that goes "Out" on Feb 28, which reduces the total outstanding. The money that goes "Out" on Feb 20 is not due back "In" until Mar 21, so this total would still be a running total. The money that comes back "In" before money goes "Out" is the total that needs to be reduced. Just think of it as money paid "Out" in day 1 is paid back 7 days from now, and more money was paid "Out" in day 2 that is due back on day 4, so the money paid "Out" on day 1 & 2 would be a total, but when day 4 comes, day 2 money is reduced, so day 1 money is still outstanding. I know this is mind boggling, but I'm sure someone can figure this out, if you input the info in a spreadsheet maybe it would be more clear. You guys answer some pretty difficult stuff - take this as a challenge. Thanks for your help! "Sandy Mann" wrote: Perhaps like me people don't understand what it is that you are trying to do. Although having said that it never ceases to amaze me how the regulars are able to interpret exactly what posters are really asking. In your example you have an "Out" on Feb 20 and another "Out" on Feb 21 yet you only count the Feb 21 "Out" because it is higher up the sheet. Surely you should count in chronological order? Can you explain more fully what it is that you are doing then perhaps you may get an answer. -- HTH Sandy with @tiscali.co.uk "heater" wrote in message ... Any help - Can this be done? "heater" wrote: I need a formula for money "out" money "In". If the money goes "Out" on a specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
Hi
If the data is always money out negative, and money in positive, and column D does represent the date when money flows , then we don't really need column F. Also, because your dates are not in order, it might be easier to have a column of sequential dates (I used column S) with dates starting at S1 with 20/02/2006 running down to 31/03/2006 in S40 The formula in Cell R1 would then be =SUMPRODUCT(--($D$14:$D$19<=S1),$E$14:$E$19)*0.8 and copied down. This would give the daily balance for any date, and does return your value of 3,816,768 for 28 February. -- Regards Roger Govier "heater" wrote in message ... Money goes "Out" on Feb 20, Feb 21, and Feb 28 . The Money that goes "Out" on Feb 21, comes back "In" on Feb 27, so that money is back before the money that goes "Out" on Feb 28, which reduces the total outstanding. The money that goes "Out" on Feb 20 is not due back "In" until Mar 21, so this total would still be a running total. The money that comes back "In" before money goes "Out" is the total that needs to be reduced. Just think of it as money paid "Out" in day 1 is paid back 7 days from now, and more money was paid "Out" in day 2 that is due back on day 4, so the money paid "Out" on day 1 & 2 would be a total, but when day 4 comes, day 2 money is reduced, so day 1 money is still outstanding. I know this is mind boggling, but I'm sure someone can figure this out, if you input the info in a spreadsheet maybe it would be more clear. You guys answer some pretty difficult stuff - take this as a challenge. Thanks for your help! "Sandy Mann" wrote: Perhaps like me people don't understand what it is that you are trying to do. Although having said that it never ceases to amaze me how the regulars are able to interpret exactly what posters are really asking. In your example you have an "Out" on Feb 20 and another "Out" on Feb 21 yet you only count the Feb 21 "Out" because it is higher up the sheet. Surely you should count in chronological order? Can you explain more fully what it is that you are doing then perhaps you may get an answer. -- HTH Sandy with @tiscali.co.uk "heater" wrote in message ... Any help - Can this be done? "heater" wrote: I need a formula for money "out" money "In". If the money goes "Out" on a specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
Your formula works (thank you); however, I had to input a formula in column S
to make the spreadsheet work for our purposes. Issue: I have the following formula in column S =IF(AND(B10="Out",D10<=F10),D10,IF(B10="In",D10<=F 10,"")), when B10 ="Out", the formula works fine, when B10="In", excel returns "TRUE" in the cell. If B10="In", I do not want anything to return in the cell. I want the result to be blank. I assume I need some quotes somewhere! "Roger Govier" wrote: Hi If the data is always money out negative, and money in positive, and column D does represent the date when money flows , then we don't really need column F. Also, because your dates are not in order, it might be easier to have a column of sequential dates (I used column S) with dates starting at S1 with 20/02/2006 running down to 31/03/2006 in S40 The formula in Cell R1 would then be =SUMPRODUCT(--($D$14:$D$19<=S1),$E$14:$E$19)*0.8 and copied down. This would give the daily balance for any date, and does return your value of 3,816,768 for 28 February. -- Regards Roger Govier "heater" wrote in message ... Money goes "Out" on Feb 20, Feb 21, and Feb 28 . The Money that goes "Out" on Feb 21, comes back "In" on Feb 27, so that money is back before the money that goes "Out" on Feb 28, which reduces the total outstanding. The money that goes "Out" on Feb 20 is not due back "In" until Mar 21, so this total would still be a running total. The money that comes back "In" before money goes "Out" is the total that needs to be reduced. Just think of it as money paid "Out" in day 1 is paid back 7 days from now, and more money was paid "Out" in day 2 that is due back on day 4, so the money paid "Out" on day 1 & 2 would be a total, but when day 4 comes, day 2 money is reduced, so day 1 money is still outstanding. I know this is mind boggling, but I'm sure someone can figure this out, if you input the info in a spreadsheet maybe it would be more clear. You guys answer some pretty difficult stuff - take this as a challenge. Thanks for your help! "Sandy Mann" wrote: Perhaps like me people don't understand what it is that you are trying to do. Although having said that it never ceases to amaze me how the regulars are able to interpret exactly what posters are really asking. In your example you have an "Out" on Feb 20 and another "Out" on Feb 21 yet you only count the Feb 21 "Out" because it is higher up the sheet. Surely you should count in chronological order? Can you explain more fully what it is that you are doing then perhaps you may get an answer. -- HTH Sandy with @tiscali.co.uk "heater" wrote in message ... Any help - Can this be done? "heater" wrote: I need a formula for money "out" money "In". If the money goes "Out" on a specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Money "In" Money "Out" Formula
For those of you who care, I figured it out:
=IF(AND(B10="Out",D10<=F10),D10,IF(B10="In","","") ) "heater" wrote: Your formula works (thank you); however, I had to input a formula in column S to make the spreadsheet work for our purposes. Issue: I have the following formula in column S =IF(AND(B10="Out",D10<=F10),D10,IF(B10="In",D10<=F 10,"")), when B10 ="Out", the formula works fine, when B10="In", excel returns "TRUE" in the cell. If B10="In", I do not want anything to return in the cell. I want the result to be blank. I assume I need some quotes somewhere! "Roger Govier" wrote: Hi If the data is always money out negative, and money in positive, and column D does represent the date when money flows , then we don't really need column F. Also, because your dates are not in order, it might be easier to have a column of sequential dates (I used column S) with dates starting at S1 with 20/02/2006 running down to 31/03/2006 in S40 The formula in Cell R1 would then be =SUMPRODUCT(--($D$14:$D$19<=S1),$E$14:$E$19)*0.8 and copied down. This would give the daily balance for any date, and does return your value of 3,816,768 for 28 February. -- Regards Roger Govier "heater" wrote in message ... Money goes "Out" on Feb 20, Feb 21, and Feb 28 . The Money that goes "Out" on Feb 21, comes back "In" on Feb 27, so that money is back before the money that goes "Out" on Feb 28, which reduces the total outstanding. The money that goes "Out" on Feb 20 is not due back "In" until Mar 21, so this total would still be a running total. The money that comes back "In" before money goes "Out" is the total that needs to be reduced. Just think of it as money paid "Out" in day 1 is paid back 7 days from now, and more money was paid "Out" in day 2 that is due back on day 4, so the money paid "Out" on day 1 & 2 would be a total, but when day 4 comes, day 2 money is reduced, so day 1 money is still outstanding. I know this is mind boggling, but I'm sure someone can figure this out, if you input the info in a spreadsheet maybe it would be more clear. You guys answer some pretty difficult stuff - take this as a challenge. Thanks for your help! "Sandy Mann" wrote: Perhaps like me people don't understand what it is that you are trying to do. Although having said that it never ceases to amaze me how the regulars are able to interpret exactly what posters are really asking. In your example you have an "Out" on Feb 20 and another "Out" on Feb 21 yet you only count the Feb 21 "Out" because it is higher up the sheet. Surely you should count in chronological order? Can you explain more fully what it is that you are doing then perhaps you may get an answer. -- HTH Sandy with @tiscali.co.uk "heater" wrote in message ... Any help - Can this be done? "heater" wrote: I need a formula for money "out" money "In". If the money goes "Out" on a specified date and comes "In" at a later date, then it will keep a running total of how much money is "Out". Ex: B14="Out", D14(Feb 21, 06) E14(-7,650,286) F14(Feb 27,06) B15="In", D15(Feb 27, 06) E15(7,650,286) F15(Feb 27,06) B16 ="Out", D16(Feb 20, 06) E16(-2,463,804) F16(Mar 21,06) B17="In", D17(Mar 21, 06) E17(2,463,804) F17(Mar 21,06) B18="Out, D18(Feb 28, 06) E18(-2,307,156) F18(Mar 21,06) B19="In, D19(Mar 21, 06) E19(2,307,156) F19(Mar 21,06) I have this formula (D14<F14,(E14*0.8),0) in 014, which equals -6,120,229. Cell 016=-1,971,043 and cell 018=-1,845,725 - Cell R14:R19 keeps the total. So, with the formula I have it just keeps a running total - cell R14 is -6,120,229, R16 is -8,091,272 and R18 is -9,936,997. I need a formula that will reduce the total in Column R once the money is returned. So, for example after Feb 27, the total in Cell R18 would be 3,816,768 (6,120,229-9,936,997). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |