none
How to calculate a value for a column if it is blank and leave it alone if populated?

    Question

  • I am trying to set up a calculated column where some rows(records) may already be populated and have a value for that column.  So I need my field to be calculated only when that column of a row is blank and be left alone if it already has a value. There is an Excel function  called ISBLANK which doesn’t work in SharePoint. My formula for the column is:  =IF([DOB+19]<=[LDOV+7],[LDOV+7],[DOB+19]) where DOB and LDOV are names other columns. So I need a formula to look like (conceptually):   =IF ISBLNK [IF([DOB+19]<=[LDOV+7],[LDOV+7],[DOB+19]), else use the existing value of the cell. Any ideas on how to formulate this?

    Friday, November 06, 2015 7:57 PM

Answers

  • Hi

    as i said in my firrst answer, you should use like this

    [column]=""

    I recommend you do not use spaces in your column names. Later you can customize how it looks like; SO use [DestructionDue] instead of [Destruction Due]

    I reproduced your issue and it's working like a charm using this formula

    =IF(DestructionDue="";IF((DOB+19)<=(LDOV+7);LDOV+7;DOB+19);"")


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by eganji Monday, November 16, 2015 11:58 PM
    Saturday, November 14, 2015 8:45 AM

All replies

  • Hi

    try this formula in your calculated column

    =IF([Column1]="", "COLUMN 1 is empty ", "--nothing --")


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Monday, November 09, 2015 7:16 AM
  • Hi,

     

    Based on your description, I understand there is a column, I will refer it as column A, if column A has a value, you don’t want calculated column to do calculation, if column A has no value you want to calculate based on DOB and LDOV columns.

    You can try this formula for calculated column:

    =IF (ISBLANK(ColumnA),IF(DOB+19<=LDOV+7,LDOV+7,DOB+19),"")

    If column A has a value, calculated column won’t have a value, if column A is blank, calculated column  value will be based on DOB and LDOV columns.

     

    Thank you


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, November 09, 2015 7:20 AM
  • Hi,

    Any update?

    Thank you


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, November 13, 2015 9:08 AM
  • Hi, Yes I have an update and thank you for the input. I have set up the formula for the field as follows but still get an error message. I think I am doing something wrong with the syntax of the nested IF statements. 


    =IF(ISBLANK[Destruction Due],IF(([DOB+19]<=[LDOV+7]),[LDOV+7],[DOB+19]),"")


    When I use the inside IF statement as:  =IF([DOB+19]<=[LDOV+7],[LDOV+7],[DOB+19]) it does the calculation properly. But I need the formula to check [Destruction Due] and perform the second IF statement only IF the [Destruction Due] is blank. Otherwise, if [Destruction Due] is populated skip the calculation and leave the field blank.

    Thank you for the input and looking forward to a solution.

    Friday, November 13, 2015 10:51 PM
  • Hi

    as i said in my firrst answer, you should use like this

    [column]=""

    I recommend you do not use spaces in your column names. Later you can customize how it looks like; SO use [DestructionDue] instead of [Destruction Due]

    I reproduced your issue and it's working like a charm using this formula

    =IF(DestructionDue="";IF((DOB+19)<=(LDOV+7);LDOV+7;DOB+19);"")


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    • Marked as answer by eganji Monday, November 16, 2015 11:58 PM
    Saturday, November 14, 2015 8:45 AM
  • I tried and it worked. I had to make small adjustments to the punctuation but the overall formula worked just fine. Thank you for your big help.
    Monday, November 16, 2015 11:59 PM
  • Welcome ;)

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Tuesday, November 17, 2015 6:43 AM