Slide show of Menus (MAC Excel 2011 version)

Here’s a short list of web pages to find other blogs. To further your knowledge of Excel or diversify into other fields

Just making sure you’re a geek too.

<!– Begin of mycountdown.org script –> <div align=”center” style=”margin:15px 0px 0px 0px”> <noscript> <div align=”center” style=”width:140px;border:1px solid #ccc; background: #C43F05; color: #F9F9FF;font-weight:bold;font-size:12px;”> <a style=”text-decoration: none; color:#F9F9FF;” href=”http://mycountdown.org/My_Countdown/My_countdown/”>My Countdown </a></div> </noscript> <script type=”text/javascript” src=”http://mycountdown.org/countdown.php?cp3_Hex=0F0200&cp2_Hex=C43F05&cp1_Hex=F9F9FF&ham=0&img=&hbg=0&hfg=0&sid=0&fwdt=200&lab=1&text1=AVENGERS MOVIE&text2=Release date&group=My Countdown&countdown=My Countdown&widget_number=3011&event_time=1336089600&timezone=UTC”></script> </div> <!– End of mycountdown.org script –>

Here’s a little tip for helping to organize complicated formulas.

**ALT+ENTER**

It works for text and formulas. ALT+ENTER sends whatever you’re typing to the next row within the same cell. If it’s text and you have wrap-around turned on it’s helpful for Headers (and etc.) but that is not why I’m mentioning ALT+ENTER.

**Here’s an example of a (very) complicated formula.**

**WITHOUT ALT+ENTER**

=IF(Start_Linear_On=”YES”,IF(AND(ISERROR(1/COUNTIFS(Linear_Plant, Start_Plant_Number,Liner_Component_Number,$B2))FALSE,Start_Linear_ Plant=”YES”)=TRUE,SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,St art_Plant_Number,Liner_Component_Number,$B2)/COUNTIFS(Linear_Pla nt,Start_Plant_Number,Liner_Component_Number,$B2),IF(Start_Linear_Pl ant=”YES”,SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,”ALL”,Liner _Component_Number,$B2)/COUNTIFS(Linear_Plant,”ALL”,Liner_Compon ent_Number,$B2),VLOOKUP($B2,dim,2,FALSE)))*IF(Start_Linear_Pallet= “YES”,MAX(1,IF(ISERROR(1/SUMIFS(Linear_Pallet_size_Multiplier,Linear _Plant,Start_Plant_Number,Liner_Component_Number,$B2))=TRUE,IF(IS ERROR(1/COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2 ))=TRUE,1,SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,”ALL”,Lin er_Component_Number,$B2)/COUNTIFS(Linear_Plant,”ALL”,Liner_Comp onent_Number,$B2)),MAX(1,IF(ISERROR(1/COUNTIFS(Linear_Plant,Star t_Plant_Number,Liner_Component_Number,$B2))=FALSE,IF(Start_Linear _Plant=”YES”,SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,Start_P lant_Number,Liner_Component_Number,$B2)/COUNTIFS(Linear_Plant,St art_Plant_Number,Liner_Component_Number,$B2),VLOOKUP($B2,dim,3, FALSE)),1)))),1),#N/A)

**WITH ALT+ENTER**

=IF(Start_Linear_On=”YES”,

IF(AND(ISERROR(1/COUNTIFS(Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2))=FALSE,Start_Linear_Plant=”YES”)=TRUE,

SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,Start_Plant_Number,Liner_Component_Number,$B2),

IF(Start_Linear_Plant=”YES”,

SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,”ALL”,Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2),

VLOOKUP($B2,dim,2,FALSE)))

*

IF(Start_Linear_Pallet=”YES”,

MAX(1,IF(ISERROR(1/SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant, Start_Plant_Number,Liner_Component_Number,$B2))=TRUE, IF(ISERROR(1/COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number, $B2))=TRUE,

1,

SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,”ALL”, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2)),

MAX(1,IF(ISERROR(1/COUNTIFS(Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2))=FALSE,

IF(Start_Linear_Plant=”YES”,

SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,Start_Plant_Number,Liner_Component_Number,$B2),

VLOOKUP($B2,dim,3,FALSE)),1)))),1),

#N/A)

…maybe it’s an overcomplicated answer but it can be helpful for separating information to help change the formula later if needed.

Good Luck!

I liked Excel 2007. It was a huge leap over many of the limitations of Excel 2003. I little hard to get used to at first but when worth the effort. Now I’m using Excel 2010. It’s faster (side by side compared the two) and the menus can be customized. (it wasn’t easy to customize in 2007.)

…but there is a catch with 2010. I think within large complex calculations, accuracy was sacrificed for speed. Holy crap! The Grail of divine calculation accuracy is at stake!

(It may be related to needing the newest version of Windows and the best 64 bit processor to correct these issues, or that may just be a bunch of hooey to get the consumer to consumer more.)

**Here’s what issues I’ve discovered about Excel 2010.**

- If you
**filter**by a color related to a complex**conditional formatting**, you may not see all of your data, and may see lines that aren’t of the color you formatted by. - In the tab ‘Data’ in ‘Data Tools’ is the button ‘
**Remove Duplicates**‘. It’s a brilliant idea. (I may be partially saying that because I wrote a macro in Excel 2003 to do the same thing and now it feels like Microsoft was watching. [I’m kidding about the Microsoft watching part, I’m not paranoid, just sarcastic]) Be careful about trusting your results after clicking this button. I had a list of data lines 97,000 lines, and had to click ‘Remove Duplicates’ 4 times before the result accurately removed all the duplicate lines. It’s useful, but always double check your data. I just keep clicking the button until no more are found and that worked (for now…). - I seems in my mind there was a third issue I had with Excel 2010, but I’m drawing a blank. Now don’t get me wrong. I live Excel, and it is a very useful program, but don’t forget “Only you can prevent forest fires.”. In other words the end result is your fault! If it’s true or false, always make sure to confirm you data.

The most useful shortcut at saving you from using your mouse is F2. (I try to use my mouse as little as possible, and learn as many short cuts as can be found. It’s faster and when somebody is looking over your shoulder it confuses the hell out of them, since they don’t know how it’s possible.)

F2 – Edit the Active Cell. (No having to double click it!)

**And Here’s a few more shortcuts for Editing Data…**

F3 – Paste a defined name into a formula

CTRL+A – After typing a formula (and a “(“), this will bring up the “Function Arguments”.

CTRL+SHIFT+A – Kind of like CTRL+A, but not as useful to me. Try it and tell me if you like it. (to start: After typing a formula (and a “(“), this will bring up…)

Enjoy!

There are many useful mathematical formulas. SUM, SUMIF, SUMIFS (in version 2007 of better), COUNT, STDEV, etc. These have a great range of versatile uses when combined.

..but there are two formulas I use more then anything else. IF and VLOOKUP

IF(x=b,c,d) This is a great easy way to make a choice based on a simple of complicated premise. It either slips out ‘c’ or ‘d’ depending on if x=b is true or false.

IF is easy to use. VLOOKUP… can be confusing.

**VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])** I know you’re say ‘HUH?’ already.

Say I have a long list of Part Numbers at my plant (in column A) in conjunction with a long list of Descriptions for those Part Numbers (in column B). Here’s a tip. Select these columns and click in the Name Box in the upper left corner and type a name to call this array (aka table_array) of data. Let’s call it ‘PARTS’.

Name Box:

On a separate tab I have a list of Part Numbers (on for illustration it’s just in cell A1), but in column B I want to type a formula to return the Description associated with that Part Number. VLOOKUP is the answer.

Your **vlookup_value** is the Part Number you want to find a description for in cell A1.

Your** table_array** is ‘PARTS’, which is more useful a description the going to the other list and selecting the two columns of data.

Your **col_index_num **for this example is 2. 2 is referring to which column of your array you want to return and spit out in the cell in which you’re typing VLOOKUP. 2 doesn’t necessarily refer to column B. If your array is B:C (instead of A:B), then C is column 2.

WIth **[range_lookup] **you will want to just type **FALSE**. If you type TRUE, then you’ll get the closest approximation to the Part Number you’re looking for and that would be very bad. 99.9% you’ll want to type FALSE here.

So your final formula should look like =VLOOKUP(A1,PARTS,2,FALSE)

***QUICK TIP. If you getting a N/A# result, make sure that the Part Number in your array if the farthest left column of the array. This is the most common mistake I get asked about. When I review a spreadsheet, most the time it’s because the array.

Well, thanks for scrolling and Good Luck!

Nothing looks more sloppy then spelling errors.

Use spellcheck. It’s the shortcut F7.

…but don’t completely depend on spellcheck. Just because it’s a correctly spelled word, doesn’t mean it’s the correct word.