# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

In cell H8, insert a nested logical function to display the required pet deposit for each unit. If the unit has two or more bedrooms (C8) AND was remodeled less than 10 years ago (cell H3), the deposit is \$275 (cell H4); if not, the deposit is \$200 (cell H5). Use relative and mixed references correctly. The pet deposit for Unit 101 is \$200.
Solved by O. F. in 24 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/09/2018 - 04:50
Welcome to Excelchat, I see that your question is about Nested If / Logical function.
Excelchat Expert 19/09/2018 - 04:50
Excelchat Expert 19/09/2018 - 04:50
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 19/09/2018 - 04:50
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User 19/09/2018 - 04:50
yes...i can send you the necessary spread sheet I am working from
Excelchat Expert 19/09/2018 - 04:51
Excelchat Expert 19/09/2018 - 04:51
That would be great.
Excelchat Expert 19/09/2018 - 04:54
Let me know once you share the file.
Excelchat Expert 19/09/2018 - 04:55
I have build up the sample formula based on the information in the query.
Excelchat Expert 19/09/2018 - 04:55
But will finalize it once you will share the file.
Excelchat Expert 19/09/2018 - 04:56
For instance How is bedroom listed in C8 or how is remodeling listed in H3. I will amend the formula accordingly.
User 19/09/2018 - 04:56
ok..when I try to cut and paste I does not work. Do you think I need to do it step by step in the box that pops up so you can put each field in by category?
User 19/09/2018 - 04:56
I may be missing colons or semi-colons
Excelchat Expert 19/09/2018 - 04:57
I’m going to amend the formula in the file you have shared according the other info.
Excelchat Expert 19/09/2018 - 04:57
This should take me few minutes to solve. If prompted for extension of session, please do so.
User 19/09/2018 - 04:58
ok..will do
Excelchat Expert 19/09/2018 - 04:58
formula is correct.
Excelchat Expert 19/09/2018 - 04:58
I have a question.
User 19/09/2018 - 04:58
sure
Excelchat Expert 19/09/2018 - 04:58
The last remodel date is in column F
Excelchat Expert 19/09/2018 - 04:59
So we need to calculate Column G and find the years since remodel.
Excelchat Expert 19/09/2018 - 05:00
and then check if the remodel is <10 years.
User 19/09/2018 - 05:00
looking for the formula for the pet deposti
User 19/09/2018 - 05:00
*deposit
Excelchat Expert 19/09/2018 - 05:00
that would be the correct procedure.
Excelchat Expert 19/09/2018 - 05:00
Ok. Please allow me a minute.
Excelchat Expert 19/09/2018 - 05:02
Here it is
Excelchat Expert 19/09/2018 - 05:02
Excelchat Expert 19/09/2018 - 05:03
I have calculated Column G using the formula
Excelchat Expert 19/09/2018 - 05:03
=YEAR(TODAY())-YEAR(F8)
Excelchat Expert 19/09/2018 - 05:04
today() gives today's date and =YEAR(TODAY()) will give present year from the today's date and then subtracting the last remodel year in Column F
Excelchat Expert 19/09/2018 - 05:04
So we get the Column G, years since remodel.
Excelchat Expert 19/09/2018 - 05:05
Now for h8 - Pet Deposit
Excelchat Expert 19/09/2018 - 05:05
=IF(AND(C8>=2,G8<10),\$H\$4,\$H\$5)
Excelchat Expert 19/09/2018 - 05:06
If these 2 condition C8>=2 (bedroom >=2) and G8<10 (remodel <10 years) when both this condition will satisfy then we will get H4 otherwise H5.
Excelchat Expert 19/09/2018 - 05:06
H4 and H5 is absolute so that when we copy this formula down in other cells it always refer them.
Excelchat Expert 19/09/2018 - 05:06
I hope I am making sense.
User 19/09/2018 - 05:08
got it. great..git it. I did notice that where you typed in commas there semi-colons
User 19/09/2018 - 05:08
That is where the disconnect is for me...learning where to put them
Excelchat Expert 19/09/2018 - 05:09
The semicolon or comma in the formula generally vary for the region.
Excelchat Expert 19/09/2018 - 05:09
In Europe the excel is generally set up to use semicolon (;)
Excelchat Expert 19/09/2018 - 05:10
but in other parts it set to use comma (,) in the formula.
User 19/09/2018 - 05:11
ok...for some reason my formulas compute with semi-colons instead of commas..can we go over the recommendation column..here are the directions..In cell I8, enter a nested logical function to display Need to Remodel if the apartment is unoccupied (No) AND was last remodeled more than 10 years ago (H3). For all other apartments, display No Change. Although Unit 101 was last remodeled over 10 years ago, the recommendation is No Change because the unit is occupied.
Excelchat Expert 19/09/2018 - 05:12
Excelchat Expert 19/09/2018 - 05:12
Oh, this is another problem.
Excelchat Expert 19/09/2018 - 05:12
As informed earlier, our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 19/09/2018 - 05:12
oohhhhh ok..im sorry..
Excelchat Expert 19/09/2018 - 05:13
I am afraid you need to post another problem with that.
User 19/09/2018 - 05:13
thank you for your help.. I did learn something
User 19/09/2018 - 05:13
no prob
Excelchat Expert 19/09/2018 - 05:13
I will try my level best to get back for you on the problem if you post this next.
Excelchat Expert 19/09/2018 - 05:13
Does this solution solve your problem?
Excelchat Expert 19/09/2018 - 05:13
Thanks for coming to Excelchat.
Excelchat Expert 19/09/2018 - 05:14
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.
User 19/09/2018 - 05:14
yes it..i just had to put in the semi-colons for some reason and it worked like a charm
User 19/09/2018 - 05:14
ok bye
Excelchat Expert 19/09/2018 - 05:14
Great.

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.