Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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
Excelchat Expert 23/09/2018 - 06:42
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
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
Excelchat Expert 23/09/2018 - 06:49
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.