Array for Max Column

PNuff

New Member
Joined
Dec 6, 2017
Messages
5
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 6, 2017
Messages
5
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
Joined
Dec 6, 2017
Messages
5
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
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,904
Messages
5,638,907
Members
417,058
Latest member
BRYCEPIETROWIAK

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?

Disable AdBlock

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
Top