You are viewing limited content. For full access, please sign in.

Question

Question

Perform Calculation based on Radio Button value

Forms
Updated August 7, 2019
asked on August 2, 2019

Hello,

Here's a description of the calculation I want to perform: I want a formula to SUM the value of Currency Field: "Total_Applicant_Income_Before", If it's greater than 7500 and then subtract 7500 if Radio Button:  "Is_Applicant_Disabled" is set to "Yes" (which has an assigned value of 1). Here's the formula I'm trying to use but it does not work. No error message to help me out. It just returns $0.00. 

=SUMIF(Total_Applicant_Income_Before,">7500")-((COUNTIF(Is_Applicant_Disabled_,">0"))*7500)

Radio Button Setup.png
Radio Button Setup.png (23.58 KB)
Download
0 0

Answer

SELECTED ANSWER
replied on August 7, 2019

If it's just a single value, what are you SUMming? 

IF(Total_Applicant_Income_Before< 7500,0,Total_Applicant_Income_Before) - IF(Is_Applicant_Disabled=1,7500,0)

This calculation says, if total applicant income is less than 7500, use 0, otherwise use the income; then if applicant is disabled, subtract 7500, otherwise subtract 0. This calculation as written would fail if they can set income to below 7500 and mark they are disabled (would return -7500). If that is a possible situation, replace the condition in the second if from Is_Applicant_Disabled=1 to

IF(Total_Applicant_Income_Before<7500,0,Total_Applicant_Income_Before) - IF(AND(Is_Applicant_Disabled=1,Total_Applicant_Income_Before>7500),7500,0)

This way, you only subtract 7500 if applicant is disabled AND the total income is greater than 7500. 

 

0 0

Replies

replied on August 5, 2019

I don't fully understand the calculation. Do you only want to sum up the value of Total_Applicant_Income_Before if it's greater than $7500? Or do you want to sum up that value regardless and then only subtract $7500 if both the sum is greater than $7500 AND Is_Applicant_Disabled = 1? 

Is Total_Applicant_Income_Before a table column with many values? Or just a single value? 

The way you have your Sumif formula, it is looking at a single value or the rows in the Total_Applicant_Income_Before column of a table and adding all the ones that are over $7500. Based on the variable name, it doesn't look like you are using a table, so your calculation is saying, If Total_Applicant_Income_Before is greater than 7500, use that value. Since Total_Applicant_Income_Before is less than 7500, nothing is being added, hence the 0. 

0 0
replied on August 7, 2019

Do you only want to sum up the value of Total_Applicant_Income_Before if it's greater than $7500? Yes and then subtract $7500 if Is_Applicant_Disabled = 1. If the sum of Total_Applicant_Income_Before is less than 7500. Then I just want it to return 0.00.

 

Is Total_Applicant_Income_Before a table column with many values? Or just a single value? It is just a single value.

 

The current formula gives me 0.00 no matter whether the amount entered in Total_Applicant_Income_Before is greater than 7500 or not.

 

Some background: This is an application for Rental Relief for elderly & disabled people. If the applicant is disabled they get $7500.00 deducted from their gross income which is used in calculating the amount of relief they will receive. Therefore, if they report $8000.00 in income, and they are disabled. I want the formula to return $500.00 (deducting $7500.00). However, if they report a gross income of $6000.00 AND they are disabled, I want the formula to return $0.00 (I don't want any negative numbers). If they are NOT disabled then I just need it to SUM as usual.

 

So after writing this it occurs to me that my formula is not going to work. Especially if the applicant is NOT disabled. However, i'm not sure what formula would work. Any help would be greatly appreciated. Perhaps I need to break the calculation up into separate hidden fields?

0 0
SELECTED ANSWER
replied on August 7, 2019

If it's just a single value, what are you SUMming? 

IF(Total_Applicant_Income_Before< 7500,0,Total_Applicant_Income_Before) - IF(Is_Applicant_Disabled=1,7500,0)

This calculation says, if total applicant income is less than 7500, use 0, otherwise use the income; then if applicant is disabled, subtract 7500, otherwise subtract 0. This calculation as written would fail if they can set income to below 7500 and mark they are disabled (would return -7500). If that is a possible situation, replace the condition in the second if from Is_Applicant_Disabled=1 to

IF(Total_Applicant_Income_Before<7500,0,Total_Applicant_Income_Before) - IF(AND(Is_Applicant_Disabled=1,Total_Applicant_Income_Before>7500),7500,0)

This way, you only subtract 7500 if applicant is disabled AND the total income is greater than 7500. 

 

0 0
replied on August 7, 2019

Jared,

 

Thank you! This worked perfectly. I had attempted to use an IF statement before, but I did not know how to use an AND statement. This clears so much up for me. I sincerely appreciate your help!

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...