Once a while ago I had this working in excel after looking for ages through the webs, but the same method isn’t working in LibreOffice. Here goes my attempt at explaining what I want to happen. Note: I will use example ranges and values.
I have a table with items based on a slot. These slots have a certain “Type” of integers. These types are chosen from a drop down menu. In a second set they will be added together as totals and returned with each their own slot, type and integer value.
Now comes the third table. Here I want to calculate the highest value for each Type.
Say Range A1:A4 are the slots (eg. Head, body, arms, feet) and in range B1:B4 I have a type (eg. Shirt, Vest, Hat, Pants, Boots… etc) and in the ranges C1:F4 I have integers based on these types.
In the last table range A6:A15 these types are listed. In range C6:F15 I want each cell to return the highest values for each type.
A1 - Head | B1 - Type A | C1 - 4
A2 - Body | B2 - Type B | C2 - 1
A3 - Arms | B3 - Type C | C3 - 2
A4 - Feet | B4 - Type A | C4 - 3
A6 - Type A | B6 [empty] | C6 Return 4 (since of type A in range C1:C4, the int. 4 is highest)
A7 - Type B | B7 [empty] | C7 return 1
A8 - Type C | B8 [empty] | C8 return 2
A9 - Type D | B9 [empty] | C9 return “-”
A10 - Type E | B10 [empty] | C10 return “-”
The Formula I used in Excel before was this (hope it helps clear up what I am doing)
{=array_constrain(arrayformula(MAX(IF($E$23:$E$39=$A42,$F$23:$F$39))),1,1)}
I know I am terrible at explaining so any questions I’ll attempt answering as best I can.
Thanks in advance for your time and efforts!!