Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Needed!
Hi, i have a problem regarding formulas in excel. The problem i am solving is
whether to grant a loan to an individual and it is determined through a point system. I have to set up a system in excel where points will be allocated to the individual when he or she meets a particular criteria (eg. age25, add 1 point). what formula should i use for the criteria: 2 points are deducted for each ADDITIONAL $10,000 of the requested loan? I know how to set basic criteria like (eg age25, add 1 point) using IF function : =IF(A225,"+1"). What i dont know is how i set a formula when the criteria is for every ADDITIONAL value (ie it will be never ending)? I hope i illustrated my problem clearly. Help would be very much appreciated! Cheers. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Needed!
On Dec 11, 6:56 am, lilo wrote:
I know how to set basic criteria like (eg age25, add 1 point) using IF function : =IF(A225,"+1"). What i dont know is how i set a formula when the criteria is for every ADDITIONAL value (ie it will be never ending)? First, change your first criterion formula to =if(A225,1,0). I'm sure you will be happier with that. If you want the "+", use the custom format "+0;-0;0". This permits you to treat the points as numbers, not text. Second, it is unclear whether you would like a single formula or perhaps a column of formulas (criteria), which you then total. I recommend the latter so that you can see what factors are and are not contributing points. That also makes the formulation trivial. For example: B1: =if(A225, 1, 0) B2: =-2*int(A3/10000) ....etc... B10: =sum(B1:B9) where A3 contains the amount of the loan. You can simplify the formula in B1 to =1*(A225). If you want everything in one formula, you could do something like: B10: =1*(A225) - 2*int(A3/10000) + .... (In that context, you do not need the "1*". But it does not hurt; and it might help you to understand better what is going on.) ----- original posting ----- On Dec 11, 6:56 am, lilo wrote: Hi, i have a problem regarding formulas in excel. The problem i am solving is whether to grant a loan to an individual and it is determined through a point system. I have to set up a system in excel where points will be allocated to the individual when he or she meets a particular criteria (eg. age25, add 1 point). what formula should i use for the criteria: 2 points are deducted for each ADDITIONAL $10,000 of the requested loan? I know how to set basic criteria like (eg age25, add 1 point) using IF function : =IF(A225,"+1"). What i dont know is how i set a formula when the criteria is for every ADDITIONAL value (ie it will be never ending)? I hope i illustrated my problem clearly. Help would be very much appreciated! Cheers. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Needed!
Hi...Thank you so much for your help.....You saved me from endless hours of
work....Once again, Thanks!!!! Cheers "joeu2004" wrote: On Dec 11, 6:56 am, lilo wrote: I know how to set basic criteria like (eg age25, add 1 point) using IF function : =IF(A225,"+1"). What i dont know is how i set a formula when the criteria is for every ADDITIONAL value (ie it will be never ending)? First, change your first criterion formula to =if(A225,1,0). I'm sure you will be happier with that. If you want the "+", use the custom format "+0;-0;0". This permits you to treat the points as numbers, not text. Second, it is unclear whether you would like a single formula or perhaps a column of formulas (criteria), which you then total. I recommend the latter so that you can see what factors are and are not contributing points. That also makes the formulation trivial. For example: B1: =if(A225, 1, 0) B2: =-2*int(A3/10000) ....etc... B10: =sum(B1:B9) where A3 contains the amount of the loan. You can simplify the formula in B1 to =1*(A225). If you want everything in one formula, you could do something like: B10: =1*(A225) - 2*int(A3/10000) + .... (In that context, you do not need the "1*". But it does not hurt; and it might help you to understand better what is going on.) ----- original posting ----- On Dec 11, 6:56 am, lilo wrote: Hi, i have a problem regarding formulas in excel. The problem i am solving is whether to grant a loan to an individual and it is determined through a point system. I have to set up a system in excel where points will be allocated to the individual when he or she meets a particular criteria (eg. age25, add 1 point). what formula should i use for the criteria: 2 points are deducted for each ADDITIONAL $10,000 of the requested loan? I know how to set basic criteria like (eg age25, add 1 point) using IF function : =IF(A225,"+1"). What i dont know is how i set a formula when the criteria is for every ADDITIONAL value (ie it will be never ending)? I hope i illustrated my problem clearly. Help would be very much appreciated! Cheers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
IF AND Formula Help Needed | Excel Discussion (Misc queries) | |||
Formula needed - is this possible? | Excel Discussion (Misc queries) | |||
Formula Needed | Excel Discussion (Misc queries) | |||
Formula Needed... | Excel Worksheet Functions |