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.

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.

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.