Weighted Forecast question

Jenn Schaal

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.

Brent Wilson

@Jenn Schaal

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..

Jenn Schaal

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?

Jenn Schaal

Brent Wilson

#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 Wilson

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 !!

Jenn Schaal

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.

Brent Wilson

@Jenn Schaal Ahh yes the magic computer.. it's working now I don't know why scenario. Awesome it is working !!