Variable Between A Percent Range

[Blue22]

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%

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

 

[Joe4]

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.

=IF(A2=100%,7,IF(A2>=26%,8,IF(A2>=1%,9,10)))

 

[Joe4]

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]

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.

=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]

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.

=IF(IF(A2=100%,7,IF(A2>=26%,8,IF(A2>=1%,9,10)))=B2,"Match","Something is wrong!")