Forum Discussion
Excel formula
Hello,
I am seeking help to create a formula that will populate a percentage of an income based on where it falls within the federal poverty level. For example if the income in cell A1 equals 38640. or above I'd like cell B1 to populate .10% of what ever the value is, however if cell A1 has in come that falls between12,881 and 25,760. then I'd like cell B1 to populate .05% of whatever the value is. I tried using the formula below but it came back with a name error. Can someone please assist? Thank you.
=IFS(A2>38640,"AD2*.10%", A2<12880,"AD2*.0%", A2>=12881,"AD2*.05%", A2>=25761,"AD2*.07%")
=IF(A1>=38640,A1*0.1,IF(A1>25761,A1*0.07,IF(A1>12881,A1*0.05,0)))
Is this what you want to do?
13 Replies
Remove all the quotes from the formula. "..." is a text value, not a calculation.
I'd do it like this:
=AD2*IFS(A2<=12880, 0%, A2<=25760, 0.05%, A2<=38640, 0.07%, TRUE, 0.10%)
or
=AD2*LOOKUP(A2, {0, 12880, 25760, 38640}, {0%, 0.05%, 0.07%, 0.10%})
- Just tried both, both came back with errors
AD2 should contain a number, not a text value, for this to work.
And are you absolutely sure that you meant cell AD2 in your first post?
- ok, thanks will try this now
=IF(A1>=38640,A1*0.1,IF(A1>25761,A1*0.07,IF(A1>12881,A1*0.05,0)))
Is this what you want to do?
- Hi, Thanks again, I was able to successfully complete the necessary calculation with the formula you provided.
- Just tried this and its bring back a value error.
That's surprising. Can you open the attached pdf file? The formula seems to work in my spreadsheet.
- Thank you, unfortunately my work computer will not allow me open the example you sent but I will test the formula to see if it works
