Conditional Formatting help

[masond3]

HI GUYS and girls

I need help with conditional formatting.



there are 5 columns, within these 5 columns there will be two kinds of data. Either a number or -. (-) is meant to show n/a, but for reporting purposes I need it to remain (-)
the columns in question is q –u
I need the conditional formatting to searches across the multiple columns, and whatever the last number is within those 5 column highlight that particular cell but only highlight the cell if its higher than the average

I have created averages for each column between q-u which is located in column ac- ad

Also the conditional formatting, cant apply if the stage, column f is Opportunity lost or live transactions

please find attached worksheet

[Cutter]

Does this formula for CF accomplish what you want for column Q?

=AND(ISNUMBER(Q2),COUNT(R2:U2)=0,Q2>$AD$2)

If so, each column needs to be different formula so adjust it for the other columns

Column R would be:

=AND(ISNUMBER(R2),COUNT(S2:U2)=0,R2>$AD$3)

For column U you would just need:

=AND(ISNUMBER(U2),U2>$AD$6)

[masond3]

Sorry to be a pain cutter, can you apply it the worksheet ?
I have found an old worksheet which i have just attached, where i applied cf, hopefully that will explain what i mean

[vikas.bhandari]

Let me know if you were not looking for the similar output.

I have made a conditional formatting in the attached book which highlights the last value in Column Q to U in Blue color if the values are greater than the average calculated on right.

Thanks,
Vikas B

[masond3]

Vikas , that is pretty much what i need, however if the stage is live transaction or opportunity lost, then the conditional formatting , shouldn’t be applied.

Also instead of highlighting every cell which is higher than the average in ab, I needs to highlight the very last stage its at, but only if its higher than the average.

so you should affectively only have highlighted cell per row, if its at one of the stages and higher than the average

if its lower than the average nothing should be highlighted

[vikas.bhandari]

Here you go

[masond3]

vikas, you clever man

as my data set always grows, will the conditional formatting also grow ?

[vikas.bhandari]

You will need to copy any cell with formatting and paste the formatting to the new cells. It would do.

Thanks!
Vikas

[Cutter]

I may as well give you my version since it has different formulas:

For example:

Mine: =AND(F2<>"Live Transactions",F2<>"Opportunity Lost",COUNT(R2:U2)=0,1*Q2>$AD$2)

Vikas: =AND(Q2>$AD$2,COUNT(R2:U2)=0,ISNUMBER(Q2),NOT(OR(F2="Opportunity Lost", F2="Live Transactions")))

[masond3]

HI cutter, looking at your formula, it looks like you have referenced the column, so regardless of the ammount of data is in there, the formatting will always apply as your reference the stage column, which will never change, and also the averages, which will never change. is that correct ?