conditional formatting result of formula

[Kim B]

Hello. I want to apply a conditional formatting icon but the tricky part is the cell that receives the formatting isn't the result of the formula.

Excel Workbook
	C	D	E
4	1/1/1991	26	40
Sheet1

the difference result if 40-26 is 14 the conditional formatting needs to be applied against the result of 14 being less than, greater than, or equal to a value. So if the result is less than 5 then i would want a yellow icon.

Not sure it can be done without a result column, but thought I would give it a shot

thanks in advance

 

[Roxxien]

You need to choose to have your conditional formatting by a formula rule.

Then paste this formula

=$E$4-$D$4<5

You then only need to modify your format for what you want.

So, for your answer, why couldn't you do it without a result column?

 

[Kim B]

Because under the edit format rule, I can't give it a formula, the box wants a value, unless I am in the wrong place to begin with?

Excel Workbook
	A	B	C	D	E
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sheet1

 

[Kim B]

The screen shot didn't come through correctly. The icon conditional formatting wants a value to apply the formatting to, I can't type in a formula. If I change to format based on formula, I can type in a formula, but then my formatting selection goes away for icons.

 

[shg]

Icon sets can only be applied to the cells containing the values, not to other cells.

 

[Kim B]

ok thank you, then maybe you can suggest some other kind of formatting. I have included more data to help. A color would appear for each value that is less then 5 years left, less than 7 years left, less than 10 year left less than 15 years left and greater than 40. Each statement is indicative of itself. So if a value is 26, it would be the color of less than 15...

Excel Workbook
	D	E
4	26	40
5	27	40
6	31	40
7	9	40
8	16	40
9	3	40
10	16	40
Sheet1

 

[shg]

So if a value is 26, it would be the color of less than 15...

26 < 15?

Aside from that,

Conditional Formatting, Use a Formula

=Me < 5, pick a color, tick Stop if True
=Me < 10, pick a color, tick Stop if True
=Me < 15, pick a color, tick Stop if True

Replace Me with the address of the cell in which it appears.

 

[Kim B]

Sorry for the confusion, it is so simple, it is confusing. The difference between 40 and 26 is 14, that is what is left. The interpretation is if item has a useful life of 14 years left, it is 26 years old as of now. But the measurement is not how old it is now, but how many years until it has maxed out.

 

[shg]

No matter; the cells are formatted based on their values. You can figure out what those values are and then proceed as suggested, no?

 

[Kim B]

Doesn't seem to be working.

Excel Workbook
	D	E	F
4	26		40
5	27		40
6	31		40
7	9		40
8	16		40
9	3		40
10	16		40
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	1. / Formula is =$F$4-$D$4<5	Abc
E4	2. / Formula is =$F$4-$D$4<7	Abc
E4	3. / Formula is =$F$4-$D$4<10	Abc
E4	4. / Formula is =$F$4-$D$4<15	Abc
E4	5. / Formula is =$F$4-$D$4<1	Abc
E5	1. / Formula is =$F$4-$D$4<5	Abc
E5	2. / Formula is =$F$4-$D$4<7	Abc
E5	3. / Formula is =$F$4-$D$4<10	Abc
E5	4. / Formula is =$F$4-$D$4<15	Abc
E5	5. / Formula is =$F$4-$D$4<1	Abc
E6	1. / Formula is =$F$4-$D$4<5	Abc
E6	2. / Formula is =$F$4-$D$4<7	Abc
E6	3. / Formula is =$F$4-$D$4<10	Abc
E6	4. / Formula is =$F$4-$D$4<15	Abc
E6	5. / Formula is =$F$4-$D$4<1	Abc
E7	1. / Formula is =$F$4-$D$4<5	Abc
E7	2. / Formula is =$F$4-$D$4<7	Abc
E7	3. / Formula is =$F$4-$D$4<10	Abc
E7	4. / Formula is =$F$4-$D$4<15	Abc
E7	5. / Formula is =$F$4-$D$4<1	Abc
E8	1. / Formula is =$F$4-$D$4<5	Abc
E8	2. / Formula is =$F$4-$D$4<7	Abc
E8	3. / Formula is =$F$4-$D$4<10	Abc
E8	4. / Formula is =$F$4-$D$4<15	Abc
E8	5. / Formula is =$F$4-$D$4<1	Abc
E9	1. / Formula is =$F$4-$D$4<5	Abc
E9	2. / Formula is =$F$4-$D$4<7	Abc
E9	3. / Formula is =$F$4-$D$4<10	Abc
E9	4. / Formula is =$F$4-$D$4<15	Abc
E9	5. / Formula is =$F$4-$D$4<1	Abc
E10	1. / Formula is =$F$4-$D$4<5	Abc
E10	2. / Formula is =$F$4-$D$4<7	Abc
E10	3. / Formula is =$F$4-$D$4<10	Abc
E10	4. / Formula is =$F$4-$D$4<15	Abc
E10	5. / Formula is =$F$4-$D$4<1	Abc