Excel integer to boolean condition

Question

Creating a spreadsheet that has a bunch of test cases and at the end says pass or fail.

all values right now are either "Pass" or "fail". I need to be able to have a conditional where I can type in like a number. if it is above 5 i need it to return a pass. if it is below 5 i need it to return a false. any solutions guys?

Solution 1

The following might be the way to go:

where you enter a value in B1, which is then used to condition on. It's usually easier and more transparent to have a column that contains the conditioned value that is separate from the original data.

Another option might be conditional formatting. In this case, you condition on the value in a cell. Here I've formatted the cells to display a green/yellow/red dot (traffic sign) depending on the value in the cell.:

with the output resembling

Another option is to change the formatting of the cell to a specific "Custom" formatting:

Read more about how to Create a custom number format on Microsoft's help page.

A final option (that is a bit more intricate) requires you to set multiple, mutually exclusive, conditional formatting of the cells as well as separate "Custom" cell formatting:

Note that the cell entry A4 has a value of 16, while the display is actually "Pass" (since it's greater than 8 (cell B1). The steps for obtaining this include:

1. Create a conditional formatting using the "Use a formula to determine which cells to format" option.

2. Specify the "Format values where this formula is true" as depicted above (removing the dollar signs inserted around A4 by default), while setting the format using "Custom":

   

3. Do the same for the other (mutually exclusive) condition by creating a new rule for the same cell ("Format values where this formula is true:" =A4<=$B$1 and setting the "Custom" format to "Fail").

4. Once both conditional formatting rules are set, change the "Applied to" field to fit your range:

   

Solution 2

=IF(A2>5,"Pass","Fail") Replacing A2 with the appropriate cell reference.

Comments

[user2569803]

could i display the 4 but have the value of pass? also you sir are a boss for posting this

[Werner]

@user2569803: I've added some detail at the end of my anwer explaining how you can obtain your output using a combination of conditional formatting and custom number formatting.