Weighted Forecast question
I have read everyone's questions in here on this topic and yet I still cannot get this to work.
I want Forecasted Amount (Total) to multiply by Probabillity to get Forecasted Amount. Every time I try a formula, I either get UNPARSEABLE.
I'm still very new to smartsheet, so I know I have a lot to learn.
Answers
-
Weighted Forcast should be very simple
= [Forecast Amount (Total)]@row*[Probability]@row
If you are new to Smartsheet remember any field with spaces will need to be in square brackets. [Probability] doesn't need it but it is good practice.
Also, the @row can be added for a more generic formula rather then [Forecast Amount (Total)]@3*[Probability]@3
The issues could also be the column types..
If for example, you typed "$20,000" in Forecast Amount rather than "20000" and then formatted the column in currency format to display the $20,000 then Smartsheet would see it as text.
The same would go for Probability... "50%" vs ".5" and format to a percent
If Smartsheet thinks they are text it cannot do the math on it
Also, it has messed me up before where I have spent time figuring out why my formula won't work only to find I made a weighted forecast a date field or something stupid.
Let me know if that helps..
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Hi @Brent Wilson -
Thanks - I removed the currency and percent formatting and double checked they were text/numbers, then retyped them as such. I tried your formula - I am still getting UNPARSEABLE.
The formula I used was =[Forecast Amount Total]@row*[Probability]@row
I did remove the parenthesis from Forecast Amount (Total) on the sheet as it seemed to be causing issues in formulas as well.
Any ideas?
-
#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.
Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
My guess it is something simple in the formula. I sometimes go right back to basics and create two columns and simply calculate it right on the row and sneak up on the issue.
Get it working on the datasheet and then try working on it in the summary
I guarantee you it is something simple and silly and ANNOYING !!
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Thanks @Brent Wilson - I've got the formula working in the data sheet but not on the metrics sheet. So, I just updated it to a column formula once I got it working and that seems to fix it.
-
@Jenn Schaal Ahh yes the magic computer.. it's working now I don't know why scenario. Awesome it is working !!
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!