-1

I have a sheet a sheet which records/calculates achieved Foreign Exchange rates.

There are 3 columns - € Rate; £ Used and € Got. The second two columns are given values according to the activity and the rate is a calculated cell by diving the £ Used by the € Got in each case.

The issue is that I am looking to calculate the highest (best) rate obtained and then attempting to highlight the (any) cells which meet that value.

In the Conditional Format, I am using the cell range and selecting Is equal to and the cell which contains the highest rate, but this instruction appears to be ignored, and nothing is highlighted.

Is this because values in calculated cells are ignored for Conditional formatting rules purposes, and if so, what workaround or different mechanism is available to achieve highlighting the highest rate?

EDIT:

So =B4=$E$2 works (where E2 contains the best rate and B4 is the first cell across the selected range) and if set as a custom formula, the correct action takes place.

Improve this question
4
  • I imagine the cell values don't match but look like they do. A simple test would be to place this formula in a cell: IF(B2=F8,"Match","Don't Match")
    – Blindspots ♦
    May 17 at 4:36
  • That shows they match - (E2 against F8)
    – Low Rep Mods are bad
    May 17 at 5:58
  • 1
    Interesting quirk.
    – Blindspots ♦
    May 17 at 12:19
  • 1
    While screenshots might be helpful, questions should mainly use text. Please describe the relevant parts of your spreadsheet, including the relevant t spreadsheet settings, and add sample data and formulas as text directly into the question body.
    – Rubén ♦
    May 18 at 8:33

2 Answers 2

Reset to default
-1

Conditional Foprmatting is Working

Your conditional formatting argument is working as intended, but no cells match the condition.

Simple Test

Place the following formula in an unrelated cell:

=IF(B2=F8,"Match","Don't Match")
="Don't Match"

Reason they don't match

DIVIDE(690.08,600)=1.1501333...

DIVIDE(690.08,600)<>1.1501

Improve this answer
0
-1

Following further research I have discovered the solution to this problem.

=B4=$E$2 in a custom formula. This works across the selected range B4:J27

enter image description here

The result is thus:-

enter image description here

I used this reference to help solve the issue.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.