spreadsheet query, nested formulae and conditional formatting

Hi, I am trying to set up a spreadsheet that requires some thought and I'm struggling!

I will have 3 sets of data each with 3 subsets in them. for example A1A2A3, B1B2B3 and C1C2C3. Can the sheet select the highest number from the As,Bs and Cs and give a total? Also can the highest number in the As turn a different colour, same for Bs and Cs??

Any help with this would be greatly appreciated!!

The formula for the first part of the query is in the yellow cell.

Conditional formatting deals with the rest so highlight one of the other cells and check conditional formatting to see how it's done.

S.

Your highest number will be =max(A1:A3), =max(B1:B3),and =max(C1:C3).
With these results you can then same the sum. You could either do it piece-wise or do =sum(max(A1:A3),max(B1:B3),max(C1:C3)). That is the total of your highest values in columns A, B, and C.

For your conditional formatting, highlight your data (A1 to A3, for example) go to conditional formatting, highlight cell rules, equal to, now type =max(A1:A3) in the left box and choose your format on the right. Now click OK and your max value for that column should be formatted how you specified. Do each column (A, B and C) one at a time, or else you'll only have the one max result for A1 through C3, which is not what you want.

That's fantastic! Thank-you, is there anyway of preventing it from adding two of the same number in each set of data if they are both the same and highest?

eg. if A1 and A2 were both 10 and A3 was 8 it would only use one of the 10s in the final sum?

Originally Posted by Spencer101

The formula for the first part of the query is in the yellow cell.

Conditional formatting deals with the rest so highlight one of the other cells and check conditional formatting to see how it's done.

S.

No, it will not add 10 twice. The max is the max.

As mentioned above, they could all be 10 and the max would still be 10, so it will only count it once.

S.