Excel conditional formatting help

[ritcH]

Okay, I realise I use this board a lot for excel help but seeing as it's full of helpful people I shall continue!

I'm trying to do something which I think it quite complicated but hopefully isn't!

My result is calculated as follows. If the Data + 20% column equals less that Data + 5 then the result is Data + 5. However, if the Data + 20% column equals more than or the same as Data + 5 then the result is Data + 20%.

The result column is therefore calculated with a formula, for example C2 is: =IF((B2>(A2+5)),B2,(A2+5)). Okay that's fairly simple.

Now for the complicated bit. I want to do two things. First, I want to conditionally format the Result column so that if the result is false i.e., A2+5 then it turns the text red. And if the result is true i.e., the number from Data + 20% then the text remains black.

The next thing I want to do is count the number of instances in the results column where the result is false.

Can anyone help? I can use the conditional formatting command to format one cell but I have no idea how to apply the formatting to all other cells in the results column so that the formatting changes for the row the result is on. It's very annoying - and obviously the data I am working with is not as above but hundreds of rows.

This is not homework, it's real work.. sadly! It's not in my job description either, but I'm just giving it a go - with someones help, I hope.

 

[Participant]

I think I can help you with the counting thing but there's probably a better way. You could do "=IF(C2=A2+5,"1","")" then count the "1"s [using =COUNTA or countif]. You can hide the column with the "1"s in in for neatness' sake. I'll have a think about the colours but it may involve a macro as conditional formatting isn't powerful enough for that.

 

[ritcH]

Yeah it'll probably definitely require a macro but I know no visual basic! It's so frustrating being able to logically think about a problem but not knowing a language that computers can understand!

Some day I'll learn...

I'll try the counting, thanks for the input

 

[TheKnat]

I've just done this with no macro. You need 2 conditional formating rules though.

1) Create a rule for B2:C6 looking for duplicate values. Set the format to be red
2) Create a rule for B2:B6 looking for cell value>0 to set the colour to black

Bit of a round about way of doing it but it will check for duplicate values and turn them red and then re-format the B column and turn it black again.