Conditional Formatting a cell w/ a fraction or being able to format based on another cells range

[OfficeMgr615]

Conditional Formatting a cell w/ a fraction or being able to format based on another cells range
I am using Office 365, Excel 2016. I am trying to create a scorecard for clients to use, I want the cells to highlight based on a range of 3 colors (90%=red, 95%=yellow, 100%=green), though my cell doesn’t contain a %. Technically it’s a range of the % anything 90-94.9% shows red, 95-99.9% is yellow and anything 100% or higher is green. My CEO would prefer me to show these colors using the Format Style: Icon set, yet at this point I’ll use anything as long as it works.

In row 4, cells E4 to P4 we show Actual/Budget numbers for each month (example in cell E4: 184/157) 184 is their actual number, 157 was their budget number. This cell should be green since 184 is higher than 157. My next cell F4 has 140/154, this cell should be red since 140 falls between 90-94.9% or 138.6-145.9. I’ve made sure the cell is formatted as TEXT. For whatever reason since I have a fraction in the cell (184/157) I can not get any conditional formatting to work. The text in the cell isn’t technically a fraction but still looks like one and I was told by another person in the office that since my conditional formatting would be based on a range for that cell the only way I can get it to work would be to reference another cell to this cell to color accordingly, yet I don’t know the correct formula.

Here is a snapshot where I've showed my Actual/Budget numbers for each month, then the 90-95-100% range to base what color to format the cell on.

Scorecard
Measure	Goal	Oct-15	Nov-15	Dec-15	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16
Total Volume	To Budget (actual/budget)	184/157	140/154	160/161	188/180	200/150	160/174	202/183	175/178	225/162	/183	/190	/173
	90%	141.3	138.6	144.9	162.0	135.0	156.6	164.7	160.2	145.8	164.7	171.0	155.7
	95%	149.2	146.3	153.0	171.0	142.5	165.3	173.9	169.1	153.9	173.9	180.5	164.4
	100%	157.0	154.0	161.0	180.0	150.0	174.0	183.0	178.0	162.0	183.0	190.0	173.0

<tbody>
</tbody>

In rows 32, 33, 34 from E32 to P34 I have formulas in each cell to determine the % based on the Budget number, (example in cell E32: =90/100*157 – showing the result 141.3 which means 141.3 is 90% of 157). I went and did this for each month for each 90% 95% 100%, thinking I could reference this cell to color my E4 to P4 cells accordingly yet its still a number range I need to base my conditional formatting off of in the end. From here I’ve conditionally formatted the cells Selecting Rule Type – Format all cells based on their values, Format Style Icon Sets (since the first rule ‘format based on values’ appears to be the only way I can get it to select Format Style Icon Sets). From here I have red circle when value is >= Type: Formula, Value =90/100*157, yellow circle when < Formula and >= Value = 95/100*157, green circle when < Formula. Now I did opt to Reverse Icon Order, problem with this is that I always get a red circle using Icon Sets; when I keep the Icon order where green is first, I always get green circles. I’m sure an IF formula would be better to get the range, but I don’t know what it would look like.

When I change to the Rule – Format only cells that contain, Cell Value, Between, =157 and =300 – then I get my cells to highlight accordingly. Again though I still need cell E4 to show green (and following cells to color accordingly based on my number range), I’ve even tried using this same Rule – Format only cells that contain Between =157/157 and =300/157 --- this doesn’t work, my cell stays white, no formatting took effect. And of course each month has a different Budget number so I need to redo the formatting for each cell.

I really don’t know what I’m doing here, if anyone has a better suggestion on how to set this up let me know. To say again in case I’ve confused you, cell E4 has Actual/Budget numbers in them 184/157 this is over 100% so I need the cell to show green, if it read 155/157 it would need to show yellow, if it read 143/157 it would need to be red. The % ranges 90% is 141.3, 95% is 149.2 100% is 157 – so if the Actual number is between 141.3 and 149.1 the cell should be red, if the Actual number is between 149.2 and 156.9 the cell should be yellow and anything 157 or higher should show the cell in green. This isn’t as simple as it sounds since my cell only reads 184/157 not just a single number.

Is there a way to have another cell somewhere on the page be my range, if one cell has 157, cell next to it has 300, can I have my cell E4 of 184/157 refer to another cell that is already conditionally formatted? Meaning if I did the Rule to Format only cells that contain Bewteen 157 and 300, the cell would show green and since this cell is green my cell E4 would show green. If this is possible how do I conditionally format my E4 to register the color of another cell?

To also throw into the mix here is I’ll be handing this excel sheet off to the client. Good chance is they may use this to forecast and will change the Actual/Budget numbers, which would then screw up all my formatting since the numbers wont be the same. In a perfect world, if there was a formula I could use and not have to reference another cell that would be better. If there is a way to conditionally format cell E4 to read off a % range (90-94%, 95-99%, 100% and up), yet I don’t think this is possible if the only text in my cell says 184/157.

Any help is appreciated. Ask questions if you need more descriptions in order to help. Thanks

 

[navic]

Any help is appreciated.

Try the following

R/C	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3			Measure	Goal	Oct-15	Nov-15	Dec-15	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16
4			Total Volume	actual/ budget	184/157	140/154	160/161	188/180	200/150	160/174	202/183	175/178	225/162	/183	/190	/173
5
6				90%	141,3	138,6	144,9	162,0	135,0	156,6	164,7	160,2	145,8	164,7	171,0	155,7
7				95%	149,2	146,3	153,0	171,0	142,5	165,3	173,9	169,1	153,9	173,9	180,5	164,4
8				100%	157,0	154,0	161,0	180,0	150,0	174,0	183,0	178,0	162,0	183,0	190,0	173,0

<tbody>
</tbody>

In cell E6 put the following formula (copy it down and to the right). This formula will calculate the value of a certain percentage from column D, based on second number from cell in range E4:P4

=($D6*100)/100*VALUE(MID(E$4,SEARCH("/",E$4,1)+1,10))

Select range E4:P4 and start Conditional Formatting.

For the first rule insert a formula (format green)

=IF(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>VALUE(MID(E4,SEARCH("/",E$4,1)+1,10)),TRUE,FALSE)

For the second rule insert a formula (format red)

=IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E6,VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))<e7),true,false)


For the third rule insert a formula (format yellow)

=IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E7,VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))<e8),true,false)

I hope it will help?

[EDIT]: I do not know what is going on but the last two formulas I can not insert in the

 TAG<e7),true,false)[ code]
<e8),true,false)[ code]
<e7),true,false)[ code]
<e8),true,false)[ code]
<e7),true,false)[ code]
<e8),true,false)[ code]
<e7),true,false)[ code]
<e8),true,false)[ code]
</e8),true,false)[></e7),true,false)[></e8),true,false)[></e7),true,false)[></e8),true,false)[></e7),true,false)[></e8),true,false)[></e7),true,false)[></e8),true,false)
</e7),true,false)

 

[OfficeMgr615]

Thanks - I was able to get the green color to work. For the red and yellow its telling me I'm missing a parenthesis, so I'll see if I can figure that part out.

The trick will be when I test it to change #s since I wasnt sure how that would effect formulas. Meaning cell E4 has 184/157, what would happen if the client kept this sheet to use for next year and entered for example 163/165 - would the cell still turn yellow, would the formula know new numbers have been entered into E4 and recalculate the %s?

 

[OfficeMgr615]

Sorry I couldnt figure out how to edit my previous post.
When I take your red and yellow formula and enter it into a blank cell, the red produces a FALSE value, I did get the parenthesis error first time and by hitting Enter again I got False. While the yellow one gives the error. Best I can see its the parenthesis before the AND that doesnt have an end and adding a parenthesis to the end of the formula doesnt fix it.

 

[OfficeMgr615]

It's actually the =IF(AND(VALUE portion of the formula, both of these parenthesis are missing an end.
Adding two )) to the end of the formula kind of fixes it but not, now I'm getting the "There's a problem with this formula, Not trying to type a formula?" error message.
I'll keep trying.

 

[OfficeMgr615]

I'm using this formula for red -- =IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E6),VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4)))
and this one for yellow -- =IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E7),VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4)))

I thought it worked, fixed the yellow first, but when I went to fix the red and add the rule to conditional formatting - it changed the cells that were previously highlighted Green to now show Red.

 

[OfficeMgr615]

I tried testing it and changed cell E4 from 184/157 to 156/157 which should have made the cell go to yellow, but instead it stayed red.

I went back and Managed my rules to have green on top, yellow in middle, red on the bottom and selected the box for STOP IF TRUE on the green and yellow formula. Doing that at least changed my red cells back to green!
But there is still a problem with the yellow and red. If I take a cell that is green and change the Actual number to a number that should change the color to yellow, it instead changes to red. If I take a yellow cell, change the number to over 100% it goes green like it should, but if I try to change the number so the cell shows red, it stays yellow.

Any ideas?

 

[navic]

I do not know why someone has not accepted my formula.
Here is renovated formula.
I have not put a formula in CODE tag because the forum cut off the last part of the formula

red format
=IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E6,VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))...missing the last part

<e7),true,false)

yellow format
=IF(AND(VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E7,VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))...missing the last part

<e8),true,false)< html="">[EDIT]
I do not know why someone has not accepted my formula.
last missing piece
</e8),true,false)<></e7),true,false)

 

[navic]

This formulas are in image

 

[OfficeMgr615]

I'm getting the "There's a problem with this formula, Not trying to type a formula?" error message. Don't know why its not working other than I can tell it looks to be missing a parenthesis again. The last ) after TRUE,FALSE doesnt have a buddy.
I tried this one =IF(AND((VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E6),VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))<e7),true,false) where="" i="" added="" a="" (="" to="" the="" beginning="" between="" and(value.="" this="" at="" least="" produced="" false="" result,="" better="" than="" an="" error="" message.
I did the same for yellow =IF(AND((VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))>E33),VALUE(IFERROR(IF(LEN(E$4>10),LEFT(E$4,SEARCH("/",E$4)-1),E$4),E$4))
<e34),true,false)

Updated both formulas for conditional formatting and it works !!!!!!! I'll work on the rest of the sheet and see if I have any more issues. THANKS!!


1) Can anyone confirm if there is a way to do this but with using Icon Sets?</e34),true,false)></e7),true,false)>