Formula to work out if value is within a certain percentage

Hi All,

Apologies if this may be in the wrong sub-forum.

I have a spreadsheet set up to work out if someone is achieving a KPI average. At present i have it set up with conditional formatting to show as a green value if the person is above the average, black if its on the average and red if its below the average.

What i am looking to change is for it to be orange if they are below the average but within 10%. So if the average was 200 and they hit 180-199 it would show as orange, anything below that would show as red. Hope this explains what I'm trying to achieve let me know if i need to explain further detail.

Any help would be much appreciated.

Thanks.

you have to use "use formula to determine which cells to format" if the average is in cell A1 <A1*.9 format as red etc

Originally Posted by Norie

Where would the KPI average come from and where is the KPI for each person stored?

Thanks for the reply Norie, The KPI data is stored on a seperate weekly master list that i use a vlookup to bring each persons data into a simple looking spreadsheet just to display their values against the average as below image shows.
KPI.jpg

So i need a formatting/formula that would be making C3 show as orange as its below 10% of 200.

<c2*.9 as a formal and format orange however 10% of 200 is 20 and so it is not below 180! if you mean below =and(c3> c2*.9,c3<c2)

Originally Posted by davsth

<c2*.9 as a formal and format orange however 10% of 200 is 20 and so it is not below 180! if you mean below =and(c3> c2*.9,c3<c2)

Hi Davsth, thanks for the help. I have got this partly working but i think i have completely confused myself in the process.

I currently have
=and(c3> c2*.9,c3<c2) = orange (I'm not entirely sure what this formula means but it seems to work)
cell value equals exact = black
cell value less than = red
cell value more than = green

(Ignore the values above in that table)
With C3 as 180 and C2 as 200. 180 and below appears as red, 181-199 appears as orange, 200 appears as black, 201+ appears as green.

Does this all seem correct or am i missing something?
Shouldn't 180 be orange?

Originally Posted by Bo_Ry

That need conditional formatting

Select B3:E3 Press Alt H L R >
New rule > use a formula > =B3<B$2 > Format > change font or fill color Orange > OK
New rule > use a formula > =B3<0.8*B$2 > Format > change font or fill color Red > OK

This has worked exactly as i need!
Thank you Bo_Ry and everyone else for providing help. My Excel learning continues..