Skip to Content
avatar image
Former Member

Displaying Positive/Negative numbers from a formula

This question has been asked/answered in the link below.

However, I am trying to adding to it the following addittion:

IF ZERO THEN OK

How can I get this result?

https://archive.sap.com/discussions/message/6494472#6494472

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

10 Answers

  • Best Answer
    Mar 10, 2017 at 11:00 AM

    Hi Rony,

    Please try this code:

    local numbervar a := {field1} - {field2};
    if a > 0 then
    "+" & totext(a,'#') 
    else if a < 0 then
    "-" & totext(a,'#')
    else
    'OK'

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 10, 2017 at 11:22 AM

    Thank you.

    However, Ok is not showhing where results are zero, just a blank field.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 10, 2017 at 12:17 PM

    Are there Null values?

    The formula works fine for me, though.

    Open the formula and look for an option on the top that says 'Exceptions for Nulls'; change it to 'Default Values for Nulls'.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 10, 2017 at 12:54 PM

    I changed to

    'Default Values for Nulls'

    and there was not alteration in the result.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 10, 2017 at 01:23 PM

    Could you post the numbers in field 1 and field 2 please?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 10, 2017 at 01:50 PM

    You can get the Excel spreadsheet in the link below.
    The data of both tabs are pulled into the report.
    Note that in the Bond Data tab some codes repeat more that once, whereas in the Quickbooks data the same codes only appear once with the summary qty.

    https://www.dropbox.com/s/9981gfi651a7e4w/28FEB17%20Stock%20Valuation%20from%20Bond%20and%20U76_S.xlsb?dl=0

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 10, 2017 at 06:38 PM

    Hi,

    I took the liberty of downloading the spreadsheet to have a look.

    How are you linking the worksheets? The only link I could use it Stock Level.

    What numbers are you trying to evaluate? I'm assuming Stock Level.

    Brian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 13, 2017 at 08:41 AM

    Hi Brian

    Yes, trying to evaluate stock level, but the report is grouped by bond short code, then subgrouped by qb short code.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 13, 2017 at 03:09 PM

    Abhilash

    Your solution was right all along.

    Somehow I had formated the field to supress if 0, hence the consistent mistake.

    Sorry about that, and thank you again.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 30, 2018 at 07:10 PM

    The formulae are working fine in my Quickbooks Support software for accounting thanks for the formulae

    Add comment
    10|10000 characters needed characters exceeded