How do I write these formulas more efficiently?

[relknes]

I would like to know how to type the following formula set in a more efficient way:


=MIN(SUMXMY2(a1:c1,e1:g1),SUMXMY2(a1:c1,e2:g2),SUMXMY2(a1:c1,e3:g3),...,(SUMXMY2(a1:c1,e5000:g5000))
=MIN(SUMXMY2(a2:c2,e1:g1),SUMXMY2(a2:c2,e2:g2),SUMXMY2(a2:c2,e3:g3),...,(SUMXMY2(a2:c2,e5000:g5000))
...
=MIN(SUMXMY2(a5000:c5000,e1:g1),SUMXMY2(a5000:c5000,e2:g2),SUMXMY2(a5000:c5000,e3:g3),...,(SUMXMY2(a5000:c5000,e5000:g5000))


Is there a way to do that?

[Glenn Kennedy]

Maybe....

H1, copied down:
=MIN(SUMXMY2($A$1:$C$1,E1:G1))

I1 =MIN(H:H)

[Glenn Kennedy]

I've just noticed that your thread title is meaningless. Please re-read rule 1, that you just agreed to adhere to, and then select "thread tools" from the top of post 1 and change it to something that you would use if you were using Google to search for a solution....

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[relknes]

The original post has been edited to refine the title.
Sorry about that. No offense was intended. I will try to keep titles specific in the future.

[AliGW]

No offence taken! However, I recommend you read the forum rules you agreed to on joining today: https://www.excelforum.com/forum-rul...rum-rules.html

Thanks for changing your title.

[Glenn Kennedy]

So, relknes.... was my suggestion of any use to you?

[relknes]

So, relknes.... was my suggestion of any use to you?

I am looking for a way to do it in a single cell. I keep feeling like I need to use an array, but I'm not familiar enough with array functions to know how to do it.

[relknes]

Single cell per MIN function, that is, so 5000 cells in the example rather than 5000^2.

[relknes]

So I ended up trying to solve this by writing a Macro. This is my first attempt at writing code in VBA. I basically used the record function to get one step of Glenn Kennedy's idea, and then coded in some iterations, ending up with:

Please Login or Register  to view this content.

I would say that this solved the issue, but it is running really slow (as in, running the full script takes hours/crashes my computer, so I have to run it in batches manually), and I want to make sure that this is just a function of the number of calculations rather than some problem in my code.
Thanks, Glenn Kennedy, for pointing the way on how to execute these loops.

[Glenn Kennedy]

I did try to adapt some alternative formulae, but got nowhere and gave up as I reckoned that no single formula solution would ever be fast enough.

Personally, I'd stick with the helper....

Anyhow... glad you were able to get something.You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.