Excel formula to retreive data from two cells with certain conditions

[kjxavier]

cell: A1 = 1st data
cell: B1 = 2nd data

cell: C1 = 3rd data




1. If A1 = 85 (exact 85 only)
and if B1= < 25
then c1 = 0.21

or if B1= >25 and <= 30
then c1 = 0.32

2. If A1 >=1 and < 85
and if B1= < 25
then c1 = 0.32

or if B1= >25 and <= 30
then c1 = 0.32

3. If A1 >85 <=90
and if B1= < 25
then c1 = 0.41

or if B1= >25 and <= 30
then c1 = 0.52

4. If A1 >90 <=95
and if B1= < 25
then c1 = 0.67

or if B1= >25 and <= 30
then c1 = 0.78

5. If the loan percentage is >95 <=100
Return value = "NA"

[Saarang84]

Untested,

Please Login or Register  to view this content.

For the last condition, i.e. If the loan percentage is >95 <=100, then return value = "NA" I've used cell A1. You need to correct it accordingly if required.

[kjxavier]

Hi! Sarang

Thank you for the reply!

I tried your formula, but could not get the actual results.

I just posted some sample numbers:
formula.JPG


I would be grateful if you could correct the formula or suggest me if i am wrong!

Thank you

[Saarang84]

Hi,

Instead of using whole numbers, try using them as percentages..

Your example will work only if used as below :
0.83 0.22
0.85 0.26
0.89 0.29
0.94 0.20
0.98 0.21

[kjxavier]

Hi,

Instead of using whole numbers, try using them as percentages..

Your example will work only if used as below :
0.83 0.22
0.85 0.26
0.89 0.29
0.94 0.20
0.98 0.21

HI!

The everything except the 1st example is working, when tested with the example above
i.e.:
When A1 is <=0.85 and B1 is <0.25.
it is working with >=25 but not with <25

[kjxavier]

Hi,

Instead of using whole numbers, try using them as percentages..

Your example will work only if used as below :
0.83 0.22
0.85 0.26
0.89 0.29
0.94 0.20
0.98 0.21

Hi!

I got some errors while checking your formula:

Please go through the attachment.

Thank you