Excel formula / code for calculating value based on multiple cells - REMINDER

[kjxavier]

SORRY TO POST IT AGAIN!

Am not receiving any response! If i am wrong - please ignore this and may close my thread!

If someone could solve this for me ....... it will be highly grateful!


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"


NOTE: A Valued member/administrator helped me, but found some errors :

=IF(AND(A1=0.85,B1<=0.25),0.21,IF(OR(AND(B1>=0.25,B1<=0.3),AND(A1>=1,A1<0.85,B1<=0.25)),0.32,IF(AND(A1>0.85,A1<=0.9,B1<=0.25),0.41,IF(AND(B1>=0.25,B1<=0.3),0.52,IF(AND(A1>0.9,A1<=0.95,B1<=0.25),0.67,IF(AND(B1>=0.25,B1<=0.3),0.78,IF(AND(A1>0.95,A1<=1),"NA")))))))
=======================
ATTACHED WORK FILE

[pareshj]

Hi,

Just check this condition, I think this is not feasible:
>=1 and < 85 B< 25 (Here you are asking for A greater than or equal to 1 and less than 85)
>=1 and < 85 B= >25 and <= 30 (Same as above, you are asking for A greater than or equal to 1 and less than 85)

Please recheck this conditions and hope this will help you.


Regards,
Paresh J

[Gottoplay]

This formulas takes in account that whatever is in column B is lower or equal to 30. It shortens the formula.

=IF(A3<0.85,0.32,
IF(A3=0.85,IF(B3<0.25,0.21,0.32),
IF(A3<=0.9,IF(B3<0.25,0.41,0.52),
IF(A3<=0.95,IF(B3<0.25,0.67,0.78),"NA"))))

[kjxavier]

Excellent work, with simple formula!

[kjxavier]

Hi! Gottoplay

Thanks for your formula!...... i should say: excellent formula!

i have a similar problem:

78 19 NA
78.2 21 NA
80.2 19 0.19
81.6 21 0.32
88.9 16 0.23
89.1 27 0.52
92.5 16 0.26
92.9 27 0.78
95.1 19 0.79
95.7 22 0.9
97.1 11 NA
98.2 26 NA
99 27 NA

where i edited your formula!:


=IF(A3<80,"NA",
IF(A3<=85,IF(B3<20,0.19,0.32),
IF(A3<=90,IF(B3<20,0.23,0.52),
IF(A3<=95,IF(B3<20,0.26,0.78),
IF(A3<=97,IF(B3<20,0.79,0.90),
IF(A3>=97,"NA")))))


and i got the desired result!

but the function is showing some error! yet it gives the correct result!
Can you please check the edited formula and correct me?

[Gottoplay]

When I pasted your formulas, I got an error because you have a missing parenthesis at the end.

The last "IF" can be removed. With this you do not need to add the parenthesis. See below formula.

=IF(A3<80,"NA",
IF(A3<=85,IF(B3<20,0.19,0.32),
IF(A3<=90,IF(B3<20,0.23,0.52),
IF(A3<=95,IF(B3<20,0.26,0.78),
IF(A3<=97,IF(B3<20,0.79,0.90),"NA")))))

[kjxavier]

Thanks again!