+ Reply to Thread
Results 1 to 6 of 6

Help with Strange Formula

  1. #1
    Registered User
    Join Date
    07-03-2006
    Posts
    10

    Help with Strange Formula

    Hi Guys,

    I am trying to make a budget spreadsheet and want to get fancy with it. I have a macro that converts a figure when i have paid it to add some brackets around it ie < 10.00 >

    Now I want to only add up the fiqures that arn't in these brackets.

    Any ideas?

    e.g.

    10.00
    10.00
    <10.00>
    <20.00>
    10.00

  2. #2
    Ardus Petus
    Guest

    Re: Help with Strange Formula

    SUM function will ignore text values, so you can write:
    =SUM(A1:A5)
    which will produce 30.00

    HTH
    --
    AP

    "scr5jo" <[email protected]> a écrit dans
    le message de news: [email protected]...
    >
    > Hi Guys,
    >
    > I am trying to make a budget spreadsheet and want to get fancy with it.
    > I have a macro that converts a figure when i have paid it to add some
    > brackets around it ie < 10.00 >
    >
    > Now I want to only add up the fiqures that arn't in these brackets.
    >
    > Any ideas?
    >
    > e.g.
    >
    > 10.00
    > 10.00
    > <10.00>
    > <20.00>
    > 10.00
    >
    >
    > --
    > scr5jo
    > ------------------------------------------------------------------------
    > scr5jo's Profile:
    > http://www.excelforum.com/member.php...o&userid=35990
    > View this thread: http://www.excelforum.com/showthread...hreadid=557771
    >




  3. #3
    DL
    Guest

    Re: Help with Strange Formula


    It is the wrong way (imho). You need to add another field near to your
    amounts as (for example): paid or Payment status etc. and fill it up
    with 1 or 0 or with some check box (paid / nope). It will be the correct
    way of maintaining this small DB.

    D.-


    "scr5jo" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi Guys,
    >
    > I am trying to make a budget spreadsheet and want to get fancy with

    it.
    > I have a macro that converts a figure when i have paid it to add some
    > brackets around it ie < 10.00 >
    >
    > Now I want to only add up the fiqures that arn't in these brackets.
    >
    > Any ideas?
    >
    > e.g.
    >
    > 10.00
    > 10.00
    > <10.00>
    > <20.00>
    > 10.00
    >
    >
    > --
    > scr5jo
    > ----------------------------------------------------------------------

    --
    > scr5jo's Profile:

    http://www.excelforum.com/member.php...o&userid=35990
    > View this thread:

    http://www.excelforum.com/showthread...hreadid=557771
    >




  4. #4
    Registered User
    Join Date
    07-03-2006
    Posts
    10
    Hi Ardus,

    it's not a text value, it's just the way I formatted the cell. If I run my macro again, it will convert it back to normal. Here is the marco so you can see :

    Sub Show_Paid()
    If ActiveCell.NumberFormat = "#,##0.00" Then
    Selection.NumberFormat = "< #,##0.00 >"
    ElseIf ActiveCell.NumberFormat = "< #,##0.00 >" Then
    ActiveCell.NumberFormat = "#,##0.00"
    End If
    End Sub


    DL : I tried a second column but it's messy - I have my columns as Jan, Feb etc with carry forward balances and so to work over 2 columns is a pain.

    What I am hopeing to get is a nested SUB (IF) statement that can identify the formula type and then not add up the <> ones. I am struggeling to find a format command in Excel though( plenty in VBA)

    thanks
    Steve

  5. #5
    Bob Phillips
    Guest

    Re: Help with Strange Formula

    and =SUMIF(A:A,"<0") for those in the brackets

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "scr5jo" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi Ardus,
    >
    > it's not a text value, it's just the way I formatted the cell. If I run
    > my macro again, it will convert it back to normal. Here is the marco so
    > you can see :
    >
    > Sub Show_Paid()
    > If ActiveCell.NumberFormat = "#,##0.00" Then
    > Selection.NumberFormat = "< #,##0.00 >"
    > ElseIf ActiveCell.NumberFormat = "< #,##0.00 >" Then
    > ActiveCell.NumberFormat = "#,##0.00"
    > End If
    > End Sub
    >
    >
    > DL : I tried a second column but it's messy - I have my columns as
    > Jan, Feb etc with carry forward balances and so to work over 2 columns
    > is a pain.
    >
    > What I am hopeing to get is a nested SUB (IF) statement that can
    > identify the formula type and then not add up the <> ones. I am
    > struggeling to find a format command in Excel though( plenty in VBA)
    >
    > thanks
    > Steve
    >
    >
    > --
    > scr5jo
    > ------------------------------------------------------------------------
    > scr5jo's Profile:

    http://www.excelforum.com/member.php...o&userid=35990
    > View this thread: http://www.excelforum.com/showthread...hreadid=557771
    >




  6. #6
    Bob Phillips
    Guest

    Re: Help with Strange Formula

    =SUMIF(A:A,">0")

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "scr5jo" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi Ardus,
    >
    > it's not a text value, it's just the way I formatted the cell. If I run
    > my macro again, it will convert it back to normal. Here is the marco so
    > you can see :
    >
    > Sub Show_Paid()
    > If ActiveCell.NumberFormat = "#,##0.00" Then
    > Selection.NumberFormat = "< #,##0.00 >"
    > ElseIf ActiveCell.NumberFormat = "< #,##0.00 >" Then
    > ActiveCell.NumberFormat = "#,##0.00"
    > End If
    > End Sub
    >
    >
    > DL : I tried a second column but it's messy - I have my columns as
    > Jan, Feb etc with carry forward balances and so to work over 2 columns
    > is a pain.
    >
    > What I am hopeing to get is a nested SUB (IF) statement that can
    > identify the formula type and then not add up the <> ones. I am
    > struggeling to find a format command in Excel though( plenty in VBA)
    >
    > thanks
    > Steve
    >
    >
    > --
    > scr5jo
    > ------------------------------------------------------------------------
    > scr5jo's Profile:

    http://www.excelforum.com/member.php...o&userid=35990
    > View this thread: http://www.excelforum.com/showthread...hreadid=557771
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1