Calculate percentage of value based on the day of the week

Jed Rusyniak

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

Sean Morgan

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

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

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

Hi @Jed Rusyniak ,

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

Greetings

Stefan

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