max and conditional formatting

[zrx1200]

e8 = 7.8
e10 =5.5
e6 = 6.3

answer to be in cell e12

conditional formatting formula =e12=max(e8,e10)-e6

I've tried all combinations of locks on rows and columns.

It does nothing, I don't understand why?

2019 office

 

[StephenCrump]

I'm not sure what you mean by "answer"? The answer to what?

Here's E12 conditionally formatted to yellow fill if E12=MAX(E8,E10)-E6.

	A	B	C	D	E
1
2
3
4
5
6					6.3
7
8					7.8
9
10					5.5
11
12					1.5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E12	Expression	=E12=MAX(E8,E10)-E6	text	NO

I don't know whether you may have minor rounding discrepancies, e.g. if my E6 displays as 6.3, but is really 6.2999999, the E12 conditional format condition will not be true. I would need to round to the precision required.

 

[zrx1200]

I'm not sure what you mean by "answer"? The answer to what?

Here's E12 conditionally formatted to yellow fill if E12=MAX(E8,E10)-E6.

	A	B	C	D	E
1
2
3
4
5
6					6.3
7
8					7.8
9
10					5.5
11
12					1.5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E12	Expression	=E12=MAX(E8,E10)-E6	text	NO

I don't know whether you may have minor rounding discrepancies, e.g. if my E6 displays as 6.3, but is really 6.2999999, the E12 conditional format condition will not be true. I would need to round to the precision required.

SO, in case outlined above, I can get fill color only not with 1.5 the "answer" using this formula below.
=E12+MAX(E8,E10)-E6

Your formula yields nothing. No fill or answer number?

All cells are formatted to Number one decimal.

I don't get this?

 

[StephenCrump]

Sorry, I still don't understand what you are trying to do.

1. What formula, or what value, do you have in cell E12? You haven't told us this yet.

2. When do you want cell E12 to be conditionally formatted?

 

[zrx1200]

Sorry thought I was clear in #1, never the less let me clarify.

Cell E6 will have value of 6.3
Cell E8 will have value of 7.8
Cell E10 will have value of 5.5
Cell E12 will get its value from the conditional formatting formula, which is suppose to be =E12=Max(E8,E10)-E6 equaling 1.5 display in cell E12

Does the scope now make sense?

Thanks for your time thus far!

 

[Joe4]

Just to clarify -
- Conditional Formatting only changes the formatting on cells. It cannot/does not return values to a cell.
- Formulas in a cell will return values, but not formatting

So it sounds like maybe you want both of these things?
If that is the case, you would place your formula in cell E12, and add any Conditional Formatting that you want to that cell separately (though I am not sure it makes sense to have the same formula in both cells, as then the Conditonal Formatting would always be applied, kind of defeating the purpose).

 

[zrx1200]

Just to clarify -
- Conditional Formatting only changes the formatting on cells. It cannot/does not return values to a cell.
- Formulas in a cell will return values, but not formatting

So it sounds like maybe you want both of these things?
If that is the case, you would place your formula in cell E12, and add any Conditional Formatting that you want to that cell separately (though I am not sure it makes sense to have the same formula in both cells, as then the Conditonal Formatting would always be applied, kind of defeating the purpose).

Thanks Joe4,

You help solve the the issue!

- Conditional Formatting only changes the formatting on cells. It cannot/does not return values to a cell. Key!

Also, I noticed in my vba code I was clearing cell E12 which was taking away the formula so I omitted that cell from clear contents in the vba procedure.

Thanks to Stephen and Joe.

 

[Joe4]

You are welcome.
Glad we were able to help you figure it out.