if formula question

blakeboyuk

New Member
Joined
Mar 5, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

Unfortunately the wind whistles through my ears at times and I struggle to comprehend even basic excel formula logic.

One such example is that I have a number of scenarios below which determine whether an order is defined as a HIT or a MISS.

The main question is was the part ordered and picked before the cutoff meaning its a HIT. This gets a little more complicated and I need to find a formula to account for orders placed after cutoff and picked the following day which are also HITS. When I've been attempting this thus far I get "too many arguments for this function" error message.

Can any one help offer some advice on how to do this?

Thanks so much

David

Order NumberOrder Placed?Pick DatePick TimeStatus
110354220Ordered after cutoffPicked the following dayPicked before cutoffHIT
110250140Ordered before cutoffPicked on the same dayPicked before cutoffHIT
110307676Ordered before cutoffPicked on the same dayPicked after cutoffMiss
110366617Ordered after cutoffPicked the following dayPicked after cutoffMiss
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,396
Office Version
  1. 365
Platform
  1. MacOS
can you show the formula you tried
I need a lot more information to assist

I assume the cells have dates in and not those words

What are the cutoffs , how does excel know the times to use
Ordered on 5th Aug 2020 at 11:15 ,
whats the cutoff , is it 24:00 hour operation ?
So if ordered at 23:00 on one day and picked at 01:00 on next day - 2 hour elapsed time - is that picked before cutoff ?

Order placed , is that a date and time in the cell
Pick Date , is just the Date
Pict time , is just the time so these are split into two columns

each order number is only 1 row , you don't have multiple rows for the same order
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,773
Office Version
  1. 365
Platform
  1. Windows
Hi, and a belated "Welcome to Mr. Excel" !!

Assuming your order number starts in column A, and your actual data starts in row 2...

Use this formula in E2, then fill down (with the fill handle):

=IF(OR(AND(B2="Ordered before cutoff",C2<>"Picked on the same day"),AND(B2="Ordered before cutoff",C2="Picked on the same day",D2="Picked after cutoff"),AND(B2="Ordered after cutoff",C2<>"Picked on the same day",D2="Picked after cutoff")),"Miss","HIT")
 

blakeboyuk

New Member
Joined
Mar 5, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thanks very much Sykes & Etaf for your responses.

Skyes, yes that has worked. fantastic! So an IF/OR/AND formula combined was the answer.That's something I'm not familiar with so I'm going to try to break down what you have done so I will know for next time.

Many thanks once again, David.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,773
Office Version
  1. 365
Platform
  1. Windows
David
I would point out that my solution wasn't so much THE answer, as just ONE solution. There are usually many ways to achieve the same goal with Excel, and very often I find that others come up with solutions which are both shorter AND slicker than mine, so you may find that other options become available...

For info, the 3 AND functions evaluate the conditions within their brackets (I believe you can test up to 255 conditions at a time!). For an AND to return TRUE, ALL conditions within its brackets need to return TRUE.
The OR function will return TRUE if ANY of the conditions within it return TRUE, so if ANY one or more of the 3 AND conditions returns TRUE, the OR returns TRUE, and consequently the IF returns TRUE, and we end up with a "Miss" in Column E.

Looking at your requirements, I think there are only 3 conditions where you fail to achieve your dispatch targets:
1. The buyer orders in time, and you don't pick the item that same day.
2. The buyer orders in time, and you pick the item the same day - but not before the day's deadline.
3. The buyer orders too late (so you have until the following day to pick) but you miss the following day's deadline.

These are the 3 AND conditions which are tested for.
If ANY return TRUE, the OR tells the IF to display "Miss" otherwise it displays "HIT."

HTH

Pleasure to help, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,561
Members
414,077
Latest member
ammylar5

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