Question description:
This user has given permission to use the problem statement for this
blog.
I'm not sure how to fully use the IF statement
Solved by Z. H. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/09/2018 - 06:30
Hello. Welcome to Excelchat, I see that your question is about the usage of IF statement
Excelchat Expert
23/09/2018 - 06:30
I can help you with that problem.
Excelchat Expert
23/09/2018 - 06:30
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.
User
23/09/2018 - 06:30
Yeah would it be possible if I sent you the excel document so that you know what I'm talking about?
Excelchat Expert
23/09/2018 - 06:30
Yes, that would be great.
User
23/09/2018 - 06:31
Okay that's cool.
Excelchat Expert
23/09/2018 - 06:31
Please share the file.
Excelchat Expert
23/09/2018 - 06:32
Please use the button on right of this chat window - Clip icon to attach the file and send it.
User
23/09/2018 - 06:33
[Uploaded an Excel file]
User
23/09/2018 - 06:33
This is super confusing to me.
Excelchat Expert
23/09/2018 - 06:33
I am on the file.
Excelchat Expert
23/09/2018 - 06:34
Please tell me the issue you are facing.
User
23/09/2018 - 06:34
In the Profit Analysis worksheet:
Expenses costs include maintenance, cleaning, and administrative expenses associated for a car rental. The Expenses for one rental transaction will be determined from the values specified in the worksheet. DO NOT enter the values in this column on a cell by cell basis.
a. In the first cell directly under the Expenses label, calculate the total Expenses that corresponds to the number of cars rented IN THAT ROW (NumCars) using an "IF" statement to as part of the formula to determine the value of Expenses for one vehicle.
b. First, use the "IF" statement to determine the single vehicle Expenses and second, edit the cell contents to multiply it by the NumCars so that the Expenses value represents the total Expenses costs for the Number of Cars (NumCars) rented in a given row.
c. Use the absolute cell address for the numerical Expenses value for ONE vehicle which should then be multiplied by the number of cars in each row.
d. Adjust column width as needed to eliminate truncation after format adjustments..
Note: the use of absolute addresses allows those Expenses values to be changed and then by recalculation, update the entire Excel table.
User
23/09/2018 - 06:34
This is the problem. Like it don't even know where to start.
Excelchat Expert
23/09/2018 - 06:34
Please allow me a minute to read it and process it
User
23/09/2018 - 06:35
Thank you.
Excelchat Expert
23/09/2018 - 06:36
Ok. So let us take it step by step.
Excelchat Expert
23/09/2018 - 06:36
As mentioned earlier
Excelchat Expert
23/09/2018 - 06:36
that our policy is 1 problem per session
Excelchat Expert
23/09/2018 - 06:36
so let's start with problem a
Excelchat Expert
23/09/2018 - 06:36
a. In the first cell directly under the Expenses label, calculate the total Expenses that corresponds to the number of cars rented IN THAT ROW (NumCars) using an "IF" statement to as part of the formula to determine the value of Expenses for one vehicle.
User
23/09/2018 - 06:37
Okay
Excelchat Expert
23/09/2018 - 06:37
so we need to calculate the total expenses for each row.
Excelchat Expert
23/09/2018 - 06:37
I have 1 question.
Excelchat Expert
23/09/2018 - 06:38
There are 2 types of expenses give - 1 for Hybrid and another for Non hybrid.
Excelchat Expert
23/09/2018 - 06:38
so where ever column D - CarClass says hybrid it comes under hybrid expenses and rest all are Non-Hybrid?
User
23/09/2018 - 06:39
Yea there are only Non-Hybrid and Hybrid cars.
Excelchat Expert
23/09/2018 - 06:39
Ok
Excelchat Expert
23/09/2018 - 06:40
I’m going to build the formula for the Expenses. This should take me few minutes to solve. If prompted for extension of session, please do so.
User
23/09/2018 - 06:41
I will definitely extend the session.
Excelchat Expert
23/09/2018 - 06:41
Here it is
[Uploaded an Excel file]
Excelchat Expert
23/09/2018 - 06:42
Please check the file
Excelchat Expert
23/09/2018 - 06:42
=IF([@CarClass]="Hybrid",[@[ NumCars ]]*$O$1,[@[ NumCars ]]*$O$2)
Excelchat Expert
23/09/2018 - 06:42
is is the formula I have used.
Excelchat Expert
23/09/2018 - 06:42
this*
Excelchat Expert
23/09/2018 - 06:42
Should I explain how IF formula works?
Excelchat Expert
23/09/2018 - 06:43
=IF(logical_test, value_if_true, value_if_false)
User
23/09/2018 - 06:43
What on earth? How did you get all that? lol
Excelchat Expert
23/09/2018 - 06:43
in this case logical test is [@CarClass]="Hybrid"
User
23/09/2018 - 06:44
I know we have a limit to one question but is there a way I can get more
User
23/09/2018 - 06:44
?
Excelchat Expert
23/09/2018 - 06:44
so if Car Class is Hybrid, then it will multiply the Number of Cars with O1 otherwise with O2.
Excelchat Expert
23/09/2018 - 06:44
I am really sorry. I want to help you but I am bound by the policy of the paltform.
Excelchat Expert
23/09/2018 - 06:44
platform.
User
23/09/2018 - 06:44
Oh! I get it.
Excelchat Expert
23/09/2018 - 06:45
you have to post another problem and I will surely assist you again.
User
23/09/2018 - 06:45
So was that just question A.?
Excelchat Expert
23/09/2018 - 06:45
Yes
User
23/09/2018 - 06:46
Since you already are helping me out, how do I come back to you with my next problem?
Excelchat Expert
23/09/2018 - 06:46
Please wait, I read the other problem too
User
23/09/2018 - 06:47
I feel like questions B. and C. are all working together?
Excelchat Expert
23/09/2018 - 06:47
Yes, correct
Excelchat Expert
23/09/2018 - 06:47
that is why I requested you to give me a minute to go through all
User
23/09/2018 - 06:48
Okay thank you.
Excelchat Expert
23/09/2018 - 06:48
Please use this file
[Uploaded an Excel file]
Excelchat Expert
23/09/2018 - 06:49
This address all your problems
Excelchat Expert
23/09/2018 - 06:49
I have amended the formula to this
Excelchat Expert
23/09/2018 - 06:49
=IF([@CarClass]="hybrid",$O$1,$O$2)*[@[ NumCars ]]
Excelchat Expert
23/09/2018 - 06:50
i this formula the logical test is same that is we are checking if car class is Hybrid, if it hybrid we will get O1($100) otherwise we will get O2 ($50) and then multiplying it with Num of cars column.
Excelchat Expert
23/09/2018 - 06:51
I used absolute reference as asked in part c of the query by using $O$1 AND $O$2.
Excelchat Expert
23/09/2018 - 06:51
Does this solution solve your problem?
User
23/09/2018 - 06:51
Okay this is making a little more sense now.
Excelchat Expert
23/09/2018 - 06:51
Great.
User
23/09/2018 - 06:52
So the absolute cell addresses are the ones with the $ sign in front.
Excelchat Expert
23/09/2018 - 06:52
Yes
Excelchat Expert
23/09/2018 - 06:52
absolute reference means it will always refer to that cell only.
Excelchat Expert
23/09/2018 - 06:52
so the formula will always take the price from O1 and O2
User
23/09/2018 - 06:53
Okay I'm understanding this a little more. Sorry, my professor is not around and no one knows excel around me.
Excelchat Expert
23/09/2018 - 06:53
I am glad I could help.
Excelchat Expert
23/09/2018 - 06:54
Please feel free to ask me any question you have regarding this problem.
User
23/09/2018 - 06:55
Okay so the next steps are pretty simple like adjusting columns and font.
User
23/09/2018 - 06:55
Wait so you're willing to walk me through more?
Excelchat Expert
23/09/2018 - 06:56
with this formula, Yes.
Excelchat Expert
23/09/2018 - 06:56
if you have any query regarding this formula, I am here to assist.
Excelchat Expert
23/09/2018 - 06:56
But for new problem you need to post new query.
Excelchat Expert
23/09/2018 - 06:57
The width part of the column is also taken care of.
Excelchat Expert
23/09/2018 - 06:57
So the problem you have shared is completely taken care of with this solution.
User
23/09/2018 - 06:58
Okay thank you. I will be moving on to the next and last few questions. If I have an issue I'll post a query.
Excelchat Expert
23/09/2018 - 06:58
Great.
Excelchat Expert
23/09/2018 - 06:58
Thanks for coming to Excelchat.
Excelchat Expert
23/09/2018 - 06:58
Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side and requesting you to provide good feedback and review so that I can continue my services to others.
Excelchat Expert
23/09/2018 - 06:59
Please go ahead and end the session.
Excelchat Expert
23/09/2018 - 06:59
Thank you and have a great day ahead.
User
23/09/2018 - 06:59
Okay thank you.
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.
