Conditional formatting using formulas

Hi guys

sorry to bother you, but I have been stuck on this particular formula for the last week, and I don’t seem to be progressing any further, and I am hoping you can help


basically there are 6 columns, within these 6 columns there is two kinds of data. The data will either be a number or –. (-) is meant to show n/a, but for reporting purposes I need it to remain (-)

what I am trying to achieve with the formula ,

1)formula searches the multiple columns, and whatever the last number is within those 6 column highlight that particular cell but only highlight the cell if its higher than the average

so if it take row 7, although E7 is higher than average 11, this shouldn’t be highlighted. However h7 should as it’s the last stage and its over the average

if I take row 13, g13 currently has a 12 in it, this is higher than the average of 8, once again this shouldn’t be highlighted as there is another stage, h12 should be highlighted red as it’s the last stage and its higher than the average of 21

row 13, 14 & 15 , column h should be highlighted red as it over 21 day average


Hope this make sense