Variable Between A Percent Range

Blue22

New Member
Joined
Mar 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I am new to Excel and need help with a formula.

If 0% is paid then it needs to be coded to 10, and so forth... Right now the data does not match, for example I have 0% in the paid column and the code column will show up with any value 8, 9, 7

If X= 10 then, Y = 0%
If X= 9 then, Y = between 1% to 25%
If X= 8 then, Y = between 26% to 99%
If X=7 the, Y= 100%
1647272376547.png

This needs to be true in reverse as well.

IF(A2=0%,"10",IF(AND(A3>=1%,A3<25%),"9")) Is this accurate? Is there a cleaner, easier way? How do I produce this in Power Query?

Hope this makes sense
Thank you
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,534
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Firstly, it looks like you have the IF/THEN in your description backwards, you are wanting to check the percentage and then return a number (and not the other way around, right?).

You can do a nested IF formula, starting either at the the top (or bottom) and then working your way up (or down), i.e.
Excel Formula:
=IF(A2=100%,7,IF(A2>=26%,8,IF(A2>=1%,9,10)))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,534
Office Version
  1. 365
Platform
  1. Windows
Note: I just noticed this:
How do I produce this in Power Query?

You posted this question in the "Excel Questions" forum.
There is a Power Tools forum for those kind of questions.
 

Blue22

New Member
Joined
Mar 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Firstly, it looks like you have the IF/THEN in your description backwards, you are wanting to check the percentage and then return a number (and not the other way around, right?).

You can do a nested IF formula, starting either at the the top (or bottom) and then working your way up (or down), i.e.
Excel Formula:
=IF(A2=100%,7,IF(A2>=26%,8,IF(A2>=1%,9,10)))
Oh Wow!

I really appreciate your quick response. That nested formula looks beautiful to me.

Great question, about checking the percentage. My intention is to make certain that the % paid column has the accurate code. I also want to make certain that the code column has the accurate % paid. For example, the invoice data may show 12% paid, and the funded account may say 27% paid, which is providing code 9, but really needs to be coded to 8. The data needs to be verified and corrected.

Thank you for the heads up about Power Query!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,534
Office Version
  1. 365
Platform
  1. Windows
You should only have to need to check it one way.
If you want to compare columsn A and B to make sure that the data values are in sync and are correct, just take the formula I gave you and compare it to cell B2, i.e.
Rich (BB code):
=IF(IF(A2=100%,7,IF(A2>=26%,8,IF(A2>=1%,9,10)))=B2,"Match","Something is wrong!")
 
Solution

Forum statistics

Threads
1,176,395
Messages
5,902,822
Members
434,998
Latest member
BradneedsXcellhelp

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
Top