Planned Value Formula

Ronny Dorian

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

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=adf1094e8601471a9792384df51a0db6

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

https://app.smartsheet.com/b/publish?EQBCT=0f3b9c8e34d74fe9a4a252c936447701

jmyzk_cloudsmart_jp

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)

https://app.smartsheet.com/b/publish?EQBCT=0f3b9c8e34d74fe9a4a252c936447701

Hollie205

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

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

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

@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

@jmyzk_cloudsmart_jp , please see attached for screenshots.

row17.png

row38.png

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

row112.png

Thanks,

Ronny

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=adf1094e8601471a9792384df51a0db6

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

https://app.smartsheet.com/b/publish?EQBCT=0f3b9c8e34d74fe9a4a252c936447701

Ronny Dorian

jmyzk- you have gotten it! Thank you 😁

jmyzk_cloudsmart_jp

@Ronny Dorian

Happy to help!😁