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

  • Stefan
    Stefan ✭✭✭✭✭✭

    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.

  • Jed Rusyniak
    Jed Rusyniak ✭✭✭✭

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

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @Jed Rusyniak ,

    wow, that's a treat of a nested IF statement ;-)

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Jed Rusyniak

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!