Planned Value Formula
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
-
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.
- condition:
Answers
-
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)
-
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.
-
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.
-
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
-
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.
-
@jmyzk_cloudsmart_jp , please see attached for screenshots.
Row 113 is now going to be 112 due to less rows.
Thanks,
Ronny
-
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.
- condition:
-
jmyzk- you have gotten it! Thank you 😁
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
