OK I was on here the other day and I got some great advice on a spreadsheet I'm working on for work. So I'm now looking to make it a bit simpler so any advice would be greatly appreciated.

When the % in B22 is within 5% either side of 43% (ideal split, B20) then it turns Green

When the % in B22 is within 13% either side of 43% (ideal split, B20) then it turns Amber

Same for E22, H22 & K22, the ideal % in each respectively is 4%, 4% & 49%

This has been put in place using a conditional format for each of the cells B22, E22, H22, K22 but has to be edited for each.

Here is the formula used for to turn Amber in B22 "=OR(AND(B22>=0.3,B22<=0.379),AND(B22>=0.485,B22<=0.56))"

and the 2nd rule to turn Green "=AND(B22>=0.38,B22<=0.484)"

Obviously this is fine for the 1 example department I'm using but we have over 20+ department that I would like to use this spreadsheet for & each has a different ideal split % so this could be very time consuming

I'm wondering if there is any way that me changing the ideal split % in cell B20 I could get the rules or formula to change in B22 so I wouldn't have to change the rules for every department, also this would make it a lot user friendly for anyone else to use?

I hope I've explained my self well enough and hopefully there is a solution to my problem but I do understand this could be quiet complicated to implement.

Here is a copy of my spreadsheet so far RHRP demo.xlsx

Any help much appreciated,

Ewen

## Bookmarks