Need help

clewis_10990

Hi, I have another question. I am trying to create a formula that will take the value of a contract and apply a percentage of completion to any contract that has been won. So, I need to have a formula that looks at the sales stage and chooses those we have "Closed Won" and multiplies the percentage of completion by the forecast amount. Thoughts?

KPH

And what do you want on that metrics sheet? Just one cell with the total for all the rows in the other sheet?

If that is the case:

Put the new column into the first sheet with the formula to calculate Percent Complete * Forecast Amount on each row if the sales stage is won

=IF([Sales Stage]@row="Closed Won",[Percent Complete]@row*[Forecast Amount]@row)

On your metrics sheet you just need to sum that column

=SUM({new column})

KPH

Start with an IF

IF([Sales Stage]="Closed Won"

then put the math in for the value if true

=IF([Sales Stage]@row="Closed Won",[Percent Complete]@row*[Forecast Amount]@row)

KPH

There is something going on with the community today. The @row) at the end keeps vanishing as I post and when it does display it is not in bold. When I edit that message the new text I write disappears.

So I set up a sheet and took a screen shot

clewis_10990

Thanks, KPH. I am still getting an error. The first column (Range 4) is the sales stage column, Range 10 is the percent complete and Range 2 is the forecast amount. Not sure what I am doing wrong.

KPH

It looks like you are doing this across sheets. Is that correct?

The formula above works on the one sheet that contains Sales Stage, Percent Complete, and Forecast amount. It can be used on any row in this sheet and multiples Percent Complete on that row by Forecast Amount on that row and displays this on rows where Sales Stage is Closed Won.

Are you looking to put this on a different sheet? If so:

Do you still have one row per project, and if so what identifies them? We will need to include that identifier in the IF formula to pull in the correct row to the new sheet.

Or, do you want a total for all the rows in the first sheet? In which case, I would use the formula as it is in a helper column* in the sheet where the data is and then use =SUM of that column in the sheet where you want the total.

*helper column - added in to do the math and can be hidden.

clewis_10990

I have created a metrics page that takes info from this sheet as needed so I pull columns in as needed. All of the metrics feed to the dashboard. I definitely only have one project per row. Here are the headers. What I am trying to do is show how much I have in the pipeline that is a "won" contract but also the percentage of completion so the viewer knows how much more we have in future work to complete.

Here are the columns I am working with:

KPH

And where are you trying to put the formula? On the sheet in the screen shot, or somewhere else?

clewis_10990

On a separate page for metrics. This is an example of one of the formulas I have created for that metrics page. The only way I know to do it is to reference the entire column as I have done here

KPH

And what do you want on that metrics sheet? Just one cell with the total for all the rows in the other sheet?

If that is the case:

Put the new column into the first sheet with the formula to calculate Percent Complete * Forecast Amount on each row if the sales stage is won

=IF([Sales Stage]@row="Closed Won",[Percent Complete]@row*[Forecast Amount]@row)

On your metrics sheet you just need to sum that column

=SUM({new column})

clewis_10990

Perfect! THanks so much, KPH

KPH

Happy to help! Thanks for letting me know the problem is solved.