SOLVED

Excel formula

Occasional Contributor

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%")

13 Replies
best response confirmed by Lindo15 (Occasional Contributor)
Solution

@Lindo15 

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

@Lindo15 

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%})

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
Just tried this and its bring back a value error.
ok, thanks will try this now
Just tried both, both came back with errors

@Lindo15 

That's surprising. Can you open the attached pdf file? The formula seems to work in my spreadsheet.

I will try to open the files when I get home this evening my company blocks certain outside content. I will try it again and let you know what happens. Thanks again

@Lindo15 

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?

yes, it contains a dollar amount.

@Lindo15 

What is the error you receive?

Could you attach a small sample workbook without sensitive data?

Yes, I will have to do that once I get to a personal computer my corporation blocks a large amount of file sharing so as soon as I can I will. Thanks for your help
Hi, Thanks again, I was able to successfully complete the necessary calculation with the formula you provided.