# Array for Max Column

#### PNuff

##### New Member
Hi! I have two types of variables (type A1, A2, A3…) and (type B1,B2,B3…). Each variable of both types has multiple values that are retrieved through a V-lookup table.

The combination of these two variables is a Scenario (ex. ‘typeA1-typeB1’ is one Scenario, ‘typeA2-typeB3’ is another…etc. There is always a typeA, and always a typeB. ). The values of each variable are used in several calculations for each Scenario. The results of these calculations are set in a column. Most important to me is the Max of this column. That Max falls in Cel Z10. Currently, I can only get the Max for one Scenario at a time. To do another, I need re-plug in the new two-variable Scenario that I want to look at. I’m looking to get the Max for several Scenarios at once. I am envisioning this happening by listing the two variables (typeA and typeB) side by side (which would denote the Scenario) and then having a third column (in that same line) that procures the Max of that particular Scenario.

You can assume the typeA and typeB variables are in columns A (A1-A20) and B (B1-B20) respectively, and each line (of those two values) is a Scenario. We’ll say for 20 lines for 20 Scenarios. I’m looking for the syntax that would be in column C and I am hoping for Max values (for each of the respective scenarios) to be displayed in Cels c1-c20. I’m pretty sure I will also have to change the syntax in Cel Z10, which is currently just the Max function – and that would be a help, too!

I’ve tried to lay this out as clearly as I could, but I could have missed something – I’d be happy to clarify if I can – just let me know.

Thanks for all your efforts!

Pete

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### jasonb75

##### Well-known Member
I’m pretty sure I will also have to change the syntax in Cel Z10, which is currently just the Max function
If I'm following correctly that is actually the critical part, although I'm not seeing anything in your post that tells us the relationship between the max value, or the source of that value and the scenarios that you have detailed.

I would suggest looking at the MAXIFS function, using your scenario variables as the criteria.

An actual (small) sample with scenarios, associated values and expected results will be more informative than a description.

#### PNuff

##### New Member
Thank you for responding - So true

I'm trying to figure out if a bridge fails with a truck loading. Type A variable is a bridge, Type B variable is a truck. The bridge values are mainly dimensions (of which length is the predominant value). The truck values are axle spacings and the axle weights of that axle. I have the truck moving across the bridge at 100mm intervals and at each interval I calculate what the forces (the max of which would be in Column C) from the axles that are on the bridge. The calculations for each increment in length is one line in the spreadsheet, with the max force of that specific truck position in the column I mention. I need the max of that column for several truck-bridge scenarios...

Scenario 1 - TruckA1onBridgeB1
Scenario 2 - TruckA2onBridgeB1
Scenario 3 - TruckA3onBridgeB1
Scenario 4 - TruckA1onBridgeB2
Scenario 5 - TruckA2onBridgeB2...

this type of thing...

The calculations between the bridge and truck values are expansive & intense, a bit of a pain to list out. I'm hoping my response above helps out...

Thanks

#### PNuff

##### New Member
One more thing - you can assume a max of 1000 incremental positions of the truck, and the that the first increment (100mm) occurs at Line11, 200mm at Line 12...etc. The Max function, reaches "down" columnZ to get the max...

#### jasonb75

##### Well-known Member
Sorry, without seeing a visual example, I'm just not getting the concept.

In my mind, with each row being an individual scenario, you need the calculations, that you mention for each row, making the individual result the unique max by default.
The calculations between the bridge and truck values are expansive & intense
It goes without saying that I must be missing something which appears obvious to you, but without, at minimum, a condensed version of the sheet, I have no way of figuring that out.

Replies
9
Views
390
Replies
0
Views
159
Replies
13
Views
156
Replies
1
Views
67
Replies
12
Views
565

1,127,881
Messages
5,627,447
Members
416,248
Latest member
inese_green

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back