Conditional Format based on increments

[earwig999]

Hi,

is it possible to create a formula for a conditional format to recognise increments of £250,000 and highlight figures in the column which exceed them?

[TABLE="width: 500"]
<tbody>[TR]
[TD]INCREMENTS of £250,000
[/TD]
[TD][/TD]
[TD]E.g.[/TD]
[/TR]
[TR]
[TD]250000[/TD]
[TD][/TD]
[TD]234200[/TD]
[/TR]
[TR]
[TD]500000[/TD]
[TD][/TD]
[TD]310000[/TD]
[/TR]
[TR]
[TD]750000[/TD]
[TD][/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD]1000000[/TD]
[TD][/TD]
[TD]475000[/TD]
[/TR]
[TR]
[TD]1250000[/TD]
[TD][/TD]
[TD]501200[/TD]
[/TR]
[TR]
[TD]1500000[/TD]
[TD][/TD]
[TD]654000[/TD]
[/TR]
[TR]
[TD]1750000[/TD]
[TD][/TD]
[TD]748000[/TD]
[/TR]
[TR]
[TD]2000000[/TD]
[TD][/TD]
[TD]810000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes some sense with the example table above.

 

[Joe4]

Assuming that you data is sorted in Ascending order, and that the first number appears in cell B2, then select cells B3 to the end, and enter this Conditional Formatting formula:

=INT($B3/250000) > INT($B2/250000)

and choose your desired formatting option.

 

[earwig999]

Assuming that you data is sorted in Ascending order, and that the first number appears in cell B2, then select cells B3 to the end, and enter this Conditional Formatting formula:

=INT($B3/250000) > INT($B2/250000)

and choose your desired formatting option.

Thank you and it works.... sort of

What I didn't say was that the figures go down as well as up on occasion and then the formula doesn't seem to work correctly.

[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]£234,201 [/TD]
[/TR]
[TR]
[TD]£297,171[/TD]
[/TR]
[TR]
[TD]£350,893[/TD]
[/TR]
[TR]
[TD]£533,307[/TD]
[/TR]
[TR]
[TD]£403,836[/TD]
[/TR]
[TR]
[TD]£583,518[/TD]
[/TR]
[TR]
[TD]£522,684[/TD]
[/TR]
[TR]
[TD]£789,871[/TD]
[/TR]
[TR]
[TD]£945,762[/TD]
[/TR]
[TR]
[TD]£876,035[/TD]
[/TR]
[TR]
[TD]£0[/TD]
[/TR]
[TR]
[TD]£884,436[/TD]
[/TR]
[TR]
[TD]£909,347[/TD]
[/TR]
[TR]
[TD]£868,714[/TD]
[/TR]
[TR]
[TD]£921,074[/TD]
[/TR]
[TR]
[TD]£1,130,828[/TD]
[/TR]
[TR]
[TD]£1,133,814[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

What I didn't say was that the figures go down as well as up on occasion and then the formula doesn't seem to work correctly.

Yes, the devil is always in the details! That is why it is usually best to be as detailed as possible.

Perhaps this will work:

=INT($B3/250000) > INT(MAX($B2:B2)/250000)