+3I have a table with project status, proposal amounts, and percent likelihood of receiving the project. I’d like to have the percent likelihood automatically update to 100% when the project status goes from pending to awarded.
Is this possible to achieve?
Thanks!
You could make the % a formula field and nest a bunch of IF() statement within it-- so that if the status = Awarded, then percentage = 100. You’ll have to set up all the other status %s inside the statement too.
+4Hi Chris
Noel’s solution will certainly work for you as long as you don’t need to manually alter the Percentage (because it’s now a Formula field). The are some choices you could make here:
The simplest would be a slight modification of the above - have a calculated % Chance field which uses a formula to take the manually entered % unless the Status = Awarded:
Another option, which I’ve often used in CRM type applications, is to have a table containing your Sales Statuses and for this table to hold a % Chance of winning associated with each Status. This approach kind of normalises your forecasting of your sales:
Then in this case, the % on your forecasting project table is actually a lookup from the Sales Statuses table:
I hope this helps!
+2Hi Chris
Noel’s solution will certainly work for you as long as you don’t need to manually alter the Percentage (because it’s now a Formula field). The are some choices you could make here:
The simplest would be a slight modification of the above - have a calculated % Chance field which uses a formula to take the manually entered % unless the Status = Awarded:
Another option, which I’ve often used in CRM type applications, is to have a table containing your Sales Statuses and for this table to hold a % Chance of winning associated with each Status. This approach kind of normalises your forecasting of your sales:
Then in this case, the % on your forecasting project table is actually a lookup from the Sales Statuses table:
I hope this helps!
Hi Chris
Thought I would add my experiences. Both methods that Julian suggests work and I have used them. One problem with the lookup is that if you have colour coded your status in the lookup table, it doesn’t transfer to the referencing table {- @Katherine_Duh : this would be a useful facility as it saves nested IF()s - } For instance; if you use green to show that you have won the project, the referencing table won’t show this. Therefore, you have to use a series of nested If() statements which is a pig to debug if you add a space or don’t match the number of brackets. Should you decide to go down the IF() route, then I would also strongly recommend a drop down list for the project status, so that those entering the data are not in danger of mistyping the status.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.