Max with multiple rules

SilvioSiefke · October 15, 2021, 12:35pm

The table is used for calculation. Column A is the selling price, column B is the discount, column C is the price - discount x length.

I am looking for the highest price per meter including any discount. This works as long as there is no discount. But how can you use max if it gets a possible discount?

I had the idea combine max with if but this will not work.

Would be nice someone had an idea.

Silvio

SilvioSiefke · October 15, 2021, 4:13pm

=MAX(IFS(B6>=0;A6*(1-B6);B6=0;A6);IFS(B8>=0;A8*(1-B8);A8<0;B8))*E2

this in D2 do the right job. Maybe there other ways? But this is what had search.

Hrbrgr · October 18, 2021, 9:07am

Crossposting:

https://listarchives.libreoffice.org/de/users/2021/msg01361.html

mariosv · October 15, 2021, 2:05pm

In D9 you can use =SUMPRODUCT(MAX(A$14:A$15*E9)), sumproduct makes the formula matricidal, so max gets the maximum of the price*length pairs.

eeigor · October 15, 2021, 3:26pm

=AGGREGATE(4;5;A$14:A$15*E9)

LeroyG · October 15, 2021, 6:38pm

Please, don’t kill the mother.

Maybe: makes the formula an array one.

Saludos.