Formula Help

adem

Board Regular
Joined
Mar 3, 2003
Messages
87
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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
Joined
Mar 3, 2003
Messages
87
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
 

crgriffiths

New Member
Joined
Mar 8, 2004
Messages
3

ADVERTISEMENT

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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Jan 15, 2003
Messages
564

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Mar 3, 2003
Messages
87
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

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

Which adblocker are you using?

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
Top