Calculating work completed and forecasts
I want the smartsheet to automatically update the “WIP (local)” column each month and reduce the current months forecast to £0. As you can see below, the “WIP (local)” column has increased and the forecast for September is £0 (this is fine). However, the smartsheet has now overestimated the total cost of the task (work done (wip local column) + forecast is = £338). When the cost of the task is actually £290.
The formula used to calculate work done is
=如果(DAY() < [Start Date]131, 0, [Total Cost]131 / (MONTH([End Date]131) - MONTH([Start Date]131) + 1 + IF(YEAR([End Date]131) = 2021, 12, 0) + IF(YEAR([End Date]131) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]131) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0)))
The formula used to calculate october forecast (£145 value) is
=IFERROR(IF(AND(MONTH(TODAY()) >= [Oct 20]$13, YEAR(TODAY()) = [Oct 20]$14), 0, (IF(OR(YEAR($[End Date]131) > [Oct 20]$14, AND(MONTH($[End Date]131) >= [Oct 20]$13, YEAR($[End Date]131) >= [Oct 20]$14)), IF(OR(YEAR($[Start Date]131) < [Oct 20]$14, AND(MONTH($[Start Date]131) <= [Oct 20]$13, YEAR($[Start Date]131) <= [Oct 20]$14)), $[Total Cost]131 / (MONTH($[End Date]131) - IF(AND(MONTH(TODAY()) > MONTH($[Start Date]131), YEAR(TODAY()) = YEAR($[Start Date]131)), MONTH(TODAY()), MONTH($[Start Date]131)) + 1 + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 1, 12, 0) + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 2, 24, 0)), 0))) * $Rates$202) * (1 - $[% Complete]131), 0)
Appreciate any help.
Answers
-
Genevieve P. Employee AdminIt looks like your first formula is simply dividing the [Total Cost] by the number of months... whereas your second formula is doing that, but then weighing it both by theRatesand the% Completecolumn (which is why you're seeing different values from the two different formulas).
Try taking out the multiplications at the end of your second formula to see if that's a better result for you:
=IFERROR(IF(AND(MONTH(TODAY()) >= [Oct 20]$13, YEAR(TODAY()) = [Oct 20]$14), 0, (IF(OR(YEAR($[End Date]131) > [Oct 20]$14, AND(MONTH($[End Date]131) >= [Oct 20]$13, YEAR($[End Date]131) >= [Oct 20]$14)), IF(OR(YEAR($[Start Date]131) < [Oct 20]$14, AND(MONTH($[Start Date]131) <= [Oct 20]$13, YEAR($[Start Date]131) <= [Oct 20]$14)), $[Total Cost]131 / (MONTH($[End Date]131) - IF(AND(MONTH(TODAY()) > MONTH($[Start Date]131), YEAR(TODAY()) = YEAR($[Start Date]131)), MONTH(TODAY()), MONTH($[Start Date]131)) + 1 + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 1, 12, 0) + IF(YEAR($[End Date]131) - YEAR($[Start Date]131) = 2, 24, 0)), 0)))), 0)
Otherwise, if you did want to weigh this by the Rates and % Complete, you'd need to add that element into yourfirstformula (instead of removing it from your second). Does that make sense?
Cheers!
Genevieve
-
Sareena ✭Hi Genevieve,
Thank you for your response, unfortunately, this has not worked. I added both Rates and % Complete to the first formula, however it is still over estimating how much the task is.
-
Genevieve P. Employee Admin
Can you copy/paste how you added these elements in the first formula?
What happens if you tried adjusting the second formula, instead?
-
Sareena ✭
Hi@Genevieve P,
This formula is actually resulting in an error:
=如果(DAY() < [Start Date]1283, 0, [Total Cost]1283 / (MONTH([End Date]1283) - MONTH([Start Date]1283) + 1 + IF(YEAR([End Date]1283) = 2021, 12, 0) + IF(YEAR([End Date]1283) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]1283) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates$1347) * (1 - $[% Complete]1283), 0)
Below is a simplified sheet:
Task labelled "original formula" includes has the rates and % completed, included in the formula for forecasts only, where as the task labelled "smartsheet formula" has rates and % completed in both formulas (for WIP and forecast).
To summarise, I want the WIP to increase depending on % complete entered and the current months forecast to always = 0. For e.g. using sheet above. The task starts in september and ends in december. If someone completes 10% of the work, the forecast for september should be 0 and the remaining amount should be projected in october to december.
-
Genevieve P. Employee Admin
It looks like you may have just copied/pasted the same end of the formula from your second one onto the first, however they are built slightly differently. The second one has an IFERROR statement wrapped around it, which is why the end has, 0)——你不需要在你的第一个公式。
It also looks like your row references are for row1283in the sheet... is that where you're pasting this formula? I've adjusted this to be row5, but you can change that if it really is row 1283. Try this:
=(IF(TODAY() < [Start Date]5, 0, [Total Cost]5/ (MONTH([End Date]5) - MONTH([Start Date]5) + 1 + IF(YEAR([End Date]5) = 2021, 12, 0) + IF(YEAR([End Date]5) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]1) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates$5) * (1 - $[% Complete]5)
If you always want the formula to look intoits current row, you can also use the@row functioninstead of specifying the row number.Try this:
=(IF(TODAY() < [Start Date]@row0(总成本)@row /(月([结束日期]@row) - MONTH([Start Date]@row) + 1 + IF(YEAR([End Date]@row) = 2021, 12, 0) + IF(YEAR([End Date]@row) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]@row) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * $Rates@row) * (1 - $[% Complete]@row)
This formula should no longer give you an error... however you'll have to test and see if it's returning the math that you're looking for. Let me know if it works for you!
Cheers,
Genevieve
-
Sareena ✭
Unfortunately, the first formula returned "invalid data type" and the second returned a value of £1250.
Appreciate your help :)
-
Genevieve P. Employee Admin
So that value is because it's multiplying your division by the Rate in that row, in this case £50. Can I ask why you're adding in the rates to the formula, or what these rates represent? You may just want to weigh it by the %... try this:
=如果(DAY() < [Start Date]@row, 0, [Total Cost]@row / (MONTH([End Date]@row) - MONTH([Start Date]@row) + 1 + IF(YEAR([End Date]@row) = 2021, 12, 0) + IF(YEAR([End Date]@row) = 2022, 24, 0)) * (MONTH(TODAY()) - MONTH([Start Date]@row) + 1 + IF(YEAR(TODAY()) = 2021, 12, 0) + IF(YEAR(TODAY()) = 2022, 24, 0))) * (1 - $[% Complete]@row)
-
Sareena ✭
Hi@Genevieve P,
Your formula has returned the following:
Nearly there... However, I want the person to be able to provide the % complete. For e.g. inputting 25% done, the WIP to = £25, the forecast for september to be 0, and the forecasts for Oct - Dec to be £25.
Changing the % complete to 25% returns the following values:
As you can see, this now amounts to £75.75.
-
Genevieve P. Employee Admin
I don't think I understood what your formula was originally doing, so adding a percent was perhaps not the right way to go.
I broke down the formula a bit to understand it, and I'll explain each step below. I've taken out the IF statements which are looking for different years and adding on either 12 or 14 months, since we're just looking in this year for now.
This is the core of the formula:
=[Total Cost]1 * (1 - $[% Complete]1) / (MONTH([End Date]1) - MONTH([Start Date]1) + 1) * (MONTH(TODAY()) - MONTH([Start Date]1) + 1)
The first thing it's doing is it's taking the Total Cost and multiplying it bythe percent left to complete(in this case, 0.75) to figure out the cost left for what is not complete.
Then,
(MONTH([End Date]1) - MONTH([Start Date]1) + 1)
It's figuring out how many months are in this task (in this case, 4, from Sept - Dec)
and then it divides that total cost by the number of months
75 / 4
You could stop at this point if you simply wanted to see how much cost is leftper month, however, you want to see a total Work in Progress (is that right? Are you wanting a total of what's already been completed?)
Then your formula it multiplies this by how many months have already passed:
(MONTH(TODAY()) - MONTH([Start Date]1) + 1)
Now, if wehadn'tadded in the Percentage, this would take the total (£100), divide it by how many months there are in the task (4, so £25), and then multiply that by how many months had already passed (in this case 1, so £25).
If we were in November, this would take the total (£100), divide it by task length (4 months, so £25), then multiply it by the months passed (£25 x 3 = £75) to show you a total of whatshouldhave been completed.
Based on this, it sounds like you havetwo waysto calculate how much is currently WIP. You caneitheruse the original formula you had without the percentsor,since you have the percent already, you can use this to create your WIP with a simple:
=[Total Cost]@row * $[% Complete]@row
This will tell you how much of the Total Cost has been completed, based on the percentage of task complete (instead of based on the months passed). Does that make sense?
-
VBJBC ✭
Hi, How can I calculate this on a weekly basis instead of monthly?
I tweaked the formula above:
=如果(DAY() < Start@row, 0, [Initial SOV Amount]@row / (WEEK(Finish@row) - WEEK(Start@row) + 1 + IF(YEAR(Finish@row) = 2022, 12, 0) + IF(YEAR(Finish@row) = 2024, 24, 0)) * (MONTH(TODAY()) - MONTH(Start@row) + 1 + IF(YEAR(TODAY()) = 2022, 12, 0) + IF(YEAR(TODAY()) = 2024, 24, 0))) * (1 - $[% Complete]@row)
-
VBJBC ✭
Hi Genevieve,
I used this formula to help forecast the revenue produced based on wip.:
=如果(DAY() < Start@row, 0, [Total SOV]@row / (MONTH(Finish@row) - MONTH(Start@row) + 1 + IF(YEAR(Finish@row) = 2022, 12, 0) + IF(YEAR(Finish@row) = 2024, 24, 0)) * (MONTH(TODAY()) - MONTH(Start@row) + 1 + IF(YEAR(TODAY()) = 2022, 12, 0) + IF(YEAR(TODAY()) = 2024, 24, 0))) * (1 - $[% Complete]@row)
Do you know how I can change this to a weekly forecast? I'm hoping to see how much revenue we will accrue in a given week if we keep to our current schedule.
-
Genevieve P. Employee Admin
Hi@VBJBC
This comes down to what sort of math you want to accomplish.
What I might suggest doing here is base your calculation off of theRemaining Balancecolumn instead of the original Total column.
Then you could look to see if the Start date is in the future (hasn't started yet) or if the Finish date is in the past (completed), and return 0.
=IF(OR(TODAY() < Start@row, Finish@row <= TODAY()), 0,Otherwise, you could subtract today from the Finish date to see how many days are remaining. If there are more than 7 days remaining, you can break this down into weeks by dividing those total days by 7:
IF(Finish@row - TODAY() > 7, [Remaining Balance]@row / ((Finish@row - TODAY()) / 7),Otherwise, if there are less than 7 days remaining, you can simply return the remaining balance, since that's what will be showing this week.
(余额)@row))Full potential formula:
=IF(OR(TODAY() < Start@row, Finish@row <= TODAY()), 0, IF(Finish@row - TODAY() > 7, [Remaining Balance]@row / ((Finish@row - TODAY()) / 7), [Remaining Balance]@row))
This does ignore the percentage column because it assumes that the Remaining Balance column is looking at the percentage. The number of Days and number of Weeks on the right is just to show you what the middle part of the formula is calculating.
Let me know if this makes sense and will achieve your goal!
Cheers,
Genevieve
-
VBJBC ✭
Thanks,@Genevieve P.!
For this formula, is it only possible to forecast one week in advance? Is there a way I can pick a random week let's say sometime in May 2023 and get a forecast of revenue for that week?
-
Genevieve P. Employee Admin
Hi@VBJBC
This formula is only looking at the current week's forecast (using TODAY as the date to reference).
It's more complicated to look at the "next week" because your Remaining Balance is displaying for today's date. We could subtract this week's forecast from the Remaining Balance and then use TODAY(7) to look 7 days ahead... try something like this:
=IF(OR(TODAY(7)< Start@row, Finish@row <= TODAY(7)), 0, IF(Finish@row - TODAY(7)> 7, ([Remaining Balance]@row - ([Remaining Balance]@row / ((Finish@row - TODAY()) / 7))) / ((Finish@row - TODAY(7)) / 7), ([Remaining Balance]@row - ([Remaining Balance]@row / ((Finish@row - TODAY()) / 7)))))
In regards to picking a custom week, the way I would do this is break down the Total SOV by the days of the task so we have a weekly rate:
[Total SOV]@row / ((Finish@row - Start@row) / 7)Then we'll need to add in statements for what to do if the Start date is in the future compared to your preferred date, etc. I've put my date in a Sheet Summary Field:
=IF(OR([Forecast Week Start]# + 7 < Start@row, Finish@row <= [Forecast Week Start]#), 0,[Total SOV]@row / ((Finish@row - Start@row) / 7))
Note that this doesn't take into account partial weeks... so if you select a date where the task only has 2 days in that week, it will still show you the generic 'weekly' breakdown. Is this what you were looking for?
Cheers,
Genevieve
-
VBJBC ✭
This is perfect! Thanks so much for your help Genevieve!
Best,
Jillian
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
