Search code examples
excelif-statementexcel-formulapercentage

Excel IF statement to work with comparing two percentage values and producing a third

I am trying to compare two percentages and produce a third percentage

For example:

The Threshold (column B) is the expected standard The Availability (Column A) is the actual received The Credit (Column C) is the percentage discount given dependent on the difference between columns A and B

[screengrab](https://i.sstatic.net/lGyDwXH9.png)

Formula used

I've attempted to use a formula to say if the value in A is less than the value is B minus the requested percentage then display the text but it does move from the first IF statement

=IF(A4="","",IF(A4<=B4-1%,"5%",IF(A4<=B4-3%,"15%",IF(A4<=B4-5%,"20%","N/A"))))

Nothing seems to work, any pointers would be greatly appreciated

What I want to happen

If the availability is up to 1% below the threshold 5% would be displayed If the availability is up to 3% below the threshold 15% would be displayed If the availability is up to 5% below the threshold 20% would be displayed

What I've tried

I've tried nesting =IF(A4="","",IF(A4<=(B4-1%),"5%" I've tried numerical =IF(A4="","",IF(A4<=(B4-0.01),"5%"

Nothing seems to work, any pointers would be greatly appreciated

Solution

  • I think the formula you want is what I have below.

    It's sort of what you're doing, but I did the reverse because you want your loosest constraints first, otherwise it will pull true almost always for the first condition. Think of it like this. If it's less than x -20, it's definitely x-1, so you start with 20 and work your way through the logic back up to the tightest logic. Figure what's happening is your first/second if is likely catchy everything and unless it's greater than x-1 it will always pull the "" or 5%.

    =IFS(A4="","",A4<=B4-5%,"20%",A4<=B4-3%,"15%",A4<=B4-1%,"5%")