Skip navigation

Text Rule Query

316 Views 9 Replies Latest reply: Jan 26, 2013 12:55 PM by Jerrold Green1 RSS
Schrodinger56 Level 1 Level 1 (30 points)
Currently Being Moderated
Jan 26, 2013 6:59 AM

Is it possible to create a rule that says that if a number in a cell is greater than 0 then the word 'In Credit' appears next to the number in the same cell and likewise if the number is less than 0 then 'In Debit' appears next to the number in the same cell.  Or do I have to create some fancy formula in the cell adjacent to the cell containg the number to either dispaly 'In credit' or 'In debit' depending on the value in the adjacent cell?

 

Thanks.

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 26, 2013 8:07 AM (in response to Schrodinger56)

    Schrodinger,

     

    Nothing "fancy" required. The big question is whether this value that you would like to test is entered directly or if it is the result of some calculation.

     

    If it's a direct entry, it's not possible to test it and add text in the same cell. Your added text could be in the adjacent column, and if you remove the cell border it might look enough like it is in the same column to please you.

     

    If calculated, it's a simple IF function in the same cell.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 26, 2013 8:34 AM (in response to Schrodinger56)

    In the cell with the calculation you would modify it in this way:

     

    =IF(YourCalculation <0, "In Debit", "In Credit")

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 26, 2013 8:58 AM (in response to Schrodinger56)

    Schrodinger,

     

    I made a mistake, forgetting that you would like to add the text to the calculation, not replace the calculation. So, do this:

     

    =YourCalculation & IF(YourCalculation <0, " In Debit", " In Credit")

     

    Sorry for the confusion.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jan 26, 2013 12:27 PM (in response to Schrodinger56)

    Hi Schodinger56,

     

    If the value displayed in this cell is an END result (ie. a result that will not be used in further calculations), then combining the result of the calculation with a text string as requested will work.

     

    But if the numerical part of the result is to be used in later calculations, the formulas for those calculations will need to be written to extract the part of the string (eg "75.05" from "75.05 in credit") representing the number before performing the calculations. Keeping the 'label' in a separate cell simplifies any formulas further downstream.

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jan 26, 2013 12:55 PM (in response to Schrodinger56)

    Schrodinger,

     

    It's not a difficult problem to recover the numeric calculation result. For instance, if the expression is in Column B, the value portion can be recovered by writing:

     

    =LEFT(B, FIND("In", B)-2)

     

    However, it might be easier just to repeat the expression that was used to calculate the value in column B.

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.