microsoft excel chapter 7 problem! please help

I am having trouble with 1-8. I really appreciate if you guys can help me. I tried to do them but my answers are wrong

thank you. As you can tell im new to this forum and its frustrating working on a homework for hours and not getting anywhere. BUT i am getting better at it. I figure out how to do the first 1-7 and im stuck on #8. Maybe you can help me guide through it.


#8.In cell C7, enter a DCOUNTA function to calculate the number of apartments to remodel.



Unit # Development # Bed Rental Occupied Last Remodel Recommendation
103 Rolling Meadows 2 850 No 1999 Need to remodel
406 Mountaintop View 3 1,200 No 2002 Need to remodel
505 Sunset Valley 2 700 No 2003 Need to remodel
607 Oak Tree Living 3 1,500 No 2004 Need to remodel



I will continue to work on it

This is actually a very good exercise with clear instructions. I have completed it in some 20 minutes and would like to help you BUT I will not give you the completed task. As my friend FDibbens pointed out we are here to facilitate your learning and as I am a Tutor, I would not like to think one of my students came onto this forum just to have their assignment completed by another person. Having someone else complete it for you means you will not learn the process, you'll only get the answer and I'm sure you would rather be a learner in life than one who simply gives a right response. And the motto I put up for all students to read is "Excellence is a better teacher than mediocrity" - so strive for excellence and strive for learning the process.

All the best and please feel free to ask about any specific point or any particular formula.

Cheers,

its number #9 In cell C8, enter a database function to calculate the total lost rent for the month.
sorry

Thank you Ursul. This assignment is kicking my butt and i do like learning so i could get better at it. The class im taking is online and so far this is the only chapter i have struggle with. i will continue to work on the homework. thank you

Can you help me with the type of formula i need for question 9, i think its DMIN..

Hi again. If you have correctly entered the data according to Step 7, then the formula for Step 8 will prove to be quite easy, BUT make sure you have entered the Criteria correctly. Hint: If you press the F1 key and type in the Help dialog box "DCOUNTA" then an example will be shown that will assist you with this step (or alternatively, you could enter in cell C7: =DCOUNTA( then click on the fx button [that is the 'Insert Function' to get the dialog box to open, then at the bottom of this box in blue it states "Help on this function" and this will likewise take you to the Help page]. Your formula should look something like this: =DCOUNTA(A13:G31,A13,A2:H3), but only if you have coirrectly completed Step 7. All the best

In reply to Post #7, I chose to use the SUMIFS function, with one criteria looking for Units with >=2 rooms, and the other criteria looking at column G "Need to remodel" - have a try and let me know what you come up with.

Thank you and that is what i got for step 8 =DCOUNTA(A13:G31,"Recommendation",Criteria), for step 9 do i use DMIN for the calculate the total lost rent for the month.

OK our Posts seem to be overlapping - see my Post #9 in response to your #10

Oh got it. thank you for your help. I finish the step 8,9,10,11,12,13. Now im stuck on step 14 Insert a formula in cell E4 to calculate the periodic monthly rate. This one is an easy one but i forgot how to do it. Do i need to use a formula?

Do i use PMT?

You're doing very well! OK Step 14: to calculate the Periodic monthly rate take the APR (Annual Interest rate) and divide it by the number of payments which in this case is B5 * B4 which is the formula you should have in cell E3. So cell E4 =B6/B4. Step 15 (cell E5) requires you use the PMT function

I put B6/B4 in E4 and i got $0.00. i change the number format to general and got 0.004791667 is that correct? And yes i do have B5*B4 in E3 thank you!!
ahn

For step 15. I use PMT rate .0575, Nper 360, PV -475000 is that correct?

Your formula in E4 is correct but change it to "Percentage" and you'll get 0.48%

Oh I should have added "Percentage" 2 decimal places - sorry. To get that use the shortcut keys "CTRL" + 1 i.e. CTRL+1 (note the 1 is above the 'Q' key - not the number 1 on a number keypad!!) at the same time and the Format Cells dialog box opens, select the first tab "Number", select "Percentage" and select Decimal Places: scroll to "2"

NO thats my fault i should know to change it to percentage. For step 15. I use PMT rate .0575, Nper 360, PV -475000 is that correct?

Hey n03c03 how are you doing??? Have you completed the whole exercise yet and if not where are you up to?

Hey Ursul I am on step 15(Insert a function in cell E5 to calculate the monthly payment. Ensure that the function returns a positive value). i think i did it right i use the PMT Rate .0575(B6), Nper 360(E6), pv 475000(-E2) Is that correct?

OK, no response so I'm out of here for now. All the best with the assignment and let me know how you get on. If you are still having problems then get back to me or if you have finished it and think all is well then PLEASE mark this thread as SOLVED and if you like you can click on my *Add Reputation (bottom left of this post). Take care and all the best.

i dont know why you cant see my post i was asking for step 15 for help. Thank you Ursul

OK I'm still here - don't know why I couldn't see your posts.

Cell E5 should read: =PMT(E4,E3,-E2) ... assuming you have:

E3 = 360
E4 = 0.48%

You could use cell B6 (5.75%) in the PMT function but then you would have to divide it by 12 - to get it back to a monthly figure and as this step should already have been done in cell E4 then it's easier to use that cell.

If you're still working on this then I'll stick around for a while longer

Thank you alot Ursul you help me alot today and i submit my hw in and got an 81.3% all cause of you. Step 2 is worth 10pts and i dont know how i got that wrong. I hate the IF formula

Insert functions in the Pet Deposit column of the Summary worksheet to calculate the required pet deposit for each unit. If the unit has two or more bedrooms and was remodeled after 2008 the deposit is $125, if not it is $75.

This is the IF formula I used for Step 2: =IF(AND(Database!C14>=2,Database!F14>=2005),125,75)

Well done, so we're finished I assume. If you have submitted it then we must be finished here. If you want I can upload my finished version. Note there are often many ways (and formulas) for obtaining the desired result.

Please mark this Thread as "SOLVED" and as mentioned earlier you could do me a favor by clicking on the *Add Reputation but it doesn't matter if you don't. I'm not on this site for status just to help fellow Excel users and also to learn from others better than me.

Been fun helping you and all the best for your further studies.

is there a reason why you would use the database worksheet when the information is on the summary worksheet?

If I was using this workbook then all Absolute data should be/is on the Database. The Database should be the place where all information is stored. The summary is a 'working sheet' for want of a better term and should draw all it's information and outcomes from the absolute source. I am currently managing a client's portfolio and the spreadsheet has some 35 sheets, recording and tracking share movement, but all the information is held on one sheet and the other sheets draw their information from the one source. Any changes in the 'source' and I only have to go to one sheet (and not all 35 of them!!). The database should be the hub of the operation, and particularly as in this case the Summary page information (in the range A7:F24) is not linked to the Database but copied or perhaps even created by inputting the figures manually, then mistakes can happen ... therefore always work from your database.

Thank you so much ursul. You are the best!!!!

My pleasure n03c03. I definitely am not the best, but thank you for the compliment.

Take care and enjoy your learning journey!

I need help with this can someone help me with numbers 8-18