Excel Functions and Formulas - Microsoft Community
i in excel class , having problem figuring out how functions in class.
for instance:
| unit # | apartment complex | # bed | rental price | occupied | last remodel | pet deposit | recommendation |
| 1101 | turning leaf circle | 1 | $ 765.00 | yes | 2005 | ||
| 1102 | turning leaf circle | 1 | $ 765.00 | yes | 2003 | ||
| 1103 | turning leaf circle | 2 | $ 895.00 | no | 1999 | ||
| 1104 | turning leaf circle | 2 | $ 895.00 | yes | 2005 | ||
| 1105 | turning leaf circle | 3 | $ 1,225.00 | no | 2007 | ||
| 1106 | turning leaf circle | 3 | $ 1,225.00 | yes | 2005 | ||
| 1107 | turning leaf circle | 2 | $ 895.00 | no | 2005 | ||
| 1108 | turning leaf circle | 2 | $ 895.00 | yes | 2009 | ||
| 1109 | turning leaf circle | 3 | $ 1,225.00 | no | 2008 | ||
| 1110 | turning leaf circle | 3 | $ 1,225.00 | yes | 2009 | ||
| 1201 | turning leaf circle | 1 | $ 775.00 | yes | 2011 | ||
| 1202 | turning leaf circle | 1 | $ 775.00 | yes | 2010 | ||
| 1203 | turning leaf circle | 2 | $ 875.00 | yes | 2011 | ||
| 1204 | turning leaf circle | 2 | $ 875.00 | no | 2011 | ||
| 1205 | turning leaf circle | 3 | $ 1,050.00 | yes | 2005 | ||
| 1206 | turning leaf circle | 3 | $ 1,050.00 | no | 2005 | ||
| 1207 | turning leaf circle | 2 | $ 875.00 | yes | 2000 | ||
| 1208 | turning leaf circle | 2 | $ 875.00 | yes | 2000 | ||
| 1209 | turning leaf circle | 3 | $ 1,050.00 | no | 2005 | ||
| 1210 | turning leaf circle | 3 | $ 1,050.00 | yes | 2005 | ||
| 1301 | piedra springs | 1 | $ 875.00 | no | 2008 | ||
| 1302 | piedra springs | 1 | $ 875.00 | no | 2001 | ||
| 1303 | piedra springs | 1 | $ 900.00 | no | 2001 | ||
| 1304 | piedra springs | 1 | $ 900.00 | yes | 2001 | ||
| 1305 | piedra springs | 2 | $ 1,355.00 | no | 2005 | ||
| 1306 | piedra springs | 2 | $ 1,355.00 | yes | 2005 | ||
| 1307 | piedra springs | 2 | $ 1,355.00 | yes | 2011 | ||
| 1308 | piedra springs | 2 | $ 1,355.00 | no | 2011 | ||
| 1401 | eagles domain road | 1 | $ 875.00 | no | 2005 | ||
| 1402 | eagles domain road | 2 | $ 950.00 | no | 2011 | ||
| 1403 | eagles domain road | 3 | $ 1,355.00 | yes | 2011 | ||
| 1404 | eagles domain road | 1 | $ 875.00 | no | 2011 | ||
| 1405 | eagles domain road | 2 | $ 950.00 | yes | 2011 | ||
| 1406 | eagles domain road | 3 | $ 1,355.00 | no | 2002 | ||
| 1407 | eagles domain road | 2 | $ 975.00 | yes | 2002 | ||
| 1408 | eagles domain road | 2 | $ 975.00 | yes | 2002 | ||
| 1501 | hickory ridge villas | 1 | $ 550.00 | yes | 2011 | ||
| 1502 | hickory ridge villas | 1 | $ 550.00 | no | 2011 | ||
| 1503 | hickory ridge villas | 1 | $ 550.00 | no | 2003 | ||
| 1504 | hickory ridge villas | 1 | $ 550.00 | yes | 2001 | ||
| 1505 | hickory ridge villas | 2 | $ 850.00 | no | 2003 | ||
| 1506 | hickory ridge villas | 2 | $ 850.00 | yes | 2011 | ||
| 1507 | hickory ridge villas | 2 | $ 850.00 | no | 2011 | ||
| 1601 | cypress trail | 1 | $ 1,225.00 | yes | 2005 | ||
| 1602 | cypress trail | 1 | $ 1,225.00 | yes | 2005 | ||
| 1603 | cypress trail | 2 | $ 1,355.00 | no | 2005 | ||
| 1604 | cypress trail | 2 | $ 1,355.00 | yes | 2011 | ||
| 1605 | cypress trail | 3 | $ 1,675.00 | no | 2011 | ||
| 1606 | cypress trail | 3 | $ 1,695.00 | yes | 2005 | ||
| 1607 | cypress trail | 3 | $ 1,710.00 | no | 2005 |
in above spreadsheet, figure out apartrments need pet deposit , due remodeling. basis:
- select search worksheet , pet deposit column. create formula determine required pet deposit each unit. if unit has 2 or more bedrooms , remodeled after 2006, deposit $150; if not, $100.
- the recommendation column needs nested function indicate remodeling status. if apartment unoccupied , has not been remodeled before 2006, display "please remodel" in recommendation column. display " ~ no change" apartments not meet former criteria.
- make sure each field has appropriate professional formatting titles, headers, currency, percent, , forth. worksheets need readable, clean, , professional. please let spell-check work you; use feature check spelling errors.
i have been in excel since came out , have never had this. have asked no given. begging please me.
here screenshot
1) in g2 have =if(and(c2>1,f2>=2006),150,100), , format cell currency
2)this text confusing: and has not been remodeled before 2006, why not and last remodelled before 2006? how read this.
the question asks nest if 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 several more options
but use of , makes simpler function:
=if(and(e2="no",f2<2006),"please remodel"," ~ no change")
best wishes
Office / Excel / Windows 10 / Office 2016
Comments
Post a Comment