Question description:
This user has given permission to use the problem statement for this
blog.
I need to: create a formula with structured references to calculate the percentage of the Sticker Price in column E. Columns C and D have the sticker price and sale price, respectively. How do I do this? My professor did not tell us, but expects this to be done.
Solved by V. Q. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/09/2018 - 07:27
Hi, welcome to Got it Pro-Excel!
User
27/09/2018 - 07:27
Hello!
Excelchat Expert
27/09/2018 - 07:28
According to my diagnosis, you need help with calculating percentages, is that correct?
User
27/09/2018 - 07:29
A structured reference, yes. I'm not aware of how to create one at all. I'm not sure how the site works but I can take a picture of my Excel chart for you if needed.
Excelchat Expert
27/09/2018 - 07:29
You can send your file in this chatbox by using the attachment button.
User
27/09/2018 - 07:29
Just attached it.
[Uploaded an Excel file]
Excelchat Expert
27/09/2018 - 07:30
You can send the actual Excel fie so I can work on it directly.
User
27/09/2018 - 07:31
The way my Excel file is set up, it will not let anyone else work on it, and if someone does, it'll submit a 0 for grading for me.
Excelchat Expert
27/09/2018 - 07:32
Oh ok, can you at least paste some of the data here in our shared sheet so we can identify your problem precisely and I can provide you the solution that you need?
User
27/09/2018 - 07:33
I will try.
Excelchat Expert
27/09/2018 - 07:33
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. We also currently do not support VBA/Macro solutions.
User
27/09/2018 - 07:33
Okay!
User
27/09/2018 - 07:34
I tried to paste it but it ended up only in one cell. I'm sorry.
Excelchat Expert
27/09/2018 - 07:34
Don't worry I'll just create it for you based on the image that you sent.
User
27/09/2018 - 07:35
Okay. Thank you so much.
Excelchat Expert
27/09/2018 - 07:35
No worries.
Excelchat Expert
27/09/2018 - 07:35
Give me a moment to create few rows for you.
User
27/09/2018 - 07:36
Okay.
Excelchat Expert
27/09/2018 - 07:39
Ok, do you think 3 rows are enough to identify what you need?
User
27/09/2018 - 07:39
Just so you can teach me how to do it, yes.
Excelchat Expert
27/09/2018 - 07:40
Ok, now that we have your sample data. Can you tell me more about your goal or requirement?
User
27/09/2018 - 07:40
I need to: create a formula with structured references to calculate the percentage of the Sticker Price in column E.
Excelchat Expert
27/09/2018 - 07:41
Can you tell me more about "structured references"?
User
27/09/2018 - 07:42
No, I don't even know what they mean. Lol.
User
27/09/2018 - 07:42
I'm pretty sure it's just a formula to calculate the number of stickers between column C and D.
Excelchat Expert
27/09/2018 - 07:43
No worries, we'll identify your requirement.
Excelchat Expert
27/09/2018 - 07:43
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
27/09/2018 - 07:43
Okay.
Excelchat Expert
27/09/2018 - 07:44
The only column that needs a formula is Column E, is that correct?
User
27/09/2018 - 07:44
Yes.
Excelchat Expert
27/09/2018 - 07:45
And the only information provided to you was to create a 'structured references'? No other details?
User
27/09/2018 - 07:45
Yes. There are steps to the Excel worksheet but none of the other steps include this, or how to do it.
Excelchat Expert
27/09/2018 - 07:46
Ok, Give me a moment.
User
27/09/2018 - 07:46
Ok.
Excelchat Expert
27/09/2018 - 07:49
Ok, I believe the structured reference term is an excel term. I thought it was a term for your specific school subject.
User
27/09/2018 - 07:50
Oh, no. This is Excel. The class I'm taking is on Excel.
Excelchat Expert
27/09/2018 - 07:50
Ahh, that clears so much.
User
27/09/2018 - 07:50
Lol, I am so sorry.
Excelchat Expert
27/09/2018 - 07:50
No no, no worries.
Excelchat Expert
27/09/2018 - 07:51
So a structured reference in excel is a special syntax for referencing Excel Tables.
User
27/09/2018 - 07:51
How do I do this with the sticker and sale price?
Excelchat Expert
27/09/2018 - 07:52
Wait, I'll show you.
User
27/09/2018 - 07:52
Thank you.
Excelchat Expert
27/09/2018 - 07:53
I'll send you an excel file. Give me a moment.
User
27/09/2018 - 07:53
Ok.
Excelchat Expert
27/09/2018 - 07:55
Ok, I'll give you few sample but first: What percentage do you need when you mentioned this "calculate the percentage of the Sticker Price in column E."
User
27/09/2018 - 07:55
It didn't let me know.
Excelchat Expert
27/09/2018 - 07:56
No worries, so we have sticker price and sale price.
Excelchat Expert
27/09/2018 - 07:56
Do you think it's somehow related to that?
User
27/09/2018 - 07:56
Yes, I just don't know how to create a percentage between Column C and D. I'm supposed to create a percentage from them somehow.
Excelchat Expert
27/09/2018 - 07:58
No worries, we'll get to that.
Excelchat Expert
27/09/2018 - 07:58
Here's a file sample with a structured reference sample and normal reference sample.
Excelchat Expert
27/09/2018 - 07:58
[Uploaded an Excel file]
User
27/09/2018 - 07:59
Thank you.
User
27/09/2018 - 08:00
I have it open.
Excelchat Expert
27/09/2018 - 08:00
Ok. Let me walk you through.
User
27/09/2018 - 08:00
Ok.
Excelchat Expert
27/09/2018 - 08:00
So in cell G2 - That's what we call Structured reference in Excel.
Excelchat Expert
27/09/2018 - 08:01
If you'll observe it has the same value with the Normal Reference sample.
Excelchat Expert
27/09/2018 - 08:01
G2 and I2 and both referring to the same cell.
Excelchat Expert
27/09/2018 - 08:01
Which is cell D2.
Excelchat Expert
27/09/2018 - 08:02
But, if you'll look at the formula of cell G2. You'll see that it has a different way of referencing a cell inside a table.
Excelchat Expert
27/09/2018 - 08:03
=Table1[@[Sale Price]]
User
27/09/2018 - 08:03
Ah, okay.
Excelchat Expert
27/09/2018 - 08:03
Try copying cell G2 and paste it to cell G3 and G4.
Excelchat Expert
27/09/2018 - 08:04
Were you able to do copy and paste it?
User
27/09/2018 - 08:04
I will.
Excelchat Expert
27/09/2018 - 08:05
**Were you able to copy and paste it?
User
27/09/2018 - 08:05
Yes.
Excelchat Expert
27/09/2018 - 08:05
Did you see that the structured reference remains exactly the same?
Excelchat Expert
27/09/2018 - 08:05
It's producing different results but the formula never changes?
User
27/09/2018 - 08:06
Yes.
User
27/09/2018 - 08:06
I'm still very confused though.
Excelchat Expert
27/09/2018 - 08:07
No worries, tell me which part of it is confusing for you.
Excelchat Expert
27/09/2018 - 08:09
Hi, are you still there?
Excelchat Expert
27/09/2018 - 08:11
I believe you got disconnected in our chat.
Excelchat Expert
27/09/2018 - 08:15
I'll provide a solution based on your last statement and you can come back to your account later. if it's what you need then we are all set, if not, feel free to post your question again when you have the time.
Excelchat Expert
27/09/2018 - 08:15
I believe you are confused with how the structured reference works.
Excelchat Expert
27/09/2018 - 08:17
It's showing the same formula every time but it's producing different results because it is based on the row where the formula is placed.
Excelchat Expert
27/09/2018 - 08:17
For example in cell G2, the formula is =Table1[@[Sale Price]]
Excelchat Expert
27/09/2018 - 08:18
It means that it's referencing Table1 and the column Sale Price.
Excelchat Expert
27/09/2018 - 08:18
Excel will automatically get the information in Table1 column Sale Price for the same row where the formula is entered.
Excelchat Expert
27/09/2018 - 08:19
So in cell G2, it will be getting the Sale Price for row 2 or cell D2.
Excelchat Expert
27/09/2018 - 08:20
Structured reference will only work within the same row not unless you're selecting the entire column. Let me explain.
Excelchat Expert
27/09/2018 - 08:21
in cell G2 of the file that I sent you, if you type equal sign (=) and click cell D2, a structured reference will automatically appear for you in your formula.
Excelchat Expert
27/09/2018 - 08:22
But if you click cell D3, it will just create a normal reference.
Excelchat Expert
27/09/2018 - 08:22
I hope that clears that up for you.
Excelchat Expert
27/09/2018 - 08:23
To get the percentage of sticker price and sale price, due to the lack of information I'll take a guess and will calculate the discount from sticker price versus sale price.
Excelchat Expert
27/09/2018 - 08:25
Here's your file with the percentage of discount in Column E.
[Uploaded an Excel file]
Excelchat Expert
27/09/2018 - 08:25
I also applied the discount percent in our shared sheet.
Excelchat Expert
27/09/2018 - 08:25
The formula logic is (Sale Price - Sticker Price) / Sticker Price.
Excelchat Expert
27/09/2018 - 08:26
So for example in E3. 10% discount was applied to the sticker price based on the sale price.
Excelchat Expert
27/09/2018 - 08:26
I hope we're able to answer all your questions.
Excelchat Expert
27/09/2018 - 08:27
If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. I'd really appreciate if you leave a rating and comment at the end of this session. Thank you for using Got it Pro-Excel!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
