Jump to content
need help in Ecxel Project
Asked by amer gorgees
Posted Nov 06 2011 10:52 AM
I have a project I am working in it now,but I have problem with it...
I attached the instractio of the project and I need somebody help me to solve it,spicialy the steps from(a-i):
b- Delete the contens of cell (A7) and look at the #NUM! errors.
c- Click cell(A10) and select (Formulas tab>Logical>AND to do the following in the function arguments dialog box:
* Logical 1 box (A4>0)
* Logical 2 box (A5>0)
* Logical 3 box (A6>0)
* Logical 4 box (A7>0)
* Logical 5 box (A8>0) and OK.
now the result is FALSE because we deleted the contens of cell A7.
d- Assign the range name (DataEntered) to cell (A10) so that you can use the range name in formulas that refer to this cell.
e- Enter the following replacement functions to test if data have been entered or if cell A13 contains a value greater than zero.:
cell A13: =IF(DataEntered,1,0).
cell B13: =IF(a13>0,A6,"").
cell C13: =IF(A13>0,A4,0)
cell D13: =IF(A13>0,H$4,0).
cell E13: =IF(A13>0,C13*A$5/A$8,0).
cell F13: =IF(A13>0,D13-E13,0).
cell G13: =IF(A13>0,A$9,0).
cell H13: =IF(A13>0,C13-f13-G13,0).
f-edit the formula in cell (H4) to be: =IF(DataEntered,PMT(A5/A8,H5,-A4),0).
edit the formula in cell (H5) to be: =IF(DataEntered,A7*A8,0).
now all error messages should be gone.
g- Type 30 in cell A7 to see calculated results appear.
h- Type the following formulas:
* cell A14: =IF(H13>0,A13+1,0). this function calculates the next payment number only if the previous ending balance is greater than zero.
*cell B14: =IF(A14>0,DATE(YEAR(B13),MONTH(B13)+1,DAY(B13)),0). the date functions identify the specific year,month,and day and add 1 to increase each due date to the next month. the result is 41000 because it is a serial date so far. you will format it soon.
*cell C14: =IF(A14>0,H13,0).
The beginning balance is equal to the ending balance from the previouse period.
i- Format cell B14 as (short date), and then format range (C13:H14) as Currency. (not accounting Number Format).