Question description:
This user has given permission to use the problem statement for this
blog.
Write an Excel formula to determine the packaging and loading costs for this item based on its box size and weight. This cost should also include the box price but do not include any surcharges for fragile or high value items. Copy the formula down the column to calculate this value for each corresponding item.
Solved by K. Y. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/10/2018 - 02:22
Hello
Excelchat Expert
10/10/2018 - 02:22
Welcome to Excelchat, I see that your question is about formula.
Excelchat Expert
10/10/2018 - 02:22
I can help you with that problem.
Excelchat Expert
10/10/2018 - 02:22
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.
Excelchat Expert
10/10/2018 - 02:23
Please tell me more on your requirement.
Excelchat Expert
10/10/2018 - 02:24
Are you still there?
User
10/10/2018 - 02:25
Yes my question is: Determine the Packaging and Loading costs in cell packlist!F3. Write an Excel formula to determine the packaging and loading costs for this item based on its box size and weight. This cost should also include the box price but do not include any surcharges for fragile or high value items. Copy the formula down the column to calculate this value for each corresponding item.
Excelchat Expert
10/10/2018 - 02:26
Please allow me a minute to relate it with the sample sheet you have made.
User
10/10/2018 - 02:27
Thank you
Excelchat Expert
10/10/2018 - 02:31
Ok.
Excelchat Expert
10/10/2018 - 02:32
So we need to calculate the value in F3 using the LBS (weight) according to the weight on boxes tab.
Excelchat Expert
10/10/2018 - 02:33
Please allow me a minute to do so.
Excelchat Expert
10/10/2018 - 02:34
=if(C3<100,hlookup(B3,boxes!B2:K9,7,false),hlookup(B3,boxes!B2:K9,8,false))
Excelchat Expert
10/10/2018 - 02:34
this is the formula I have used.
Excelchat Expert
10/10/2018 - 02:34
Please review.
Excelchat Expert
10/10/2018 - 02:35
I have used IF formula along with HLOOKUP function.
Excelchat Expert
10/10/2018 - 02:35
Please confirm this solves your problem then I will start explaining how this formula works.
Excelchat Expert
10/10/2018 - 02:35
IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert
10/10/2018 - 02:36
awaiting your reply.
User
10/10/2018 - 02:38
On my main worksheet , formula works for packlist!F3 but when I copy it down, it says #N/A.
Excelchat Expert
10/10/2018 - 02:38
Ok.
Excelchat Expert
10/10/2018 - 02:39
I have amended the formula but I think your cursor was on the same cell Let me amend it again.
Excelchat Expert
10/10/2018 - 02:40
=if(C3<100,hlookup(B3,boxes!$B$2:$K$9,7,false),hlookup(B3,boxes!$B$2:$K$9,8,false))
User
10/10/2018 - 02:40
When I copied the formula for F4, it worked for the rest of the cells.
Excelchat Expert
10/10/2018 - 02:40
Is it working or not working?
User
10/10/2018 - 02:41
It's working now. Thank you!
Excelchat Expert
10/10/2018 - 02:41
Great.
Excelchat Expert
10/10/2018 - 02:41
Do you want to understand how this formula works?
User
10/10/2018 - 02:42
Yes please
Excelchat Expert
10/10/2018 - 02:42
Ok
Excelchat Expert
10/10/2018 - 02:42
So I have used 2 functions here IF and HLOOKUP
Excelchat Expert
10/10/2018 - 02:42
IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert
10/10/2018 - 02:43
We test something under logical expression and if the result is true then it displays value_if_true otherwise it will display value_if_false.
Excelchat Expert
10/10/2018 - 02:44
logical_expression is C3<100
Excelchat Expert
10/10/2018 - 02:44
VALUE_IF_TRUE is hlookup(B3,boxes!$B$2:$K$9,7,false)
Excelchat Expert
10/10/2018 - 02:45
value _if_false is hlookup(B3,boxes!$B$2:$K$9,8,false)
Excelchat Expert
10/10/2018 - 02:45
Now let me explain how HLOOKUP works.
Excelchat Expert
10/10/2018 - 02:47
HLOOKUP(look_up_value, table_array, row_index_num, [range_look_up])
Excelchat Expert
10/10/2018 - 02:47
Lookup value is B3
Excelchat Expert
10/10/2018 - 02:47
table_array is - boxes!$B$2:$K$9
Excelchat Expert
10/10/2018 - 02:49
Now for row_index_num, row2 becomes 1, row 3 becomes 2 and so on. This is because our table_array starts with row 2.
Excelchat Expert
10/10/2018 - 02:49
since if the weight is less than 100 we will use row index 7 otherwise we will sue row index 8.
Excelchat Expert
10/10/2018 - 02:50
Does that make sense?
Excelchat Expert
10/10/2018 - 02:50
Does this solution solve your problem?
User
10/10/2018 - 02:50
Yes it does make sense. Thank you.
Excelchat Expert
10/10/2018 - 02:50
Do you have any other questions about this problem and the solution?
Excelchat Expert
10/10/2018 - 02:51
Thanks for coming to Excelchat. Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side of this chat window and requesting you to provide good feedback and review so that I can continue my services to others.
User
10/10/2018 - 02:53
No, I don't have anymore questions. Thank you for your help and I'll make sure to provide good feedback.
Excelchat Expert
10/10/2018 - 02:53
Thank you and have a great day ahead.
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.
