Array for Max Column

[PNuff]

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

 

[jasonb75]

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.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[PNuff]

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]

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]

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.