Excel Functions and Formulas

Question

I am in an Excel class and I am having problem figuring out how to do certain functions in this class.

For instance:

Unit #	Apartment Complex	# Bed	Rental Price	Occupied	Last Remodel	Pet Deposit	Recommendation
1101	Turning Leaf Circle	1	$        <br>765.00	Yes	2005
1102	Turning Leaf Circle	1	$        <br>765.00	Yes	2003
1103	Turning Leaf Circle	2	$        <br>895.00	No	1999
1104	Turning Leaf Circle	2	$        <br>895.00	Yes	2005
1105	Turning Leaf Circle	3	$    <br>1,225.00	No	2007
1106	Turning Leaf Circle	3	$    <br>1,225.00	Yes	2005
1107	Turning Leaf Circle	2	$        <br>895.00	No	2005
1108	Turning Leaf Circle	2	$        <br>895.00	Yes	2009
1109	Turning Leaf Circle	3	$    <br>1,225.00	No	2008
1110	Turning Leaf Circle	3	$    <br>1,225.00	Yes	2009
1201	Turning Leaf Circle	1	$        <br>775.00	Yes	2011
1202	Turning Leaf Circle	1	$        <br>775.00	Yes	2010
1203	Turning Leaf Circle	2	$        <br>875.00	Yes	2011
1204	Turning Leaf Circle	2	$        <br>875.00	No	2011
1205	Turning Leaf Circle	3	$    <br>1,050.00	Yes	2005
1206	Turning Leaf Circle	3	$    <br>1,050.00	No	2005
1207	Turning Leaf Circle	2	$        <br>875.00	Yes	2000
1208	Turning Leaf Circle	2	$        <br>875.00	Yes	2000
1209	Turning Leaf Circle	3	$    <br>1,050.00	No	2005
1210	Turning Leaf Circle	3	$    <br>1,050.00	Yes	2005
1301	Piedra Springs	1	$        <br>875.00	No	2008
1302	Piedra Springs	1	$        <br>875.00	No	2001
1303	Piedra Springs	1	$        <br>900.00	No	2001
1304	Piedra Springs	1	$        <br>900.00	Yes	2001
1305	Piedra Springs	2	$    <br>1,355.00	No	2005
1306	Piedra Springs	2	$    <br>1,355.00	Yes	2005
1307	Piedra Springs	2	$    <br>1,355.00	Yes	2011
1308	Piedra Springs	2	$    <br>1,355.00	No	2011
1401	Eagles Domain Road	1	$        <br>875.00	No	2005
1402	Eagles Domain Road	2	$        <br>950.00	No	2011
1403	Eagles Domain Road	3	$    <br>1,355.00	Yes	2011
1404	Eagles Domain Road	1	$        <br>875.00	No	2011
1405	Eagles Domain Road	2	$        <br>950.00	Yes	2011
1406	Eagles Domain Road	3	$    <br>1,355.00	No	2002
1407	Eagles Domain Road	2	$        <br>975.00	Yes	2002
1408	Eagles Domain Road	2	$        <br>975.00	Yes	2002
1501	Hickory Ridge Villas	1	$        <br>550.00	Yes	2011
1502	Hickory Ridge Villas	1	$        <br>550.00	No	2011
1503	Hickory Ridge Villas	1	$        <br>550.00	No	2003
1504	Hickory Ridge Villas	1	$        <br>550.00	Yes	2001
1505	Hickory Ridge Villas	2	$        <br>850.00	No	2003
1506	Hickory Ridge Villas	2	$        <br>850.00	Yes	2011
1507	Hickory Ridge Villas	2	$        <br>850.00	No	2011
1601	Cypress Trail	1	$    <br>1,225.00	Yes	2005
1602	Cypress Trail	1	$    <br>1,225.00	Yes	2005
1603	Cypress Trail	2	$    <br>1,355.00	No	2005
1604	Cypress Trail	2	$    <br>1,355.00	Yes	2011
1605	Cypress Trail	3	$    <br>1,675.00	No	2011
1606	Cypress Trail	3	$    <br>1,695.00	Yes	2005
1607	Cypress Trail	3	$    <br>1,710.00	No	2005

In the above spreadsheet, I figure out which apartrments need a pet deposit and then which are due for remodeling.  This is the basis:

1. Select the Search worksheet and then the Pet Deposit column. Create a formula to determine the required pet deposit for each unit. If the unit has two or more bedrooms and was remodeled after 2006, the deposit is $150; if not, it is $100.
2. The Recommendation column needs a nested function to indicate the remodeling status. If the apartment is unoccupied and has not been remodeled before 2006, then display "Please remodel" in the Recommendation column. Display " ~ No Change" for apartments that do not meet the former criteria.
3. Make sure each field has the appropriate professional formatting for titles, headers, currency, percent, and so forth. Your worksheets need to be readable, clean, and professional. Please let spell-check work for you; use this feature to check for spelling errors.

I have been in Excel since it came out and have never had to do this.  I have asked for help with no help given.  So I am now begging please help me.

Answer 1

Here is a screenshot

1. In G2 we have =IF(AND(C2>1,F2>=2006),150,100), and format cell Currency

2)This text is confusing: and has not been remodeled before 2006, Why not and was last remodelled before 2006? That is how I read this.

The question asks for nest IF so use

=IF(E2="No",IF(F2<2006,"Please remodel","~ No Change")," ~ No Change")

Or

=IF(E2="Yes"," ~ No Change",IF(F2>=2006," ~ No Change","Please remodel"))

There are several more options

But a the use of AND makes for a simpler function:

=IF(AND(E2="No",F2<2006),"Please remodel"," ~ No Change")

best wishes