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
I can help you with that problem.
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
Please share the file
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?
[Uploaded an Excel file]
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
[Uploaded an Excel file]
Excelchat Expert
19/09/2018 - 05:02
Please check
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
Please allow me a minute
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.
