Thread for posting confusing functions

[vividox]

I've never had a problem with debugging. Run Evaluate Formula once to see where everything is coming from and you pretty much know all there is to know about the formula. Then you change it from there.

[Gedankenexperiment]

Originally Posted by vividox

=IF(T3="TTT",VLOOKUP(U3,Rates!$A$153:$G$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!$A$153:$G$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!$I$153:$O$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!$I$168:$O$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!$I$168:$O$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE))

and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?

Yeah, mess. Not that confusing, but also why use excel in the first place if you're not gonna show any work? You need someone to slap your hand with a ruler.

Also I find numbers written and compared as strings to be disturbing. It bothers me when "4000"<"670".

[ADoubleDot]

Originally Posted by vividox

I guess there is a great deal of ambiguity when saying "megaformula" as well. What do you consider a megaformula? This is in one of my first worksheets I made:

=IF(T3="TTT",VLOOKUP(U3,Rates!$A$153:$G$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!$A$153:$G$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!$I$153:$O$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!$I$168:$O$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!$I$168:$O$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE))

and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?

That's a mess. I wouldn't hardcode things like "TTT" in case that flag ever needed to change. Or if you wanted to apply the same calculation to a different whatever-the-****-that-is.

Most evident though, is that the whole thing could be replaced by index(match,match) which would be less retarded than what you are doing.

[vividox]

Originally Posted by Gedankenexperiment

Yeah, mess. Not that confusing, but also why use excel in the first place if you're not gonna show any your work? Also I find numbers written and matched as strings to be disturbing. It bothers me when "4000"<"670".

Not sure if I really understand your second sentence. In this particular case, I'm looking up factors for a commercial auto book of business. All this formula is doing is looking up the physical damage factor (which is, of course, dependent of the type of vehicle we are talking about, hence the funky logic). There are many, many, many other factors that go into rating, so drawing out the retrieval of a single factor seems like a waste of space to me.

This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.

[vividox]

Originally Posted by ADoubleDot

That's a mess. I wouldn't hardcode things like "TTT" in case that flag ever needed to change. Or if you wanted to apply the same calculation to a different whatever-the-****-that-is.

Most evident though, is that the whole thing could be replaced by index(match,match) which would be less retarded than what you are doing.

Yeah, same thing with the overabundance of IF(C3=3,4,6) and such. If my columns ever change, that's a lot of formula editing. Like I said, this was one of my first projects and I did a lot of stupid things on this worksheet. It still manages to get the job done.

I'm not sure how it could be replaced by INDEX(MATCH,MATCH) though, each one of the VLOOKUPs is going to a different table.

[epeddy1]

Originally Posted by vividox

I guess there is a great deal of ambiguity when saying "megaformula" as well. What do you consider a megaformula? This is in one of my first worksheets I made:

=IF(T3="TTT",VLOOKUP(U3,Rates!$A$153:$G$289,IF(C3= 24,2,IF(C3=3,4,6)),FALSE)+IF(U3>"670",0,VLOOKUP(V3 ,Rates!$A$153:$G$289,IF(C3=24,2,IF(C3=3,4,6)),FALS E)),0)+IF(T3="PPT",VLOOKUP(U3,Rates!$I$153:$O$164, IF(C3=24,2,IF(C3=3,4,6)),FALSE),0)+IF(T3="OTHER",V LOOKUP(U3,Rates!$I$168:$O$176,IF(C3=24,2,IF(C3=3,4 ,6)),FALSE)+VLOOKUP(V3,Rates!$I$168:$O$176,IF(C3=2 4,2,IF(C3=3,4,6)),FALSE))

and while lengthy, it's really just a glorified VLOOKUP. Would you consider this a megaformula or are you thinking more along the lines of a lot of logic and combination of formulas?

I'll give a simple example. Lets say that you're calculating the PV of a stream of cash flows. You need the following functions:

• Your "n" requires a few functions to correspond to the current date, convert years into quarters, and whatever else. Lets just say there are few different "if" statements and "vlookups" to determine the effective "current" date.
• Your interest rate isn't constant over time, and requires some vlookups to pull rates from another tab that contains the curve and convert them to "convertible quarterly" or whatever.
• Your cash flows have an if statement and a vlookup, and maybe some function to manipulate the amounts some way.
• Combine all previous with your favorite PV formula.

Again, this is a simplified example. I personally like to perform different functions in different columns so I can make sure the results are what I want. If all the formulas are imbedded into one cell, there's really no way to make sure the "n", rates, and cash flows are reasonable before it spits out the PV. If you ever have to go back to the spreadsheet, and say this time around you don't want "convertible quarterly" interest rates and want "convertible semi", it's easier to find which column you don't need anymore and fix it that way, rather than digging through a megaformula and making sure all the logic is still kosher. If it looks sloppy with all the columns, I can make it pretty by having a "summary" tab that summarizes the interesting columns.

Just my humble opinion.

[vividox]

Originally Posted by epeddy1

If all the formulas are imbedded into one cell, there's really no way to make sure the "n", rates, and cash flows are reasonable before it spits out the PV.

Sure there is. "Evaluate Formula" on the Formulas ribbon does exactly that.

[Gedankenexperiment]

Originally Posted by vividox

Not sure if I really understand your second sentence. In this particular case, I'm looking up factors for a commercial auto book of business. All this formula is doing is looking up the physical damage factor (which is, of course, dependent of the type of vehicle we are talking about, hence the funky logic). There are many, many, many other factors that go into rating, so drawing out the retrieval of a single factor seems like a waste of space to me.

This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.

IF(U3>670,1,0) is very different than IF(U3>"670",1,0).

This is showing my work, in this instance, since I'm showing all the individual factors for given policy types/limits, then using them to calculate the final premium.

Showing your work would be like.
(Factors)
PPT 0
TTT 30
Other 50
----
Prem 80

It's not necessary, but the whole beauty of spreadsheets is that you get to see every step. It's easier to spot errors (because you might not notice Prem is off) and easier to read (because you don't have to evaluate the formula or even know what "VLOOKUP" means to see what's happening). Plus, you might want to do something else with those factors.

[vividox]

Originally Posted by Gedankenexperiment

IF(U3>670,1,0) is very different than IF(U3>"670",1,0).

Right. The inputs are cropped numbers, so they are text strings, not numbers. They are also all 3 digit text strings, conveniently enough.

Originally Posted by Gedankenexperiment

Showing your work would be like.
(Factors)
PPT 0
TTT 30
Other 50
----
Prem 80

It's not necessary, but the whole beauty of spreadsheets is that you get to see every step. It's easier to spot errors (because you might not notice Prem is off) and easier to read (because you don't have to evaluate the formula or even know what "VLOOKUP" means to see what's happening).

I get what you are saying in general, and in general I would agree with you. However, in this case, I'm calculating a different policy on every row, and that policy is either a TTT, PPT, or Other, exclusive. So the formula may reduce to =0+0+80 or =0+80+0 but it's impossible for it to be anything like =0+30+50. Using a plus here was simply easier than using a super long conditional IF.

[Gedankenexperiment]

Originally Posted by Gedankenexperiment
IF(U3>670,1,0) is very different than IF(U3>"670",1,0).
Right. The inputs are cropped numbers, so they are text strings, not numbers. They are also all 3 digit text strings, conveniently enough.

Ohyeah, car insurance has all those little number chunks. My policy is rated 02R284AZ93OMFGBBQ2!

Originally Posted by vividox

I get what you are saying in general, and in general I would agree with you. However, in this case, I'm calculating a different policy on every row, and that policy is either a TTT, PPT, or Other, exclusive. So the formula may reduce to =0+0+80 or =0+80+0 but it's impossible for it to be anything like =0+30+50. Using a plus here was simply easier than using a super long conditional IF.

I see, I would have probably moved the tables to align horizontally or vertically, so they could be in a single lookup. But maybe you couldn't move them. Not a huge deal either way.