Need help with a formula

[Horst]

Can someone give me the right formula plase
IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Toppers]

=IF(F14+G14=123,H13+F14-G14,0) where <value>=123

"Horst" wrote:

> Can someone give me the right formula plase
> IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Toppers]

...... or do you mean...

=if(OR(F14=value,G14=Value),H3+F13-G14),0)

i.e if F14 OR G14 Value?

If both F14 AND G14 = Value then change OR to AND

=if(AND(F14=value,G14=Value),H3+F13-G14),0)


HTH

"Horst" wrote:

> Can someone give me the right formula plase
> IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Carim]

Hi Horst,

Difficult to guess ...
could be
=IF(F14=G14,H13+F14-G14,0)

HTH
Carim

[Horst]

That didn't work. I'm trying to create a running ledger but I don't want it
to automaticaly to configure unless the next entry has been posted.

"Toppers" wrote:

> ..... or do you mean...
>
> =if(OR(F14=value,G14=Value),H3+F13-G14),0)
>
> i.e if F14 OR G14 Value?
>
> If both F14 AND G14 = Value then change OR to AND
>
> =if(AND(F14=value,G14=Value),H3+F13-G14),0)
>
>
> HTH
>
> "Horst" wrote:
>
> > Can someone give me the right formula plase
> > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Toppers]

Please explain what you want to happen .. just giving a formula isn't
sufficient.

"Horst" wrote:

> That didn't work. I'm trying to create a running ledger but I don't want it
> to automaticaly to configure unless the next entry has been posted.
>
> "Toppers" wrote:
>
> > ..... or do you mean...
> >
> > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> >
> > i.e if F14 OR G14 Value?
> >
> > If both F14 AND G14 = Value then change OR to AND
> >
> > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> >
> >
> > HTH
> >
> > "Horst" wrote:
> >
> > > Can someone give me the right formula plase
> > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Horst]

I like to start with a balance in the row above. If I have a debit or credit
on the next row, I would like to calculate the balance cell on that row to
give me the new total. If there is no entry, I would like the balance cell on
that row to show 0 or stay blank. This should be like a regular check
register.

"Toppers" wrote:

> Please explain what you want to happen .. just giving a formula isn't
> sufficient.
>
> "Horst" wrote:
>
> > That didn't work. I'm trying to create a running ledger but I don't want it
> > to automaticaly to configure unless the next entry has been posted.
> >
> > "Toppers" wrote:
> >
> > > ..... or do you mean...
> > >
> > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> > >
> > > i.e if F14 OR G14 Value?
> > >
> > > If both F14 AND G14 = Value then change OR to AND
> > >
> > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> > >
> > >
> > > HTH
> > >
> > > "Horst" wrote:
> > >
> > > > Can someone give me the right formula plase
> > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Carim]

Hi,

If I may Toppers is right ... a clear explanation would help ...

=if(AND(F14<>0,G14<>0),H3+F13-G14),0)

This formula would perform the calculation only if both F14 and G14 are different from 0 ...

HTH
Cheers
Carim

[Ragdyer]

Try this:

=IF(AND(F14,G14),H13+F14-G14,0)

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Horst" <[email protected]> wrote in message
news:[email protected]...
> That didn't work. I'm trying to create a running ledger but I don't want
it
> to automaticaly to configure unless the next entry has been posted.
>
> "Toppers" wrote:
>
> > ..... or do you mean...
> >
> > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> >
> > i.e if F14 OR G14 Value?
> >
> > If both F14 AND G14 = Value then change OR to AND
> >
> > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> >
> >
> > HTH
> >
> > "Horst" wrote:
> >
> > > Can someone give me the right formula plase
> > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[David Biddulph]

"Horst" <[email protected]> wrote in message
news:[email protected]...

> "Toppers" wrote:

>> "Horst" wrote:

>> > "Toppers" wrote:

>> > > "Horst" wrote:
>> > >
>> > > > Can someone give me the right formula plase
>> > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

>> >
>> > > ..... or do you mean...
>> > >
>> > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
>> > >
>> > > i.e if F14 OR G14 Value?
>> > >
>> > > If both F14 AND G14 = Value then change OR to AND
>> > >
>> > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)

>> > That didn't work. I'm trying to create a running ledger but I don't
>> > want it
>> > to automaticaly to configure unless the next entry has been posted.

>> Please explain what you want to happen .. just giving a formula isn't
>> sufficient.

>I like to start with a balance in the row above. If I have a debit or
>credit
> on the next row, I would like to calculate the balance cell on that row to
> give me the new total. If there is no entry, I would like the balance cell
> on
> that row to show 0 or stay blank. This should be like a regular check
> register.

In which case:
=IF(AND(ISBLANK(F14),ISBLANK(G14)),"",H13+F14-G14)
--
David Biddulph

[Toppers]

Try this:

=IF(OR(F3>0,G3>0),H2+F3-G3,0) and copy down


HOWEVER, I would have thought you would want the balance on every line; if
so then
in balance column

=H2+F3-G3 and copy down

F G H
Credit Debit Balance
100
100 200
0 <==== Should this not be 200?
50 -50 <== and this 150?

HTH

"Horst" wrote:

> I like to start with a balance in the row above. If I have a debit or credit
> on the next row, I would like to calculate the balance cell on that row to
> give me the new total. If there is no entry, I would like the balance cell on
> that row to show 0 or stay blank. This should be like a regular check
> register.
>
> "Toppers" wrote:
>
> > Please explain what you want to happen .. just giving a formula isn't
> > sufficient.
> >
> > "Horst" wrote:
> >
> > > That didn't work. I'm trying to create a running ledger but I don't want it
> > > to automaticaly to configure unless the next entry has been posted.
> > >
> > > "Toppers" wrote:
> > >
> > > > ..... or do you mean...
> > > >
> > > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> > > >
> > > > i.e if F14 OR G14 Value?
> > > >
> > > > If both F14 AND G14 = Value then change OR to AND
> > > >
> > > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> > > >
> > > >
> > > > HTH
> > > >
> > > > "Horst" wrote:
> > > >
> > > > > Can someone give me the right formula plase
> > > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Horst]

That didn't work. This formula enters the latest total all the way down the
balance of the page.

"Ragdyer" wrote:

> Try this:
>
> =IF(AND(F14,G14),H13+F14-G14,0)
>
> And copy down as needed.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Horst" <[email protected]> wrote in message
> news:[email protected]...
> > That didn't work. I'm trying to create a running ledger but I don't want
> it
> > to automaticaly to configure unless the next entry has been posted.
> >
> > "Toppers" wrote:
> >
> > > ..... or do you mean...
> > >
> > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> > >
> > > i.e if F14 OR G14 Value?
> > >
> > > If both F14 AND G14 = Value then change OR to AND
> > >
> > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> > >
> > >
> > > HTH
> > >
> > > "Horst" wrote:
> > >
> > > > Can someone give me the right formula plase
> > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)
>
>

[Horst]

This still fills in the balance of the column.

"David Biddulph" wrote:

> "Horst" <[email protected]> wrote in message
> news:[email protected]...
>
> > "Toppers" wrote:
>
> >> "Horst" wrote:
>
> >> > "Toppers" wrote:
>
> >> > > "Horst" wrote:
> >> > >
> >> > > > Can someone give me the right formula plase
> >> > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)
>
> >> >
> >> > > ..... or do you mean...
> >> > >
> >> > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> >> > >
> >> > > i.e if F14 OR G14 Value?
> >> > >
> >> > > If both F14 AND G14 = Value then change OR to AND
> >> > >
> >> > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
>
> >> > That didn't work. I'm trying to create a running ledger but I don't
> >> > want it
> >> > to automaticaly to configure unless the next entry has been posted.
>
> >> Please explain what you want to happen .. just giving a formula isn't
> >> sufficient.
>
> >I like to start with a balance in the row above. If I have a debit or
> >credit
> > on the next row, I would like to calculate the balance cell on that row to
> > give me the new total. If there is no entry, I would like the balance cell
> > on
> > that row to show 0 or stay blank. This should be like a regular check
> > register.
>
> In which case:
> =IF(AND(ISBLANK(F14),ISBLANK(G14)),"",H13+F14-G14)
> --
> David Biddulph
>
>
>

[Horst]

Great job. That formula worked. Thank you very much.

"Toppers" wrote:

> Try this:
>
> =IF(OR(F3>0,G3>0),H2+F3-G3,0) and copy down
>
>
> HOWEVER, I would have thought you would want the balance on every line; if
> so then
> in balance column
>
> =H2+F3-G3 and copy down
>
> F G H
> Credit Debit Balance
> 100
> 100 200
> 0 <==== Should this not be 200?
> 50 -50 <== and this 150?
>
> HTH
>
> "Horst" wrote:
>
> > I like to start with a balance in the row above. If I have a debit or credit
> > on the next row, I would like to calculate the balance cell on that row to
> > give me the new total. If there is no entry, I would like the balance cell on
> > that row to show 0 or stay blank. This should be like a regular check
> > register.
> >
> > "Toppers" wrote:
> >
> > > Please explain what you want to happen .. just giving a formula isn't
> > > sufficient.
> > >
> > > "Horst" wrote:
> > >
> > > > That didn't work. I'm trying to create a running ledger but I don't want it
> > > > to automaticaly to configure unless the next entry has been posted.
> > > >
> > > > "Toppers" wrote:
> > > >
> > > > > ..... or do you mean...
> > > > >
> > > > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> > > > >
> > > > > i.e if F14 OR G14 Value?
> > > > >
> > > > > If both F14 AND G14 = Value then change OR to AND
> > > > >
> > > > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> > > > >
> > > > >
> > > > > HTH
> > > > >
> > > > > "Horst" wrote:
> > > > >
> > > > > > Can someone give me the right formula plase
> > > > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)

[Horst]

I couldn't get this formula to work

"Carim" wrote:

>
> Hi,
>
> If I may Toppers is right ... a clear explanation would help ...
>
> =if(AND(F14<>0,G14<>0),H3+F13-G14),0)
>
> This formula would perform the calculation only if both F14 and G14 are
> different from 0 ...
>
> HTH
> Cheers
> Carim
>
>
> --
> Carim
> ------------------------------------------------------------------------
> Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
> View this thread: http://www.excelforum.com/showthread...hreadid=531317
>
>

[Ragdyer]

Then try this:

=IF(OR(F14<>"",G14<>""),INDEX($H$3:H13,MATCH(MAX($H$3:H13)+1,$H$3:H13))+F14-
G14,"")

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Horst" <[email protected]> wrote in message
news:[email protected]...
> That didn't work. This formula enters the latest total all the way down
the
> balance of the page.
>
> "Ragdyer" wrote:
>
> > Try this:
> >
> > =IF(AND(F14,G14),H13+F14-G14,0)
> >
> > And copy down as needed.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Horst" <[email protected]> wrote in message
> > news:[email protected]...
> > > That didn't work. I'm trying to create a running ledger but I don't
want
> > it
> > > to automaticaly to configure unless the next entry has been posted.
> > >
> > > "Toppers" wrote:
> > >
> > > > ..... or do you mean...
> > > >
> > > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> > > >
> > > > i.e if F14 OR G14 Value?
> > > >
> > > > If both F14 AND G14 = Value then change OR to AND
> > > >
> > > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
> > > >
> > > >
> > > > HTH
> > > >
> > > > "Horst" wrote:
> > > >
> > > > > Can someone give me the right formula plase
> > > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)
> >
> >

[Horst]

David Biddulph

I don't know if you received my reply but your formula worked perfect for
what I needed. Great job, I appreciated your help.

Horst

"David Biddulph" wrote:

> "Horst" <[email protected]> wrote in message
> news:[email protected]...
>
> > "Toppers" wrote:
>
> >> "Horst" wrote:
>
> >> > "Toppers" wrote:
>
> >> > > "Horst" wrote:
> >> > >
> >> > > > Can someone give me the right formula plase
> >> > > > IF(F14:G14=VALUE,SUM(H13+F14-G14),0)
>
> >> >
> >> > > ..... or do you mean...
> >> > >
> >> > > =if(OR(F14=value,G14=Value),H3+F13-G14),0)
> >> > >
> >> > > i.e if F14 OR G14 Value?
> >> > >
> >> > > If both F14 AND G14 = Value then change OR to AND
> >> > >
> >> > > =if(AND(F14=value,G14=Value),H3+F13-G14),0)
>
> >> > That didn't work. I'm trying to create a running ledger but I don't
> >> > want it
> >> > to automaticaly to configure unless the next entry has been posted.
>
> >> Please explain what you want to happen .. just giving a formula isn't
> >> sufficient.
>
> >I like to start with a balance in the row above. If I have a debit or
> >credit
> > on the next row, I would like to calculate the balance cell on that row to
> > give me the new total. If there is no entry, I would like the balance cell
> > on
> > that row to show 0 or stay blank. This should be like a regular check
> > register.
>
> In which case:
> =IF(AND(ISBLANK(F14),ISBLANK(G14)),"",H13+F14-G14)
> --
> David Biddulph
>
>
>