 SOLVED

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.

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

Re: Excel formula

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

Re: Excel formula

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

Re: Excel formula

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

Re: Excel formula

Just tried this and its bring back a value error.

Re: Excel formula

ok, thanks will try this now

Re: Excel formula

Just tried both, both came back with errors

Re: Excel formula

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

Re: Excel formula

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

Re: Excel formula

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?

Re: Excel formula

yes, it contains a dollar amount.

Re: Excel formula

What is the error you receive?

Could you attach a small sample workbook without sensitive data?

Re: Excel formula

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

Re: Excel formula

Hi, Thanks again, I was able to successfully complete the necessary calculation with the formula you provided.