Calculate percentage of value based on the day of the week
I'm working on a project for one of my customers. I need to be able to use the historical percentage of containers arriving at port on each day of the week to project the number of containers that are going to arrive at port in the future based of the forecasted volume in that given week. Struggling with the formula to write that will do this for me.
Something like, if DOW is Sunday then return 22.02% of the Weekly Forecasted containers number, if DOW is Monday then return10.79% of the Weekly forecasted Containers...
Answers
-
Hello @Jed Rusyniak ,
The formula you want may look something like this: =IF(DOW@row = "Sunday", [Sum of containers Arriving at Port]1)
Although this is an example, if you can provide a little more detail on where the percentages are stored, whether that be in the same Sheet or a different, I will be able to craft a more logical and direct formula to fit this scenario.
Please let me know if you have any questions
Regards
Sean
-
Hi @Jed Rusyniak ,
VLOOKUP comes to my mind.
I don't know where your day and percentage labels live in your sheet, so I used "day of week" and "percentage" als column title.
=VLOOKUP( search_value, lookup_table, column_num, [match_type ])
=VLOOKUP(DOW@row, [Day of week]$1:percentage$7, 2, false)
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
I ended up using this:
=IF(WEEKDAY(Date465) = 1, [Weekly Forecasted Containers]465 * 0.2202, IF(WEEKDAY(Date465) = 2, [Weekly Forecasted Containers]465 * 0.1079, IF(WEEKDAY(Date465) = 3, [Weekly Forecasted Containers]465 * 0.1141, IF(WEEKDAY(Date465) = 4, [Weekly Forecasted Containers]465 * 0.0858, IF(WEEKDAY(Date465) = 5, [Weekly Forecasted Containers]465 * 0.1295, IF(WEEKDAY(Date465) = 6, [Weekly Forecasted Containers]465 * 0.2466, IF(WEEKDAY(Date465) = 7, [Weekly Forecasted Containers]465 * 0.0959)))))))
-
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
I'd recommend replacing the row number to @row instead if possible, so you don't need to worry about the row numbers and minimize the system resource use.
=IF(WEEKDAY(Date@row) = 1, [Weekly Forecasted Containers]@row * 0.2202, IF(WEEKDAY(Date@row) = 2, [Weekly Forecasted Containers]@row * 0.1079, IF(WEEKDAY(Date@row) = 3, [Weekly Forecasted Containers]@row * 0.1141, IF(WEEKDAY(Date@row) = 4, [Weekly Forecasted Containers]@row * 0.0858, IF(WEEKDAY(Date@row) = 5, [Weekly Forecasted Containers]@row * 0.1295, IF(WEEKDAY(Date@row) = 6, [Weekly Forecasted Containers]@row * 0.2466, IF(WEEKDAY(Date@row) = 7, [Weekly Forecasted Containers]@row * 0.0959)))))))
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!