| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 68943 | 2006-05-16 10:11:00 | MS Excel Experts wanted | jamesyboi (6579) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 455346 | 2006-05-16 10:11:00 | :eek: Ok heres the deal. I'm doing this thing for school like a fake scenario about making a template spreadsheet for a loan company. In the template I have formulae such as PMT and IF functions to calculate the interest rates and Fortnightly repayments. The problem is, this is a template, therefore i want to delete all the names of the clients and their loan amounts etc and leave it blank for other people to use. But when I delete the values the formuale do not have a '-' sign. I want to achieve this. Instead I get a formuale error like #DIV/0! and 7/07/00. Is there a way in the options to make it so the formuale with missing values for their references just revert to a '-' symbol - or anything but DIV/0! and 7/07/00. note. the 7/07/00. one is because I have a column called 'approval date' which is formulated to be 7 days after the interview date (form =E4+$C$2) Thanks! time is of the essense, any comments very much appreciated. |
jamesyboi (6579) | ||
| 455347 | 2006-05-16 10:19:00 | Hi, have you tried putting your equation that are giving the invalid result within an IF statement using the ="" for the arguement to the cell reference? or such like, so that id the ref cell is blank (""), the cell shows "", but if not the equation. Another way is to put the calculus in a sheet behind the pro forma so they only get results back after initial data input. |
SolMiester (139) | ||
| 455348 | 2006-05-16 10:23:00 | great.. i messed around with the options. couldnt' remember their defaults. so i reverted to default. and it screwed up half way through the process and now its correupt........>.< can't repair guess ill reinstall | jamesyboi (6579) | ||
| 455349 | 2006-05-16 10:27:00 | no cos my invalid function is another interest rate value. its not the problem i think. just the PMT and the Application approval date | jamesyboi (6579) | ||
| 455350 | 2006-05-16 10:30:00 | ok to make it understandable. how can you make the result of a pmt function not display as an error when the cells it uses in its formuale are blank? | jamesyboi (6579) | ||
| 455351 | 2006-05-16 10:36:00 | For the date one i tried an IF function saying if the interview date was nothing then the approval date would be nothing but because of its formatting it displayed as 00/01/00. however if i use text it works! now i just need to solve the PMT function. | jamesyboi (6579) | ||
| 455352 | 2006-05-16 10:38:00 | by putting the entire sheet in duplicate to behind the front sheet and use IF to mirror cell.?! | SolMiester (139) | ||
| 455353 | 2006-05-16 10:47:00 | nice idea. i could copy the sheet and make it hidden. however i must explain the formuale in my planning... | jamesyboi (6579) | ||
| 455354 | 2006-05-16 10:50:00 | I tried this. =IF(J7=0,"",IF=J7>15000,21%,20%) but it doesn't work. maybe i did it wrong. basically says if the loan amount is 0 then the interest value is nothing. and if there is a loan amount then another IF function takes place......errr | jamesyboi (6579) | ||
| 455355 | 2006-05-16 10:53:00 | also. with pmt. i tried =IF(J6=0,"","=PMT(K7/12,I7*12,0,J7)/26"). but it thinks the =pmt is a word not another formuale. i need a function that can do a function in a function. | jamesyboi (6579) | ||
| 1 2 | |||||