3 Replies Latest reply: May 15, 2014 9:59 AM by Mike Urbonas RSS

    Entering blanks in if statement

    Peterl _

      I am a novice and frustrated by a simple formuala

      Entering if <0,"",[column7] I get the error "operand type". What should I enter to simply get a blank?

        • Entering blanks in if statement

          If your column is numeric, then the problem is you are trying to assign a string to numeric field.  If it is a string, then you are comparing it to zero.  Whichever is the case, there's a problem.

          • Entering blanks in if statement
            Nick Osdale-Popa

            I would assume that you are putting the formula in a numeric field and trying to return a blank string, thus the reason for the "operand types" error. I would change the formula as thus:


            [font="courier"]IF( < 0, 1/0, )[/font][/quote]This will return a Null value to your calculated field, essentially a blank.  You can deal with how Nulls are displayed in the Options|View menu.  However, be careful with Nulls as I've been told they may affect some calculations (though this may only relate to database joins).

            • Entering blanks in if statement
              Mike Urbonas

              The "Operand values" error indicates you can't mix and match two different data types within one column (that's typical for any application working with data).


              I don't think using calc fields which may yield a NULL value is the way to go.  I recommend in general to avoid NULLs all together.  For example, the presence of NULLs will cause a summary view to display blank or incorrect aggregated values.


              You could just just enter your formula as:  IF(<0,0,[column7]) and then from Monarch's menu bar, click on: Options -> View... and check the box "Suppress Zero Values" -- doing so will display zero values in Monarch as blank.


              However, if you then export your data to, say, Excel, the zeroes will re-appear because the data will now comply with whatever Excel setting you have.  To set up Excel to not display zeroes either, click on Tools -> Options... and uncheck the Zero Values box within the View tab. 


              And to make Excel use this setting each time you open Excel, I believe you would then save as a template.  I suggest you go to Excel Help and search for "Create a sheet template for new worksheets" or "About controlling how Excel starts" for further instructions.  I hope this is helpful.