Get max cell value of all rows and columns

[RSvebeck]

Hi

I have a set of calculated numbers in a table:

Region A    B    C    D
Alfa   1.43 1.23 1.78 1.90
Beta   1.68 2.24 1.38 1.91
Gamma  2.71 2.08 2.02 1.06
Delta  1.68 1.59 1.42 1.15

I want to color the highest number (2.71) red using a expression in the "Background Color" attribute.

I tried this formula:

=IF(rank(rangemax(,,,))=1,rgb(250,200,200))

Where referens to the expression for column A.

But it does not work.

=IF(max(rangemax(,,,))=,rgb(250,200,200))

does not work either...

There is nothing beeing colored...

Any ideas?

I was expecting this to be solved easy, so I must be missing something fundamental here....

Regards

Robert S

[Miguel_Angel_Baeyens]

Hi Robert,

In each expression use instead:

If(Max(TOTAL A) = , RGB(250, 200, 200))

And so on.

Hope that helps.

Miguel

[swuehl · Accepted solution]

You are using different expression, but you want to get the max value over all expressions?

Not sure if I missed a better approach, but I think you need to use advanced aggregation together with maxrange, but use the full expression inside instead of the labels:

Like

=if("A" = max(total aggr(rangemax( avg({<department={a}>}score), avg({<department={b}>}score)), relation)),red(80) )

in my attached sample.

Hope this helps,

Stefan

[RSvebeck]

Hi Miguel.  That expression will give me the max of each column, but what I want is the "single max" of all rows and columns - > The max of all dimensions and expressions. It seems like Stefan has provided a soluition for this. Thanks for taking time to look into this. Reg. Robert

[RSvebeck]

Stefan, your solution is good and it solves my issue.

Thanks for taking time to look into this. Best Regards, Robert