Planned Value Formula

Options

Hello,

I'm trying to make this formula work:

=IFERROR(([Actual End Date]100), "(TODAY() - [Planned Start Date]100) / ([Planned End Date]100 - [Planned Start Date]100) * [Issue Type]13)")

It stays blank in the cell after deploying it, which is not what I want. The Planned Start Date and Planned End Date columns don't change color when editing the formula, so something is not right. The output should be a dollar value, because we're multiplying that Today()... string by another cell that is a dollar value (i.e., [Issue Type]13). How do I get an actual value to output?

What makes it extra challenging is when [Actual End Date]100 has a value, which is a date cell, it gives me #INVALID COLUMN VALUE. If I put DATEONLY before it, it outputs literally whatever is in between "" in the original formula, and still gives me #INVALID COLUMN VALUE. If I put DATE before the [Actual End Date]100, it gives me #INCORRECT ARGUMENT SET.

Note, the cell that I am putting this formula in is in a Text/Number column.

Please help. Thanks!

Ronny

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Ronny Dorian

    Though I do not understand the logic of your formula (17, 38, 113), here is the formula fix;

    Your formula has an unnecessary closing parenthesis, ),  after [Issue Type]17 before TODAY().

    =IF(ISDATE([Actual End Date]113), ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) ), (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17)

    My formula

    =IF(ISDATE([Actual End Date]113), ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17, (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17)

    With the unnecessary closing parenthesis, the IF statement closes there, making the lack of false value and remaining formula cause errors.

    Your formula's structure.

    =IF(condition, true value ending * [Issue Type]17), remaining formula.

    Correct IF statement structure.

    =IF(condition, true, false)

    • condition:
      • ISDATE([Actual End Date]113)
    • True value:
      • ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17
    • False value:
      • (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17


    The published report has only rows 17, 38, and 113.


    For the published sheet, please scroll to see those rows.


Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @Ronny Dorian

    To show the Date value to the text/num column, add "", for example, [Date]@row+ "". 😀

    So, I would use a formula like the following;

    =IF(ISDATE([Actual End Date]@row), [Actual End Date]@row + "", (TODAY() - [Planned Start Date]@row) / ([Planned End Date]@row - [Planned Start Date]@row) * [Issue Type]@row)



  • Hollie205
    Hollie205 ✭✭✭
    Options

    I would recommend trying a helper column to get the number of days you are wanting to multiply the dollar amount by.

    Also based on the formula it appears if there is not an error you are wanting to multiply the dollar amount by the date in the [actual end date]100 cell which isn't possible. To better understand would need to understand what the possible inputs would be and what output you are looking to happen.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    I have changed the column type to "Issue Type" to symbol, dollar value.

    Unlike other symbols, the symbol's value is from 1 to 5, which I can use in a formula without any conversion.


  • Ronny Dorian
    Ronny Dorian ✭✭✭✭
    Options

    Thanks for your responses.

    So I incorporated the ISDATE logic, and modified the formula as such:


    =IF(ISDATE([Actual End Date]113), ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17), (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17)


    I omitted the + and "" because i was not getting color activation within the formula. I can't change the Issue Type Colum formatting to $ because I have non-dollar values in it. How can I improve the logic above as I am now getting #UNPARSEABLE ...? Recall that I would like it to output a dollar value, and [Issue Type]17 is a dollar value.

    Overall logic as I see it now: If there is a date value in Actual End Date 113, use that date in a formula (that would otherwise work on its own), if not, use today's date in that formula.

    Thanks,

    Ronny

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    @Ronny Dorian

    Can you share screenshots showing your set-up, blocking out sensitive data?

    I want to understand why you reference different rows 113, 38, and 17. I also want to know what you meant by a dollar value, the dollar symbol column, or currency.

  • Ronny Dorian
    Ronny Dorian ✭✭✭✭
    Options

    @jmyzk_cloudsmart_jp , please see attached for screenshots.

    Row 113 is now going to be 112 due to less rows.

    Thanks,

    Ronny

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Ronny Dorian

    Though I do not understand the logic of your formula (17, 38, 113), here is the formula fix;

    Your formula has an unnecessary closing parenthesis, ),  after [Issue Type]17 before TODAY().

    =IF(ISDATE([Actual End Date]113), ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) ), (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17)

    My formula

    =IF(ISDATE([Actual End Date]113), ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17, (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17)

    With the unnecessary closing parenthesis, the IF statement closes there, making the lack of false value and remaining formula cause errors.

    Your formula's structure.

    =IF(condition, true value ending * [Issue Type]17), remaining formula.

    Correct IF statement structure.

    =IF(condition, true, false)

    • condition:
      • ISDATE([Actual End Date]113)
    • True value:
      • ([Actual End Date]113 - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17
    • False value:
      • (TODAY() - [Planned Start Date]38) / ([Planned End Date]38 - [Planned Start Date]38) * [Issue Type]17


    The published report has only rows 17, 38, and 113.


    For the published sheet, please scroll to see those rows.


  • Ronny Dorian
    Ronny Dorian ✭✭✭✭
    Options

    jmyzk- you have gotten it! Thank you 😁

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!