How to learn complex formulas

[crm911]

This was from an old thread about extracting domains from a URL, but this is a question about *HOW* you (any expert) visualise this formula before writing it? Do you write the core and then add refinements?

What is a good method of learning to write such formulas?

I didn't even notice the "dots" were gone. Okay, let's just SUBSTITUTE the spaces away...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A1,FIND("://",A1)+3,99),A1))&"/","/",REPT(" ",99)),99))),".",REPT(" ",99)),99*(2+(LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A1,FIND("://",A1)+3,99),A1))&"/","/",REPT(" ",99)),99)))&".",".",REPT(" ",99)),198)))=2))))," ",".")

 

[Ron Coderre]

Don't be impressed by long, convoluted, arcane formulas. They usually exist for one of two reasons:
1) They should have been done in VBA, but couldn't be (for some reason)
2) The person building it got it to work, but didn't put enough effort into making it efficient.

For instance, I may be wrong, but...from what i can see, that formula could be replaced by this one:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/",SUBSTITUTE(A1,"://","|||"))-1),".",REPT(" ",LEN(A1)),1),LEN(A1)))

True formula experts strive for elegant formulas that are understandable and can be maintained/edited by intermediate Excel users. I've seen some really short formulas that are so arcane that I simply have to trust that they work because I'll never figure out what they actually do. (I've seen some date calculation formulas like that). I've also seen many long, complicated formulas (like the one you posted) that just beg to be simplified. There are also formulas that look to be huge, ungainly conglomerations...but are actually repeated sections that simply reference different ranges.

The key is to be well-versed in the more powerful Excel functions (INDEX, MATCH, SUMPRODUCT, SUMIF, SUMIFS, etc) and understand how they interact with each other.

I hope that helps.

 

[Smitty]

Welcome to the Board!

What is a good method of learning to write such formulas?

Watch what guys like Ron, Aladin, Domenic, Barry, and other formula wizards do. Ron hinted at it, but a lot of it comes to trying to simplify logic as best as you can (and a lot of times it can be really hard, but that's why you come here! )

I'd say that the best place to start is instead of trying to digest a completely convoluted formula like the one above, find a situation where you need something, but can't figure it out and ask a question here; within 15 minutes you'll probably have a formula or VBA solution (or both), but trying to figure out that monster won't do you any good (hell, I got a headache just looking at it! ). You're also best suited to try to find situations/data that are familiar to you; just like recording a macro for the first time, if you try doing something you've never done before, you'll never understand the code when you try to read it back.

Just keep asking questions! That's why we're all here.

 

[Macropod]

I've seen some really short formulas that are so arcane that I simply have to trust that they work because I'll never figure out what they actually do. (I've seen some date calculation formulas like that).

Formulae like:
=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
or
=DOLLAR((DAY(MINUTE(A1/38)/2+55)&".4."&A1)/7,)*7-6
for example, that calculate the date of Easter for a year in A1.

 

[crm911]

Thank you for all the above suggestions. I have asked some questions before, when I needed an answer, but now am looking to learn. I agree that short, elegant formulas are worth using. I am not an Excel newbie, but have rarely gone past VLOOKUP and Pivot Tables.

 

[crm911]

(Mods feel free to move the post, as it could be getting into a solution mode here)

For instance, I may be wrong, but...from what i can see, that formula could be replaced by this one:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/",SUBSTITUTE(A1,"://","|||"))-1),".",REPT(" ",LEN(A1)),1),LEN(A1)))

Sorry, I didn't provide enough context as to the original requirement, as the "long" formula actually does the job most of the time. The problem is identifying a "domain", which in an SEO context becomes a looser definition than the strict one used by domain sellers.

The idea is to extract the unique domain part of the URL. For a top-level domain (TLD) of ".com", such as hxxp://www.example.com/blah/blah, we need to extract "example.com". For a country code TLD (ccTLD) of ".com.au", such as hxxp://blah.example.com.au/blah/blah we need to extract "example.com.au" (http changed to hxxp)

So I imagine the author kept on building the formula to cover all the odd examples that popped up. That's why I saw it as a complex problem. Your elegant formula written without any context (hence not a criticism) doesn't catch the tricky ones and I can't see some of the differences, e.g. a leading number breaks it, but it's not always the same number:

[TABLE="class: grid, width: 500, align: left"]
[TR]
[TD]URL
[/TD]
[TD]Long[/TD]
[TD]Elegant
[/TD]
[/TR]
[TR]
[TD]hxxp://01example.com/auto_insurance_info.htm[/TD]
[TD]01example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://0772.1example.com/%E6%BE%B3%E5%A4%A7%E5%88%A9%E4%BA%9A%E7%BD%91%E5%9D%80%E5%A4%A7%E5%85%A8/[/TD]
[TD]01example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://0777.1example.com/%E6%BE%B3%E5%A4%A7%E5%88%A9%E4%BA%9A%E7%BD%91%E5%9D%80%E5%A4%A7%E5%85%A8/[/TD]
[TD]1example.com[/TD]
[TD]1example.com[/TD]
[/TR]
[TR]
[TD]hxxp://0796.1example.com/%E6%BE%B3%E5%A4%A7%E5%88%A9%E4%BA%9A%E7%BD%91%E5%9D%80%E5%A4%A7%E5%85%A8/[/TD]
[TD]1example.com[/TD]
[TD]1example.com[/TD]
[/TR]
[TR]
[TD]hxxp://1001example.net/travel/australia/index.html[/TD]
[TD]1001example.net[/TD]
[TD]net[/TD]
[/TR]
[TR]
[TD]hxxp://1001example.net/travel/australia/south_pacific.html[/TD]
[TD]1001example.net[/TD]
[TD]net[/TD]
[/TR]
[TR]
[TD]hxxp://1234example.com/car-insurance-quotes/Comprehensive-auto-insurance.htm[/TD]
[TD]1234example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://3points.example.co.uk/car-insurance-how-many-work-weeks-in-a-year-united-states/[/TD]
[TD]example.co.uk[/TD]
[TD]example.co.uk[/TD]
[/TR]
[TR]
[TD]hxxp://5www.example.com/how-big-is-one-acre-in-square-meters/[/TD]
[TD]example.com[/TD]
[TD]example.com[/TD]
[/TR]
[TR]
[TD]hxxp://61example.com/url/662/hxxp://www.example.com.au/business-insurance/[/TD]
[TD]61example.com
[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://99example.com.au/other-business-advertising/contests/create-infographic-cover-travel-insurance-australia-287951[/TD]
[TD]99example.com.au
[/TD]
[TD]com.au[/TD]
[/TR]
[TR]
[TD]hxxp://99example.com.au/other-business-advertising/contests/create-infographic-cover-travel-insurance-australia-287951/brief[/TD]
[TD]99example.com.au[/TD]
[TD]com.au[/TD]
[/TR]
[TR]
[TD]hxxp://99example.com.au/other-business-advertising/contests/create-infographic-cover-travel-insurance-australia-287951/overview[/TD]
[TD]99example.com.au[/TD]
[TD]com.au[/TD]
[/TR]
[TR]
[TD]hxxp://9example.com/alphameric/t?p=261[/TD]
[TD]9example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://9example.com/Business/Insurance/?p=10[/TD]
[TD]9example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://9example.com/Business/Insurance/?p=11[/TD]
[TD]9example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://9example.com/listing/Business/Insurance/travel-insurance-direct-7005[/TD]
[TD]9example.com[/TD]
[TD]com[/TD]
[/TR]
[TR]
[TD]hxxp://example.com.au/[/TD]
[TD]example.com.au[/TD]
[TD]com.au[/TD]
[/TR]
[TR]
[TD]hxxp://aca.example.com.au/article/8869484/no-more-loyalty-in-insurance[/TD]
[TD]example.com.au[/TD]
[TD]example.com.au[/TD]
[/TR]
[TR]
[TD]hxxp://example.com.au/?pg=1[/TD]
[TD]example.com.au[/TD]
[TD]com.au[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[Ron Coderre]

You're right.

Now, just because I was in the mood for an Excel challenge....
I played around with possible solutions and stopped when I got to this one

=MID(MID(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3))-1),8,100),
IFERROR(FIND("|",SUBSTITUTE(LEFT(MID(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3))-1),8,100),
LOOKUP(100,SEARCH({".com",".net",".co."},MID(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",3))-1),8,100)))-1),".","|"))+1,1),100)

Yes, it's as ugly as a mud fence...but, I *think* it's less convoluted.

 

[crm911]

Thanks, Ron. I am getting some inexplicable results for some cells, but I want to study your formula to learn and then have a crack at modifying it.

 

[James006]

Hi,

If you face a complex formula ... take the time to de-construct it ... in order to learn how each of the building blocks contributes to the overall result ...

XOR LX ... and his site: EXCELXOR ...is extremely interesting ...!!!