Formula Needed!

[lilo]

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. age>25, 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 age>25, add 1 point) using IF
function : =IF(A2>25,"+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.

 

[joeu2004]

I know how to set basic criteria like (eg age>25, add 1 point)
using IF function : =IF(A2>25,"+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(A2>25,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(A2>25, 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*(A2>25).

If you want everything in one formula, you could do something like:

B10: =1*(A2>25) - 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 -----

 

[lilo]

Hi...Thank you so much for your help.....You saved me from endless hours of
work....Once again, Thanks!!!!

Cheers