# Formula Help

#### adem

##### Board Regular
Example Spreadsheet:

Cell F1 is a variable day, typed in by the user (the day a product has been ordered) e.g. SATURDAY
Column A is a list of static suppliers e.g. TESCO, SAINSBURY'S etc
Columns B, C & D indicate static delivery days for each store e.g B1 = MONDAY C1 = TUESDAY D1 = THURSDAY

I would like to put a formula into column E that looks at the 'order day' in F1, and returns the number of days until the next supplier delivery is due.

Any help would be fantastic

Thanks!

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### GorD

##### Well-known Member
Couple of things I can't follow on your example.

1) Do all suppliers supply all parts and you just want to know who can deliver it the quickest.

2) What happens if the dalivery day matches the user entered day

3) The stores are listed in a column but the delivery days are listed in a row and therefore don't seem to relate to each other.

Can you clarify? Posting some data would be good

#### adem

##### Board Regular
Apologies, my first effort with datatable didn't turn out. Let me try a brand new one row example:

A1 - Tesco (Supplier Name)
B1 - Monday (1st of three weekly deliveries)
C1 - Tuesday (2nd of three weekly deliveries)
D1 - Friday (3rd of three weekly deliveries)
E1 - (Formula to go here)
F1 - WEDNESDAY

I want to define in cell E1, the no of days until the next delivery is due. In this case it would be 2 (Friday in D1). If it was the same day, it would return 0.

The reason that the stores are in a column and the days are in a row is because there can be up to three deliveries a week for each store.

Hope this makes sense. If not I could mail a copy of the spreadsheet to anyone who could help (still having trouble with the table).

thanks for your patience

#### Zack Barresse

##### MrExcel MVP
try posting a sample, at least of what you want achieved, with Colo's HTML Maker.

#### crgriffiths

##### New Member
One way to solve your problem is to create extra columns. Assign one column for each day of the week - 7. Then create a row where you can insert the integers 1, 2, 3, 4, 5, 6, 7 into cells corresponding to the columns. Example, if column A is for sunday, insert the number 1 in a row a1. Now you have a simple reference table. Build your formulas using the absolute reference symbols '\$' to find the absolute difference between any two columns. You can use IF statements in the eigth or ninth column to search the first seven columns for meaningful info and return the value you want.

A second way is to insert dates into the cells instead of text "monday", "tuesday," etc. You can subtract one date from another to find differences.

A third option is to use an IF statement that compares text. example

=IF(b1="Monday", do something.....)

hope this helps,

Carl

#### Brian from Maui

##### MrExcel MVP
Use true date values instead of Monday, Tuesday, etc and format to show the days of the week. Then use the Networkdays function for the next delivery.

#### rrdonutz

##### Well-known Member
I like Brian's suggestion above. However, given your initial posting, the following is one possible (but not very slick) approach--
MrE3084.xls
ABCDEF
1SupplierDel_Dt1Del_Dt2Del_Dt3DaystilDelOrd_Dt
2TescoMondayTuesdayFriday2Wednesday
3Sainsbury'sTuesdayThursdayFriday3Saturday
4OmnicomWednesdayThursdayFriday1Wednesday
5SyscoMondayWednesdayThursday1Wednesday
6Shallot'sTuesdayWednesdayWednesday4Friday
7FeddersonMondayMondayMonday6Tuesday
8
9Tbl:
10Saturday1
11Sunday2
12Monday3
13Tuesday4
14Wednesday5
15Thursday6
16Friday7
Sheet1

The formula in cell E2 is:

=MIN(7+MOD(VLOOKUP(B2,Tbl,2,0)-VLOOKUP(F2,Tbl,2,0),-7),7+MOD(VLOOKUP(C2,Tbl,2,0)-VLOOKUP(F2,Tbl,2,0),-7),7+MOD(VLOOKUP(D2,Tbl,2,0)-VLOOKUP(F2,Tbl,2,0),-7))

where 'Tbl' refers to the range A10:B16 in this example. Note, also, that if a supplier makes only 1 or 2 deliveries a week, then "dummy" repeated delivery days must be entered in order to avoid the necessity for error-checking in the formula (see the last 2 rows). Finally, same day deliveries are "disallowed" by this formula. That is, your answer can't equal 0.

--Tom

#### Yogi Anand

##### MrExcel MVP
Hi adem:

Here is my convoluted formula based approach using additional working columns ...
Book1
ABCDEFGHI
1TESCOMondayTuesdayFridayFridayWednesdaysource
2SAINSBURY'STuesdayThursdayFridayTuesdaySaturdayresults
3Bobby'sMondayTuesdayFridayFridayThursday
4Gloria'sWednesdayThursdayFridayWednesdayTuesday
5
6INTERMEDIARYWORKINGTABLE
7TESCO23664Sunday1
8SAINSBURY'S35637Monday2
9Bobby's23665Tuesday3
10Gloria's45643Wednesday4
11Thursday5
12Friday6
13Saturday7
Sheet4 (4)

Formula in cell B7 is ... =VLOOKUP(B1,\$H\$7:\$I\$13,2,0)

this is then copied to cells B7:D10, and F7:F10

Formula in cell E7 is ... =INDEX(B7:D7,IF(SUM(--(IF(B7:D7>F7,1,0)))=0,1,IF(ISNA(MATCH(F7,B7:D7,0)),MATCH(F7,B7:D7)+1,MATCH(F7,B7:D7,0))))

this is an array formula -- and is also copied to cells E8:E10

Formula in cell E1 is ... =LOOKUP(E7,\$I\$7:\$I\$13,\$H\$7:\$H\$13)

this is then copied to cells E2:E4

Since I have used intermediary columns in this solution, this may or may not work for you.

#### Brian from Maui

##### MrExcel MVP
adem said:
Apologies, my first effort with datatable didn't turn out. Let me try a brand new one row example:

A1 - Tesco (Supplier Name)
B1 - Monday (1st of three weekly deliveries)
C1 - Tuesday (2nd of three weekly deliveries)
D1 - Friday (3rd of three weekly deliveries)
E1 - (Formula to go here)
F1 - WEDNESDAY

I want to define in cell E1, the no of days until the next delivery is due. In this case it would be 2 (Friday in D1). If it was the same day, it would return 0.

The reason that the stores are in a column and the days are in a row is because there can be up to three deliveries a week for each store.

Hope this makes sense. If not I could mail a copy of the spreadsheet to anyone who could help (still having trouble with the table).

thanks for your patience

How about this? B1, C1, D1, and F1 are custom formatted as ddd. Since you don't want to include the last day and first day of delivery I added a -2 at the end of the Networkdays formula,
Book1
ABCDEF
1TescoMonTueFri2Wed
Sheet1

#### adem

##### Board Regular
Brilliant stuff, thanks Brian, Yogi, Tom, GorD, Carl..

I will look forward to having a play with your inspired ideas later and let you know how I got on.

Many thanks

Replies
5
Views
351
Replies
6
Views
127
Replies
0
Views
333
Replies
4
Views
412
Replies
0
Views
233

Threads
1,181,443
Messages
5,929,942
Members
436,708
Latest member
THEjet31

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back