Forum Discussion
Correct formula (gsheet)
hi, i need help with this:
I want to show 100% percent rating if both items 1a and 1b are checked, thus 50% if 1a or 1b only. At the same time, if items 2a,2b,2c, and 2d are checked, it must show a total of 100% as well (25%each) , even the remaining items from 1a,1b,3a,3b are unchecked. Same goes if only 3a and 3b are checked. by the way, the checkbox values are "true" if checked, "false" if no check.
Thank you so much!
To achieve this in Excel for the web, you can use the following formula:
=IF(AND(A1="true", B1="true"), "100%", IF(OR(A1="true", B1="true"), "50%", IF(COUNTIF(C1:F1, "true")=4, "100%", IF(AND(C1="true", D1="true"), "50%", "0%"))))
Assuming that:
- Cell A1 contains the value of item 1a checkbox,
- Cell B1 contains the value of item 1b checkbox,
- Cells C1 to F1 contain the values of items 2a, 2b, 2c, and 2d checkboxes,
- Cell C1 contains the value of item 2a checkbox,
- Cell D1 contains the value of item 2b checkbox,
- Cell E1 contains the value of item 2c checkbox,
- Cell F1 contains the value of item 2d checkbox,
- Cells G1 and H1 contain the values of items 3a and 3b checkboxes, respectively.
You can adjust the cell references accordingly based on your actual spreadsheet layout.
This formula uses nested IF statements and the AND, OR, and COUNTIF functions to check the checkbox values and calculate the rating percentage accordingly.
Remember to replace "true" and "false" in the formula with the actual values that represent checked and unchecked checkboxes in your spreadsheet.
This formula will display "100%" if both items 1a and 1b are checked, "50%" if either 1a or 1b is checked, "100%" if all items 2a, 2b, 2c, and 2d are checked, "50%" if both items 3a and 3b are checked, and "0%" for any other combination.
1 Reply
To achieve this in Excel for the web, you can use the following formula:
=IF(AND(A1="true", B1="true"), "100%", IF(OR(A1="true", B1="true"), "50%", IF(COUNTIF(C1:F1, "true")=4, "100%", IF(AND(C1="true", D1="true"), "50%", "0%"))))
Assuming that:
- Cell A1 contains the value of item 1a checkbox,
- Cell B1 contains the value of item 1b checkbox,
- Cells C1 to F1 contain the values of items 2a, 2b, 2c, and 2d checkboxes,
- Cell C1 contains the value of item 2a checkbox,
- Cell D1 contains the value of item 2b checkbox,
- Cell E1 contains the value of item 2c checkbox,
- Cell F1 contains the value of item 2d checkbox,
- Cells G1 and H1 contain the values of items 3a and 3b checkboxes, respectively.
You can adjust the cell references accordingly based on your actual spreadsheet layout.
This formula uses nested IF statements and the AND, OR, and COUNTIF functions to check the checkbox values and calculate the rating percentage accordingly.
Remember to replace "true" and "false" in the formula with the actual values that represent checked and unchecked checkboxes in your spreadsheet.
This formula will display "100%" if both items 1a and 1b are checked, "50%" if either 1a or 1b is checked, "100%" if all items 2a, 2b, 2c, and 2d are checked, "50%" if both items 3a and 3b are checked, and "0%" for any other combination.