Formula Help

[adem]

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!

 

[GorD]

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]

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]

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

 

[crgriffiths]

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]

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]

I like Brian's suggestion above. However, given your initial posting, the following is one possible (but not very slick) approach--

MrE3084.xls
	A	B	C	D	E	F
1	Supplier	Del_Dt1	Del_Dt2	Del_Dt3	DaystilDel	Ord_Dt
2	Tesco	Monday	Tuesday	Friday	2	Wednesday
3	Sainsbury's	Tuesday	Thursday	Friday	3	Saturday
4	Omnicom	Wednesday	Thursday	Friday	1	Wednesday
5	Sysco	Monday	Wednesday	Thursday	1	Wednesday
6	Shallot's	Tuesday	Wednesday	Wednesday	4	Friday
7	Fedderson	Monday	Monday	Monday	6	Tuesday
8
9	Tbl:
10	Saturday	1
11	Sunday	2
12	Monday	3
13	Tuesday	4
14	Wednesday	5
15	Thursday	6
16	Friday	7
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]

Hi adem:

Here is my convoluted formula based approach using additional working columns ...

Book1
	A	B	C	D	E	F	G	H	I
1	TESCO	Monday	Tuesday	Friday	Friday	Wednesday		source
2	SAINSBURY'S	Tuesday	Thursday	Friday	Tuesday	Saturday		results
3	Bobby's	Monday	Tuesday	Friday	Friday	Thursday
4	Gloria's	Wednesday	Thursday	Friday	Wednesday	Tuesday
5
6	INTERMEDIARYWORKINGTABLE
7	TESCO	2	3	6	6	4		Sunday	1
8	SAINSBURY'S	3	5	6	3	7		Monday	2
9	Bobby's	2	3	6	6	5		Tuesday	3
10	Gloria's	4	5	6	4	3		Wednesday	4
11								Thursday	5
12								Friday	6
13								Saturday	7
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]

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
	A	B	C	D	E	F
1	Tesco	Mon	Tue	Fri	2	Wed
Sheet1

 

[adem]

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