Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

1. Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

I have a spreadsheet that I have setup to determine what is referred to as side value in gambling terms for the English Premiership. I have attached a values only copy of it to demonstrate what I am trying to and effectively automate, which I think may be able to be done through conditional formatting.

Essentially all of the other figures on the spreadsheet are either calculated (in the full workbook) through a solver model, formula or in the case of the odds are copied in from another workbook. This particular sheet was just based on the last 100 matches and not the full seasons results in terms of percentages.

What I am trying to do is in the case of home winning % (column G), where this exceeds home winning odds% (in this case from Bet365 and in column K) - hence creating a side value (spreadsheet model calculates chance of outcome occurring odds exceeding bookmaker odds for outcome occurring) - I would want to highlight the percentage in column G yellow, and the odds in column K another colour (in this case magenta). Can this be done through conditional formatting? In this case I would also like to tie together columns I and L, and also H and M (for the other two possible outcomes). I'm figuring that if this can't be done through conditional formatting, I could probably do this via a Macro that I wouldn't be adverse to.

Is this possible to do in columns rather to do each cell individually (for the reasons laid out in post 3 - sorry I am editing this from a couple of days after the original post to try and improve the clarity).

I know I could do this manually (e.g. as I have done here) but it would just be a lot easier if I could automate this and it would eliminate potential mistakes.

2011_2012 Last 100 Snapshot.xls

2. Re: Conditional Formatting Query for one cell exceeding another and highlighting both

not sure i understand how your logic works (and it doesnt really matter either, to answer your question)...

to apply conditional formatting the way you want to, highlight the cells you want to apply it to (or just a single cell, if you want), then select CF, select new rule, formula, then enter a formula like...

=1sr-cell-reference=2nd=cell-reference

pick the formatting you want

3. Re: Conditional Formatting Query for one cell exceeding another and highlighting both

Hi,

Thanks for this. Sorry I've not been exactly clear (or I'm possibly misunderstanding the answer). I could see that I could do this using two rules for individual cells:

e.g. rule 1: for Cell G30 if Cell Value exceeds K30 format pattern/background yellow
rule 2: for Cell K30 if Cell Value is less than G30 format pattern/background magenta

Can this be done for whole columns at a time rather than just individual cells (I'm only using Excel 2004 for Mac) as I didn't think you could effectively conditionally reference a cell range (which is essentially what I am trying to do, as to do it cell by cell it's just as quick as effectively printing it off and circling the relevant cells - to do it by column I would make a time saving).

Sorry if that came across wrong and thanks for the idea.

4. Re: Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

Hi,

I've changed the title a touch as I thought it was possibly more relevant to what I was trying to achieve. I've still had no joy with this and am considering trying to do a macro as I think that may work (or I think I could possibly add an extra worksheet in and reference to it and do an IF function - I don't really want to change the layout of the sheet in question, as I have already set this up for an easy copy and paste from another workbook).

Any general advice would be appreciated (and apologies I wouldn't normally bump something).

There are currently 1 users browsing this thread. (0 members and 1 guests)