# WOO!  I am nesting like a Motherf\*\*\*!

**URL:** <https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480>\
**Category:** Miscellaneous and Personal Stuff I Must Share\
**Created:** [March 22, 2006, 8:55pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480 "2006-03-22T20:55:27Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Inigo\_Montoya](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/inigo_montoya/32/124_2.png) [@Inigo\_Montoya](https://boards.straightdope.com/u/Inigo_Montoya)\
**Post date:** [March 22, 2006, 8:55pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/1 "2006-03-22T20:55:27Z")

</div>

Doing some work-related stuff in MS Excel. Discovered a couple weeks ago that you can nest commands rather than corral your information some other way by means of say, place holding cells in an obscure part of the worksheet. “Groovy,” sez I. “And it says here you can nest up to 7 commands in one line. Who in the world would need to nest 7 commands?!”

Heh…read 'em and weep:

= **IF** (D2\>=C2,"", **CONCATENATE** (“More UNREVIEWED !!! Do “, **ROUNDUP** ( **SUM** ( **PRODUCT** ( **PRODUCT** (G1,3)-B2)/ **SUM** (G1+1-F1)),0),” per day to catch up”))  
What’s your best Excel effort?

---

<div class="post-metadata">

**Author:** ![QuickSilver](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/quicksilver/32/7832_2.png) [@QuickSilver](https://boards.straightdope.com/u/QuickSilver)\
**Post date:** [March 22, 2006, 9:10pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/2 "2006-03-22T21:10:17Z")

</div>

I’m currently in the process of trying to import an Excel file into a SharePoint custom list…

Goddamn Microsoft! :mad:

---

<div class="post-metadata">

**Author:** ![Mindfield](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mindfield/32/19759_2.png) [@Mindfield](https://boards.straightdope.com/u/Mindfield)\
**Post date:** [March 22, 2006, 9:14pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/3 "2006-03-22T21:14:08Z")

</div>

Heh.

In Excel, that’s called power and efficiency.

In programming, that’s called _spaghetti_.

---

<div class="post-metadata">

**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\
**Post date:** [March 22, 2006, 9:21pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/4 "2006-03-22T21:21:19Z")

</div>

=IF(A1\>5035,IF(A1\<33400,(A1-5035)\*22%,(A1-33400)\*40%+(33400-5035)\*22%),A1)

Calculates your annual UK income tax, where A1 is your gross annual salary, based on today’s budget.

I think.

---

<div class="post-metadata">

**Author:** ![Inigo\_Montoya](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/inigo_montoya/32/124_2.png) [@Inigo\_Montoya](https://boards.straightdope.com/u/Inigo_Montoya)\
**Post date:** [March 22, 2006, 9:24pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/5 "2006-03-22T21:24:05Z")

</div>

> [@Mindfield](#):
>
> In programming, that’s called _spaghetti_.

Heh, took Pascal & BASIC (back when they were kinda common) and retained just enough programming philosophy to feel kinda dirty about nesting 7 commands into one line. Powerful and efficient, yes. But I couldn’t help feeling like a Programmer would choke on his coffee if he had to work through that kind of code salad on a regular basis. 😃

---

<div class="post-metadata">

**Author:** ![Zog\_10](https://avatars.discourse-cdn.com/v4/letter/z/c57346/32.png) [@Zog\_10](https://boards.straightdope.com/u/Zog_10)\
**Post date:** [March 22, 2006, 9:28pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/6 "2006-03-22T21:28:32Z")

</div>

Let’s see:

=IF(ISERROR(VLOOKUP(ROUND(D38,2),Yds\_Pass\_Att.,3,TRUE)),0,VLOOKUP(ROUND(D38,2),Yds\_Pass\_Att.,3,TRUE))

=IF(ISERROR(VLOOKUP(ROUND(H13,2),Yds\_Rush\_Att.,3,TRUE)),0,VLOOKUP(ROUND(H13,2),Yds\_Rush\_Att.,3,TRUE))

=IF(ISERROR(VLOOKUP(ROUND(K13,2),Yds\_Rec.,3,TRUE)),0,VLOOKUP(ROUND(K13,2),Yds\_Rec.,3,TRUE))

Only people who like comments call it _spaghetti_

---

<div class="post-metadata">

**Author:** ![lisacurl](https://avatars.discourse-cdn.com/v4/letter/l/41988e/32.png) [@lisacurl](https://boards.straightdope.com/u/lisacurl)\
**Post date:** [March 22, 2006, 9:32pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/7 "2006-03-22T21:32:57Z")

</div>

The combination of the reference of the title of this thread and its nerdly contents, which sadly, I understand and appreciate fully, is making me chortle wildly.

---

<div class="post-metadata">

**Author:** ![Mindfield](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mindfield/32/19759_2.png) [@Mindfield](https://boards.straightdope.com/u/Mindfield)\
**Post date:** [March 22, 2006, 9:34pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/8 "2006-03-22T21:34:24Z")

</div>

> [@Inigo Montoya](#):
>
> Heh, took Pascal & BASIC (back when they were kinda common) and retained just enough programming philosophy to feel kinda dirty about nesting 7 commands into one line. Powerful and efficient, yes. But I couldn’t help feeling like a Programmer would choke on his coffee if he had to work through that kind of code salad on a regular basis. 😃

I programmed in several flavours of BASIC back in the day (“the day” being mid-80s onward) so I became very good buddies with spaghetti code. The ultimate antithesis to that came when I picked up 65xx/68xxx assembler. If BASIC was spaghetti, assembler was couscous. 🙂

---

<div class="post-metadata">

**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\
**Post date:** [March 22, 2006, 9:34pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/9 "2006-03-22T21:34:40Z")

</div>

=IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0)

Calculates your annual UK National Insurance contribution where A1 is your gross salary.

Therefore:

=IF(A1\>5035,IF(A1\<33400,(A1-5035)\*22%+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0),(A1-33400)\*40%+(33400-5035)\*22%)+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0),A1+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0))

Calculates your annual PAYE deductions.

I think.

---

<div class="post-metadata">

**Author:** ![Ruffian](https://avatars.discourse-cdn.com/v4/letter/r/71e660/32.png) [@Ruffian](https://boards.straightdope.com/u/Ruffian)\
**Post date:** [March 22, 2006, 11:48pm UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/10 "2006-03-22T23:48:19Z")

</div>

Whoo hoo! I’m nesting too! Ain’t it fun! All the vacuuming, the scrubbing, the organizing (and re-organizing), washing all the cute little baby clothes, packing the hospital bags, sitting in the rocking chair…

…oh wait. _Totally_ different kind of nesting.

[Emilie Litella]_Nevermind._[/Emilie Litella]

---

<div class="post-metadata">

**Author:** ![Electronic\_Chaos](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@Electronic\_Chaos](https://boards.straightdope.com/u/Electronic_Chaos)\
**Post date:** [March 23, 2006, 12:26am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/11 "2006-03-23T00:26:18Z")

</div>

> [@jjimm](#):
>
> =IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0)
> 
> Calculates your annual UK National Insurance contribution where A1 is your gross salary.
> 
> Therefore:
> 
> =IF(A1\>5035,IF(A1\<33400,(A1-5035)\*22%+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0),(A1-33400)\*40%+(33400-5035)\*22%)+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0),A1+IF(A1/52\>84,IF(A1/52\>97,IF(A1/52\<645,((A1/52-97)\*11%)\*52,((A1/52-645)\*1%+(A1/52-97)\*11%)\*52),0),0))
> 
> Calculates your annual PAYE deductions.
> 
> I think.

Well, _I_ think you just broke the boards.

---

<div class="post-metadata">

**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\
**Post date:** [March 23, 2006, 1:22am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/12 "2006-03-23T01:22:24Z")

</div>

=IF($C5="","",IF(RIGHT($C5,1)=" “,VLOOKUP(LEFT($C5,5),‘IFR Waypoints’!$A$1:$C$32000,2,FALSE()),IF(OR(RIGHT($C5,2)=” n",RIGHT($C5,2)=" v"),VLOOKUP($C5,Navaids!$A$1:$H$32000,6,FALSE()),VLOOKUP($C5,‘GPS Waypoints’!$A$1:$D$32015,3,FALSE()))))

Inputs the latitude and longitude of a waypoint, navigation aid, or user created GPS waypoint sourced from three tables.

---

<div class="post-metadata">

**Author:** ![mrald](https://avatars.discourse-cdn.com/v4/letter/m/3da27b/32.png) [@mrald](https://boards.straightdope.com/u/mrald)\
**Post date:** [March 23, 2006, 1:59am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/13 "2006-03-23T01:59:54Z")

</div>

Am I the only person in the world, or at the very least on the board, that has no Friggin clue what that is or how to really use it?

---

<div class="post-metadata">

**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\
**Post date:** [March 23, 2006, 2:19am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/14 "2006-03-23T02:19:46Z")

</div>

Are you talking about my Excel “equation” or something else?

---

<div class="post-metadata">

**Author:** ![GIGObuster](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gigobuster/32/421_2.png) [@GIGObuster](https://boards.straightdope.com/u/GIGObuster)\
**Post date:** [March 23, 2006, 2:45am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/15 "2006-03-23T02:45:20Z")

</div>

> [@mrald](#):
>
> Am I the only person in the world, or at the very least on the board, that has no Friggin clue what that is or how to really use it?

Don’t worry, those are are only formulas that one uses in Excel, CONCATENATE is a way to deal with text information:

[http://www.excel-vba.com/microsoft-excel-text-formulas.htm](http://www.excel-vba.com/microsoft-excel-text-formulas.htm)  
Concatenation: Assembled strings of text (to concatenate)

> [@](#):
>
> For example:If you have Pe in cell A1 and ter in cell A2 the formula =A1 & A2 in cell A3 will return Peter. We call this a concatenation.
> 
> If you have 1 in cell A1 and 2 in cell A2 the formula =A1+A2 will return 3. The formula =A1 & A2 will return 13 because concatenating is not adding it is creating a chain of characters with the content of many cells. The result 13 is not even a number with which you could execute calculations it is a text just like Peter.
> 
> The TEXT functions in Excel are great “Time Saving” tools. When you have data that you receive from colleagues, clients or suppliers, when you download data from a database or the Internet and the format is not right for you, you need to RE-ENTER the data manually and this task is time consuming, error prone and very frustrating. The TEXT functions will allow you to do the reformatting automatically.

In the OP the command essentially will look for the complaint “More UNREVIEWED!!!” in the spreadsheet. Once found, SUM will add up the offending data.

---

<div class="post-metadata">

**Author:** ![Inigo\_Montoya](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/inigo_montoya/32/124_2.png) [@Inigo\_Montoya](https://boards.straightdope.com/u/Inigo_Montoya)\
**Post date:** [March 23, 2006, 2:51am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/16 "2006-03-23T02:51:10Z")

</div>

What the OP command _really_ does is display either no comment OR a command to review X# more new claims per day to catch up to get on time to meet a monthly quota.

The target goal is a number calculated by and displayed in C2. D2 is the current quota status for the period being monitored. The ensuing math functions calculate where the user should be with his quota, and if he’s behind, how many more he needs to pull on a daily basis to meet the minimum period target goal.

I’ve not half become a boring bastard.

---

<div class="post-metadata">

**Author:** ![Baffle](https://avatars.discourse-cdn.com/v4/letter/b/dec6dc/32.png) [@Baffle](https://boards.straightdope.com/u/Baffle)\
**Post date:** [March 23, 2006, 3:04am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/17 "2006-03-23T03:04:34Z")

</div>

=IF(C233=1,+O224,IF(C233=2,+O225,IF(C233=3,+O226,IF(C233=4,+O227,IF(C233=5,+O228,IF(C233=6,+O229,IF(C233=7,+O230,IF(C233=8,+O231,+D222))))))))

Uses a dropdown menu to select the proper result. Sadly the seven-nested commands wasn’t even enough. That is in cell C222, cell D222 right beside it is:  
=IF(C233=9,+O232,IF(C233=10,+O233,IF(C233=11,+O234,+O235)))

Room to accomodate the rest.

And that’s not all, it’s oversimplified. All of those O’s are pointing to a table of sums which could theoretically all be in the formula itself, but I simply couldn’t wrap my brain around that much complication, so I took the above-mentioned route of stashing numbers in out of the way corners of the spreadsheet.

---

<div class="post-metadata">

**Author:** ![Sublight](https://avatars.discourse-cdn.com/v4/letter/s/439d5e/32.png) [@Sublight](https://boards.straightdope.com/u/Sublight)\
**Post date:** [March 23, 2006, 6:37am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/18 "2006-03-23T06:37:27Z")

</div>

I wish this is what I used Excel for.

Right now, I’m translating and (very extensively) rewriting a bunch of internal sales guides for one of our clients. This client is very comfortable using Excel. In fact, it seems like it’s the only software he’s comfortable using. This would be ok if he were using Excel to arrange tables of data, but he’s not.

He’s using it as a graphics program. Not pie charts or anything data related, he’s actually using it to produce images. He uses it for word processing, too, blithely ignoring whether any of his text lines up anywhere, or if it’s even visible. He uses it for every possible purpose except the one it was designed for. And every fricken’ page has then been exported to PowerPoint as a separate embedded file and made into a slide presentation.

On one page was a mock-up of a pop-up window that needed its text re-written. When I clicked on the text, the entire image selected. “Great, another embedded Excel file,” I thought, and indeed it was. However, when I tried to select the text within Excel, I discovered that he’d actually made the thing in MS Paint, exported it to Excel, then exported _that_ to PowerPoint. It was garbage wrapped in slime encased in a glistening layer of shit.

---

<div class="post-metadata">

**Author:** ![slaphead](https://avatars.discourse-cdn.com/v4/letter/s/e9bcb4/32.png) [@slaphead](https://boards.straightdope.com/u/slaphead)\
**Post date:** [March 23, 2006, 10:48am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/19 "2006-03-23T10:48:33Z")

</div>

=IF(AND(H227=0,L227=0,L232=0),L238_M146,IF(ISERROR(IF(‘CA Questionnaire’!M73\>0,‘CA Results’!K227_’CA Results’!M144,‘CA Results’!K227_K232_’CA Results’!M145)),“N/A”,IF(‘CA Questionnaire’!M73\>0,‘CA Results’!K227\*‘CA Results’!M144,‘CA Results’!K227_K232_’CA Results’!M145)))

Or in other words, keep looking till you find enough inputs to do one of several sums which will give you a wild guess at an approximation to a sensible answer, while never allowing any telltale #N/A!, #DIV/0! or #WTF! messages to appear which might tip off the user what is going on.

> [@sublight](#):
>
> garbage wrapped in slime encased in a glistening layer of shit

Oh man. My sympathies. I have run into things like that. Some finance bod once emailed me a nicely formatted bullet-pointed status update done in Excel. Looked just like a word document when printed out, but hooooooo boy. Individual wingding characters in cells. One cell per line of text. Pages of it. Hours of effort on his part. All needing to be torn apart and redone properly. Oh, the humanity!

---

<div class="post-metadata">

**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\
**Post date:** [March 23, 2006, 11:26am UTC](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480/20 "2006-03-23T11:26:28Z")

</div>

One thing I find annoying about Excel is that you sometimes have to waste several of your nested functions (seven isn’t always enough) just to specify that you’d like a “0” or “N/A” cell to display as empty. There should be an option for each cell (probably accessed from the “format” settings) to display nothing in place of any null and/or error messages.

[Next page](https://boards.straightdope.com/t/woo-i-am-nesting-like-a-motherf/349480.md?page=2)