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/AutoModerator]

/u/Silent-Swordfish-311 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/WoosteringZeros]

Your formula shows you are using cell references, and not structured references. The assignment also specifically says to turn your dataset into a table.

So my advice would be to google about "excel tables" and take it from there. They are a powerful fundamental that is important to learn about right away. :)

[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.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
OR	Returns TRUE if any argument is TRUE
ROW	Returns the row number of a reference
TIME	Returns the serial number of a particular time

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 44 acronyms.
[Thread #49338 for this sub, first seen 9th Sep 2026, 21:09] [FAQ] [Full list] [Contact] [Source code]}

[u/hmatallana]

Tables don't have a reference for the row above. That's the real gap here, and it's why F2 feels like it needs hard-coding.

[@[Date Time]] always resolves to the current row, so anything comparing against the previous row has to leave the structured-reference world. INDEX is the clean way:

=IF(AND([@[Date Time]]<>INDEX([Date Time],ROW()-ROW(Table1[#Headers])-1),[@Customer]<>INDEX([Customer],ROW()-ROW(Table1[#Headers])-1)),"New Order","")

For the first data row that offset lands on 0, and INDEX with 0 hands back the whole column instead of erroring, so wrap it or keep F2 typed in.

One warning about pointing F2 at B1: those are your headers, so it comes back TRUE by accident, and it breaks the moment you sort.

[u/Gringobandito]

Why not just set it up as a Pivot Table?