IF Nested formula to return a value when value is between two number ranges

Rachel_Royal · ‎Mar 06, 2023

I'm trying to build a formula that will look up a currency value from a record, determine whether it is between two ranges of currency, and then enters a percentage (the federal poverty level) in a new column. I'm getting errors in the formula below. Any ideas about what I'm doing wrong?

IF(

{yearly income} < 22763,

"125%",

IF(AND(

{yearly income} > 22763, < 27315),

"150%",

IF(AND({yearly income} > 27315, < 36420),

"200",

IF({yearly income} > 36420,

"Over 200%,

)

Ben_Young1 · ‎Mar 06, 2023

Hey @Rachel_Royal!

Try this:

IF(
  {yearly income} < 22763,
  "125%",
  IF(
    AND(
      {yearly income} > 22763,
      {yearly income} < 27315
    ),
    "150%",
    IF(
      AND(
        {yearly income} > 27315,
        {yearly income} < 36420
      ),
      "200%",
      IF(
        {yearly income} > 36420,
        "Over 200%"
      )
    )
  )
)

The issue in your original formula was in your AND() functions.
In plain language, you're trying to evaluate statements like: "If yearly income is greater than $22,763 and less than $27,315, then return 150%."

You translated that into your function as:

IF(
  AND(
    {yearly income} > 22763,
    < 27315
  ),
  "150%"
)

Your first parameter in the AND() function is valid, but the second one is not.
You should think of each statement for the function to evaluate as isolated and individually scoped from each other, so for the second parameter, the function is only seeing " < 27315" instead of "{yearly income} < 27315" as you intended.

See Solution in Thread

Ben_Young1 · ‎Mar 06, 2023

Hey @Rachel_Royal!

Try this:

IF(
  {yearly income} < 22763,
  "125%",
  IF(
    AND(
      {yearly income} > 22763,
      {yearly income} < 27315
    ),
    "150%",
    IF(
      AND(
        {yearly income} > 27315,
        {yearly income} < 36420
      ),
      "200%",
      IF(
        {yearly income} > 36420,
        "Over 200%"
      )
    )
  )
)

The issue in your original formula was in your AND() functions.
In plain language, you're trying to evaluate statements like: "If yearly income is greater than $22,763 and less than $27,315, then return 150%."

You translated that into your function as:

IF(
  AND(
    {yearly income} > 22763,
    < 27315
  ),
  "150%"
)

Your first parameter in the AND() function is valid, but the second one is not.
You should think of each statement for the function to evaluate as isolated and individually scoped from each other, so for the second parameter, the function is only seeing " < 27315" instead of "{yearly income} < 27315" as you intended.

Rachel_Royal · ‎Mar 06, 2023

@Ben_Young1 this was so helpful! Thank you so much!

Rachel_Royal · ‎Mar 06, 2023

@Ben_Young1 I have one more nuance to this calculation that I'm not sure if I can build into this formula but am hoping you might be able to help.

The input form asks the following questions:

How many people live in the household? (The options are single select 1-10 and More than 10)
What was your total household income last year? (This is an open input currency option and is the value included in the formula above)
What is your total monthly household income? (Same as above but not included in the formula)

The formula above is based on just a one-person household, but I need to calculate the percentage above federal poverty line for each possible number in the household (1-10 and more than 10). I originally thought I would have to create separate columns and copy, paste, and edit the formula based on the number of people in the household, but I'm wondering if there's a way to calculate it all in the same column. In other words, have two logical arguments (IF "number of people" is X and "yearly income" is X).

Seeing the spreadsheet below might help explain the income ranges better.

2023 Poverty Guidelines: 48 Contiguous States (all states except Alaska and Hawaii)
								Per Year
Household/ Family Size	25%	50%	75%	100%	125%	130%	133%	135%	138%	150%	175%	180%	185%	200%
1	$3,645	$7,290	$10,935	$14,580	$18,225	$18,954	$19,391	$19,683	$20,120	$21,870	$25,515	$26,244	$26,973	$29,160
2	$4,930	$9,860	$14,790	$19,720	$24,650	$25,636	$26,228	$26,622	$27,214	$29,580	$34,510	$35,496	$36,482	$39,440
3	$6,215	$12,430	$18,645	$24,860	$31,075	$32,318	$33,064	$33,561	$34,307	$37,290	$43,505	$44,748	$45,991	$49,720
4	$7,500	$15,000	$22,500	$30,000	$37,500	$39,000	$39,900	$40,500	$41,400	$45,000	$52,500	$54,000	$55,500	$60,000
5	$8,785	$17,570	$26,355	$35,140	$43,925	$45,682	$46,736	$47,439	$48,493	$52,710	$61,495	$63,252	$65,009	$70,280
6	$10,070	$20,140	$30,210	$40,280	$50,350	$52,364	$53,572	$54,378	$55,586	$60,420	$70,490	$72,504	$74,518	$80,560
7	$11,355	$22,710	$34,065	$45,420	$56,775	$59,046	$60,409	$61,317	$62,680	$68,130	$79,485	$81,756	$84,027	$90,840
8	$12,640	$25,280	$37,920	$50,560	$63,200	$65,728	$67,245	$68,256	$69,773	$75,840	$88,480	$91,008	$93,536	$101,120
9	$13,925	$27,850	$41,775	$55,700	$69,625	$72,410	$74,081	$75,195	$76,866	$83,550	$97,475	$100,260	$103,045	$111,400
10	$15,210	$30,420	$45,630	$60,840	$76,050	$79,092	$80,917	$82,134	$83,959	$91,260	$106,470	$109,512	$112,554	$121,680

Ben_Young1 · ‎Mar 06, 2023

Hey @Rachel_Royal!

Is this along the lines of what you're looking for?

IF(
    AND(
        {Household Members}, {Household Income (Previous Year)}
    ),
    SWITCH(
        {Household Members},
        "1",
            {Household Income (Previous Year)} / 14580,
        "2",
            {Household Income (Previous Year)} / 19720,
        "3",
            {Household Income (Previous Year)} / 24860,
        "4",
            {Household Income (Previous Year)} / 30000,
        "5",
            {Household Income (Previous Year)} / 35140,
        "6",
            {Household Income (Previous Year)} / 40280,
        "7",
            {Household Income (Previous Year)} / 45420,
        "8",
            {Household Income (Previous Year)} / 50560,
        "9",
            {Household Income (Previous Year)} / 55700,
        "10",
            {Household Income (Previous Year)} / 60840
    )
)

IF(
    AND(
        {Household Members}, {Monthly Household Income}
    ),
    SWITCH(
        {Household Members},
        "1",
            {Monthly Household Income} / 1215,
        "2",
            {Monthly Household Income} / 1643,
        "3",
            {Monthly Household Income} / 2072,
        "4",
            {Monthly Household Income} / 2500,
        "5",
            {Monthly Household Income} / 2928,
        "6",
            {Monthly Household Income} / 3357,
        "7",
            {Monthly Household Income} / 3785,
        "8",
            {Monthly Household Income} / 4213,
        "9",
            {Monthly Household Income} / 4642,
        "10",
            {Monthly Household Income} / 5070
    )
)

I didn't go past ten for the household size because I don't know what you'd like to do for the "More Than 10" value. I know that Health & Human Services provides guidance up until a household size of like 14 if I remember correctly, but I'll defer to you on that one.

If you're testing this out, be sure to format your formula output to return a percentage value.

Rachel_Royal · ‎Mar 07, 2023

@Ben_Young1 I have tried the formula below and am getting an error to check my formula text. It does not give me an option to change formatting at all, unfortunately.

IF(

AND(

{Household number}, {Monthly Income}

),

SWITCH(

{Household Number},

"1",

{Monthly Income} / 1215,

"2",

{Monthly Income} / 1643,

"3",

{Monthly Income} / 2072,

"4",

{Monthly Income} / 2500,

"5",

{Monthly Income} / 2928,

"6",

{Monthly Income} / 3357,

"7",

{Monthly Income} / 3785,

"8",

{Monthly Income} / 4213,

"9",

{Monthly Income} / 4642,

"10",

{Monthly Income} / 5070

)

)))))))))

Below is a screenshot of my table if that helps. I'm trying to get the poverty level to show up in the last column.

Ben_Young1 · ‎Mar 07, 2023

Hey @Rachel_Royal!

The formatting will only be available if the only possible data type returned from the formula will only ever be a number. Since your formula can possibly return the value "Over 200%", the formatting options will be automatically disabled since string values cannot be formatted, only numbers.

It seems that the formula that you posted has multiple trailing parentheses which would throw an error. Not sure if that's just a copy/paste error, but worth confirming nonetheless.
I also noticed that your {Household Number} field name in the formula has inconsistent casing on the word Number.

...
AND(
    {Household ---> number <---}, {Monthly Income}
),
SWITCH(
    {Household ---> Number <---},
...

For the sake of making the copy and paste of the formula a bit easier, I've renamed my field names so match yours:

IF(
    AND(
        {Household Number}, {Monthly Income}
    ),
    SWITCH(
        {Household Number},
        "1",
            {Monthly Income} / 1215,
        "2",
            {Monthly Income} / 1643,
        "3",
            {Monthly Income} / 2072,
        "4",
            {Monthly Income} / 2500,
        "5",
            {Monthly Income} / 2928,
        "6",
            {Monthly Income} / 3357,
        "7",
            {Monthly Income} / 3785,
        "8",
            {Monthly Income} / 4213,
        "9",
            {Monthly Income} / 4642,
        "10",
            {Monthly Income} / 5070
    )
)

Here's a shared link to the base I did this in. If you continue to have issues, feel free to use the Copy base feature to duplicate the base in your workspace so that you can pop open all the fields and see how it's all put together.