Money "In" Money "Out" Formula

[heater]

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).

[heater]

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).
>
>

[Sandy Mann]

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
[email protected]
[email protected] with @tiscali.co.uk

"heater" <[email protected]> wrote in message
news:[email protected]...
> 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).
>>
>>

[heater]

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
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "heater" <[email protected]> wrote in message
> news:[email protected]...
> > 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).
> >>
> >>
>
>
>

[Roger Govier]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>> "heater" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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).
>> >>
>> >>
>>
>>
>>

[heater]

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<=F10,"")), 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >> "heater" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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).
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[heater]

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<=F10,"")), 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >> [email protected]
> > >> [email protected] with @tiscali.co.uk
> > >>
> > >> "heater" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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).
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >