Dec 06 2021 01:11 PM
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%")
Dec 06 2021 01:32 PM
Solution=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?
Dec 06 2021 01:34 PM
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%})
Dec 06 2021 01:35 PM
Dec 06 2021 01:44 PM
That's surprising. Can you open the attached pdf file? The formula seems to work in my spreadsheet.
Dec 06 2021 01:46 PM
Dec 06 2021 01:47 PM - edited Dec 06 2021 01:48 PM
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?
Dec 06 2021 01:53 PM
What is the error you receive?
Could you attach a small sample workbook without sensitive data?
Dec 06 2021 02:00 PM
Dec 07 2021 08:28 AM
Dec 06 2021 01:32 PM
Solution=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?