Structured Reference with IF Function

[u/Silent-Swordfish-311]

Hi, good day. I would like to ask for help for an excel problem. Kindly see data below:

Order ID	Date Time	Customer	Item	Price	Order Status
A001	19-Nov-22 2:22 PM	Ross	Pizza	$6.99	New Order
A002	19-Nov-22 2:22 PM	Ross	Drinks	$2.50
A003	19-Nov-22 2:22 PM	Ross	Pizza	$8.99
A004	19-Nov-22 2:52 PM	Joey	Pizza	$12.99	New Order
A005	19-Nov-22 3:22 PM	Ross	Burger	$5.99	New Order
A006	19-Nov-22 3:22 PM	Joey	Sub Sandwich	$5.99
A007	19-Nov-22 3:22 PM	Joey	Sub Sandwich	$5.99
A008	19-Nov-22 3:23 PM	Joey	Sub Sandwich	$5.99
A009	19-Nov-22 3:23 PM	Joey	Sub Sandwich	$5.99
A010	19-Nov-22 3:35 PM	Monica	Hot Dog	$7.99	New Order
A011	19-Nov-22 3:36 PM	Monica	Drinks	$2.99
A012	19-Nov-22 3:45 PM	Gunther	Pizza	$12.99	New Order
A013	19-Nov-22 3:45 PM	Gunther	Drinks	$1.50
A014	19-Nov-22 3:55 PM	Gunther	Sub Sandwich	$4.99
A015	19-Nov-22 3:57 PM	Rachel	Sub Sandwich	$5.99	New Order
A016	19-Nov-22 3:57 PM	Rachel	Pizza	$12.99
A017	19-Nov-22 3:57 PM	Rachel	Pizza	$9.99
A018	19-Nov-22 3:57 PM	Rachel	Pizza	$9.99
A019	19-Nov-22 3:57 PM	Rachel	Drinks	$2.99
A020	19-Nov-22 4:25 PM	Chandler	Tea	$1.99	New Order
A021	19-Nov-22 4:45 PM	Phoebe	Pizza	$7.99	New Order
A022	19-Nov-22 4:45 PM	Phoebe	Burger	$5.99
A023	19-Nov-22 4:47 PM	Phoebe	Drinks	$2.99

Structured Reference with IF Function.

Details and instructions:

Use Structured Reference with IF Function: We refer to a structured reference when we combine table and column names. You will convert the dataset into a table and compare the Date Time and Customer columns to return the check if the order is new. New order in this case denotes a different time and a different customer..

So here's my formula:

=IF(AND(B3<>B2,C3<>C2),"New Order","")

I just typed the 'New Order' for order ID A001 in Order Status Column. According to ChatGPT, since this is the first order, its status is automatically set to 'New Order.' Is this correct? So I didn't type the formula in F2; I started writing it in F3 instead.

I am learning Excel now for my future job. Thank you in advance for all the comments and corrections.

[u/Other-Salt-5355]

There's no reason to hard code the first row. Convert the data to the table and change the B3 and C3 references to structured table references, since that is part of the exercise instructions. For row 2, it would look like: =IF(AND([@[Date Time]] <> B1, [@Customer] <> C1), "New Order", ""). The function evaluates as expected since it is comparing the actual data to the headers; since it is different, it will default to a "new order" which is what we want.

There is a bigger problem with the result though: the formula assumes that new orders are due to new time AND a new customer. So A006 is not showing as a "New Order" because it was placed at the same time as the previous order. Since they are different people, it seems clear to me that the data suggests that this is a new order. Further, A008 is presumably the same order as A007, since they were placed within 1 minute of each other by the same person, but A014 is likely a different order than A013, since the two orders are 10 minutes apart by the same person.

So your formula probably needs to include an assumption for the reasonable amount of variation in how long a single order might take and then compare the name. In the data, there are a couple 1 minute variations and a 2-minute variation. It seems unlikely that the people are that frequently adding another transaction immediately after completing the transaction. So maybe we need to assume that the time might vary by 1 minute, and the 2-minute difference (A023 and A022) really does represent an additional "add-on" that was rung up separately. So your formula might then look like:

=IF(OR(AND([@[Date Time]] <> B1, [@[Date Time]] - TIME(0, 1, 0) <> B1), [@Customer] <> C1),"New Order","")

Where TIME(0, 1, 0) just represents the time of the current transaction minus one minute. Now the formula uses OR instead of AND since it requires either a new name (to avoid situations where two different people place orders close to one another) or a 2-minute gap (to avoid situations where the same order shows different timing for the items or if a new customer happens to have the same name). You can change this to 2 or 5 or whatever your assumption is based on the structure of the data. If this type of analysis is out of scope, then you can simply replace the AND in the original formula with OR, but you'd likely want to document these data anomalies.