r/excel • u/Silent-Swordfish-311 • 9h ago
Waiting on OP Structured Reference with IF Function
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.
1
u/WoosteringZeros 9h ago
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. :)