Using If function

Zanony

New Member
Joined
Apr 27, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi, Im trying to come up with a scoring system where:

Debt Level floorDebt level ceilingPoints
0%​
9%​
25
10%​
19%​
75
20%​
34%​
100
35%​
44%​
50
45%​
50%​
25
50%​
51%​
0

Im using this formula: =IF(C27<9,25,IF(C27<19,75,IF(C27>25<34,100,IF(C27<44,50,IF(C27<50,25,IF(C27>51,0," "))))))

But its giving me the wrong answer because for a level of debt of 25% the points should be 100, yet is giving me 25.

Help! Anyone!!!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Welcome to the MrExcel forum!

Your biggest problem is that you're using 9 for 9%, as well as the other percentages. The next problem is that you need to use AND for the combined conditions, and finally, you need <= in some places instead of just <. A corrected formula is below. But you can do the whole thing much simpler with a VLOOKUP. See below:

Book1
ABCD
1Debt Level floorDebt level ceilingPoints
20%9%25
310%19%75
420%34%100
535%44%50
645%50%25
750%51%0
8
26
2725.00%100
28100
Sheet17
Cell Formulas
RangeFormula
D27D27=IF(C27<9%,25,IF(C27<19%,75,IF(AND(C27>=25%,C27<=34%),100,IF(C27<=44%,50,IF(C27<50%,25,IF(C27>=51%,0," "))))))
D28D28=VLOOKUP(C27,A2:C7,3)
 
Upvote 0
Welcome to the MrExcel forum!

Your biggest problem is that you're using 9 for 9%, as well as the other percentages. The next problem is that you need to use AND for the combined conditions, and finally, you need <= in some places instead of just <. A corrected formula is below. But you can do the whole thing much simpler with a VLOOKUP. See below:

Thank you Eric for helping me.

When I try to fix it with your solution its not allowing me. What Im trying to formulate is basically: If John Doe is between ages 20-29 then thats worth 25 points on his credit application. (=IF(AND(C7=>20,=<29,25,IF(AND(C7=>30,=<39,100,IF(AND(C7=>40,=<49,75,IF(AND(C7=>50,=<59,50,IF(AND(C7=>60,=<70,25,IF(C7=>70,0" ")))))))))))

If he is between ages 30-39 then thats worth 100 points and so forth. The same with with loan to value and debt leverage. But Im making a mistake somewhere.

Edad59
iScore 1 (Edad)=IF(AND(C7=>20,=<29,25,IF(AND(C7=>30,=<39,100,IF(AND(C7=>40,=<49,75,IF(AND(C7=>50,=<59,50,IF(AND(C7=>60,=<70,25,IF(C7=>70,0" ")))))))))))
 
Last edited by a moderator:
Upvote 0
Your issue here is that when you code an AND function, you need both complete conditions in it, and you need to close it with a parenthesis. You have:

AND(C7=>20,=<29

and it should be:

AND(C7=>20,C7<=29)

I fixed your formula in C8 below. But even so, there are better ways to write that formula. Look at this:

Book1
BCDEFG
6EdadPuntos
7Edad590190
8Points50202925
9503039100
1050404975
11505950
12606925
13702000
Sheet1
Cell Formulas
RangeFormula
C8C8=IF(AND(C7>=20,C7<=29),25,IF(AND(C7>=30,C7<=39),100,IF(AND(C7>=40,C7<=49),75,IF(AND(C7>=50,C7<=59),50,IF(AND(C7>=60,C7<=70),25,IF(C7>=70,0))))))
C9C9=IFS(C7<20,0,C7<30,25,C7<40,100,C7<50,75,C7<60,50,C7<70,25,C7>=70,0)
C10C10=VLOOKUP(C7,$E$7:$G$13,3)


The C9 formula does the same thing, but without any AND functions. If the age is under 20, then the formula returns 0 and stops. If the formula is under 30, the formula returns 25 and stops. We don't need to use AND(C7>=20,C7<=30) because the under 20 situation has already been taken care of. The same for the rest of the ranges. But when you have a set of ranges like this, it's even easier to build a table like in E6:G13 and put the ranges and points there. Then a VLOOKUP does all the work of figuring out the ranges.

Hope this helps!
 
Upvote 0
Your issue here is that when you code an AND function, you need both complete conditions in it, and you need to close it with a parenthesis. You have:

AND(C7=>20,=<29

and it should be:

AND(C7=>20,C7<=29)

I fixed your formula in C8 below. But even so, there are better ways to write that formula. Look at this:

Book1
BCDEFG
6EdadPuntos
7Edad590190
8Points50202925
9503039100
1050404975
11505950
12606925
13702000
Sheet1
Cell Formulas
RangeFormula
C8C8=IF(AND(C7>=20,C7<=29),25,IF(AND(C7>=30,C7<=39),100,IF(AND(C7>=40,C7<=49),75,IF(AND(C7>=50,C7<=59),50,IF(AND(C7>=60,C7<=70),25,IF(C7>=70,0))))))
C9C9=IFS(C7<20,0,C7<30,25,C7<40,100,C7<50,75,C7<60,50,C7<70,25,C7>=70,0)
C10C10=VLOOKUP(C7,$E$7:$G$13,3)


The C9 formula does the same thing, but without any AND functions. If the age is under 20, then the formula returns 0 and stops. If the formula is under 30, the formula returns 25 and stops. We don't need to use AND(C7>=20,C7<=30) because the under 20 situation has already been taken care of. The same for the rest of the ranges. But when you have a set of ranges like this, it's even easier to build a table like in E6:G13 and put the ranges and points there. Then a VLOOKUP does all the work of figuring out the ranges.

Hope this helps!
THANK YOU THANK YOU THANK YOU PROBLEM SOLVED!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top