Excel Conditional Formatting (Excel for Mac)

Question

I have the following formula in a cell:

=(((U5+V5)/2)*O5*(1+(R5/100)))

I would like to set up conditional formatting for this cell, as follows:

When the solution to the formula is <G5, the cell has a green background,

and when the solution to the formula is >G5, the cell has a red background.

What I have done, which doesn't work, follows:

Click on the cell with this formula, then click on Conditional Formatting, then select New Rule.

Here are my selections:

Style: 2 color scale

For Minimum column:

Type: Formula

Value: I copy and paste =(((U5+V5)/2)*O5*(1+(R5/100)))<G5

Color: I select green

For Maximum column:

Type: Formula

Value: I copy and paste =(((U5+V5)/2)*O5*(1+(R5/100)))>G5

Color: I select red

I then click OK, and no background color shows up in that cell.

Currently, the value in the selected cell where I want the conditional formatting to show is $51.50,

and, the value in cell G5 is $71.00.

Thus, if this was working, I would expect the conditional formatting to make the cell green.

Finally, if you know what formula symbols to apply for less than or equal to, and greater than or equal to, that also would be appreciated.

TIA for your assistance.

Answer 1

Thanks, but unfortunately I cannot follow your directions.

I do not know where to find 'Use a formula to determine which cells to format'.

Sorry, I missed the fact that you're doing this on Mac.

Does your user interface look different from this?

Answer 2

My apologies, I should really stop answering questions about Mac (I'm on Windows).

After selecting Conditional Formatting > New Rule..., select Classic from the Style drop-down.

You should then be able to select 'Format only cells that contain' from the drop-down below that.

Answer 3

What color do you want it to be when the result equals G5??

An easy way to do this is:

• Instead of using the color scale from the 'Format all cells based on their values' selection, select 'Use a formula to determine which cells to format'.
• Use the formula: =[your cell address]>G5 (so if the cell you want to format is M5, your formula would be =M5>G5)
• Click the Format button
• Select whatever shade of red you want to use when the condition is met

That takes care of red.

To take care of green, you can just color the cell green by default. It will always be green unless its value is greater than G5.

Answer 4

Remove the current rules, if any.

Select the cell with the formula.

Set the fill color to green. This will be the default.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'greater than' or 'greater than or equal to' from the second drop-down, according to your preference.

Enter =G5 in the box next to it.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

Remark: in formulas, you can use <= for 'less than or equal to' and >= for 'greater than or equal to'.

Answer 5

Thanks, but unfortunately I cannot follow your directions.

I do not know where to find 'Use a formula to determine which cells to format'.