Conditional Formatting Excel: If cell C3 is closer to ZERO value than cell A3, I want cell F3 to display "Correcting" shaded green

Question

I need a conditional formatting formula that will enter unique text and shade the cell to identify if a "trader" is correcting their borrowing position.

Context: traders borrow and loan "marbles" but should always be trading back to a zero balance. I need a formula to identify when traders are correcting their position based on their lending and borrowing activity from the day prior to the current day.

So column B is previous days trading position (negative or positive) and column C is current day. I want Column F to show "Correcting" shaded in green if Column C is closer to zero than Column B.

See the pic attached for an example of what I like to see:

Answer 1

you may know this but for fun... start by typing "Correcting" with a green fill in all the required F cells.

Then... for each row. select the F cell.

in conditional formatting, click in the formula bar then go select the appropriate A cell, then a less than sign ( < ) Then select the adjacent C cell.

that should auto generate this in the same formula bar... =$A$3<$C$3 the 3's will vary of course by row. and you can select the

hit the format button and select the color white for both the Font and Fill Tabs. now whenever C is greater than A, F is empty.

Answer 2

You need a formula in column F (starting in F3) that reads something like

=IF(ABS($B3)>ABS($C3),"Correcting","")

Then you can colour the text however you want. If you want fill colour, use conditional formatting and just select an option that looks for cells containing text equal to "Correcting"

It matters if you only want this to show when nearer to zero and not on the other side - eg if you think 20 is closer than 30, but 20 is not "closer" than -30 because they are not on the same side of zero. Then you could use:

    =IF(AND(ABS($B3)>ABS($C3),SIGN($B3)=SIGN($C3)),"Correcting","")

Are you expecting users to type their own comments as well? That would of course overwrite any formulas you enter.