Posted: 10/30/2012 10:14:32 AM EDT
|
I have an interview at another department within the same agency I currently work for. Part of the interview process is providing an on-site writing sample, Excel Sample and taking an Integrity test.
I have experience with Excel but am not an Excel ninja. Can anyone give me a few pointers on some functions/formulas I should study up on? I don't have much time to study so I can't just take a class. Has anyone here had to provide an Excel sample before? What am I in for? And what is an integrity test? Thanks. |
|
Quoted: I have an interview at another department within the same agency I currently work for. Part of the interview process is providing an on-site writing sample, Excel Sample and taking an Integrity test. I have experience with Excel but am not an Excel ninja. Can anyone give me a few pointers on some functions/formulas I should study up on? I don't have much time to study so I can't just take a class. Has anyone here had to provide an Excel sample before? What am I in for? And what is an integrity test? Thanks. Pivot tables and linear regression graphs or you really don't know Excel. |
|
Quoted:
Learn to do vlookups and maybe a few if statements What does the dept you're trying to get into do? that may help us with some suggestions I always find myself writing repetive formulas if I use if then statements. Now I use this format - ((a1>=a2)*b2)+((a1<a2)*b3). Hope that makes sense |
|
In interviews for financial or analyst positions I always ask questions about importing CSV files. Excel is pitiful at that, and you need a few tricks to do it correctly. Copy the .csv file a .txt file then import it. Manually select columns that don't import correctly like ones that have leading zeros or large numbers (since Excel truncates them) and convert them to text formatting. That keeps Excel from corrupting fields that can contain leading zeros like ZIP codes or part numbers.
Example: Seq,PartNumber,Name,Description |
|
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? |
|
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. |
|
Quoted:
I'm an Excel whiz*, but I've never used Pivot Tables. *I once used an Excel spreadsheet to automate 3D model creation in Solidworks.  Ok, I just looked up the definition of Pivot Tables. I use them all the time, but never knew they were called that. 
EDIT: Looks like I hit the quote button instead of edit. 
|
|
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. BLASPHEMY!! I thought the same as you for the first 3 minutes, then I was like HOLY SHIT THIS IS AWESOME. |
| Don't know what industry you are in but it might be worthwhile to look up time value of money type things also if you don't know them. Pv, fv, pmt, rate, etc. also know how to make an amortization table. Shoot me an Im if you are in real estate or finance and I can give you a few things to help you out. |
|
Quoted:
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. BLASPHEMY!! I thought the same as you for the first 3 minutes, then I was like HOLY SHIT THIS IS AWESOME. I didn't find anything awesome about it. PITA is more like it. |
|
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. Get the add in that converts the menu options back to the 2003 style. Winning! |
|
Quoted:
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. Get the add in that converts the menu options back to the 2003 style. Winning! This has possibilities. Thanks! |
|
OP, if you can figure out what these formulas do, you should be golden. Counts are very helpful. Also, not many people know about the NA() command, which is very helpful when creating data plots and avoids false data points to show up.
=IF(COUNT(Pitch!R222, Yaw!R222)=0, "", ((74*(TAN(RADIANS(Pitch!R222))))^2+(74*(TAN(RADIANS(Yaw!R222))))^2)^0.5) =IF(COUNT(Chg_Parallelism!U71:U85)=0, NA(), AVERAGE(Chg_Parallelism!U71:U85)) |
|
Quoted: Quoted: Quoted: Quoted: Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. BLASPHEMY!! I thought the same as you for the first 3 minutes, then I was like HOLY SHIT THIS IS AWESOME. This. Love the ribbon! I have used excel to its limits from version 97 and up. I prefer 2010 over them all.
|
|
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? I am familiar with Pivot Tables as I use them for book keeping, Budget Management and invoice tracking in my current position. I am furnished with Excel 2003 at my current place of employment.  . I wish I had a newer version.
|
|
You should also know the difference between relative and absolute cell references. For example, if you have this:
@SUM(A1..A25) And copy it to a new row, the range will change. That might be what you want, but it might not. If you change it to this: @SUM($A$1..$A$25) Then the range will stay the same no matter where you copy it to.z |
|
Quoted:
OP, if you can figure out what these formulas do, you should be golden. Counts are very helpful. Also, not many people know about the NA() command, which is very helpful when creating data plots and avoids false data points to show up. =IF(COUNT(Pitch!R222, Yaw!R222)=0, "", ((74*(TAN(RADIANS(Pitch!R222))))^2+(74*(TAN(RADIANS(Yaw!R222))))^2)^0.5) =IF(COUNT(Chg_Parallelism!U71:U85)=0, NA(), AVERAGE(Chg_Parallelism!U71:U85)) What is this sorcery? Looks like rocket science. |
|
It would help to know what field the job is in. We could provide better suggestions then. But what has been mentioned covers a lot of it.
Pivot tables Conditional formatting Importing CSV files Writing Macros If-then statements Drop down lists Naming ranges of cells Data validation Specialized formulas The list is pretty long, but it can be narrowed down by field. |
|
Quoted:
Quoted:
OP, if you can figure out what these formulas do, you should be golden. Counts are very helpful. Also, not many people know about the NA() command, which is very helpful when creating data plots and avoids false data points to show up. =IF(COUNT(Pitch!R222, Yaw!R222)=0, "", ((74*(TAN(RADIANS(Pitch!R222))))^2+(74*(TAN(RADIANS(Yaw!R222))))^2)^0.5) =IF(COUNT(Chg_Parallelism!U71:U85)=0, NA(), AVERAGE(Chg_Parallelism!U71:U85)) What is this sorcery? Looks like rocket science. just simple =IF formulas filled with scary looking calcs |
|
Quoted:
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. Get the add in that converts the menu options back to the 2003 style. Winning! Is this the UBitMenu menu add-in, or something else? Link for UBitMenu: http://www.ubit.ch/software/ubitmenu-languages/ |
|
A more advanced topic is knowing when not to use a spreadsheet. Many studies have shown 25% or higher cell error rates. Here's a good summary:
http://panko.shidler.hawaii.edu/SSR/devexpt.htm Where I work, I taught all of the financial and marketing people how to use SQL. That helped us find a stunning number of errors in our spreadsheets. I'm not trying to pick on our financial guys, but even though all four had MBAs from good schools and more than a decade each in experience with spreadsheets, they still made serious errors. They're great tools for fast and dirty work, but when you start making changes such as adding or removing columns or having multiple people make changes, it's very easy to make an error that is not detected.z |
|
Quoted:
Quoted:
I'm an Excel whiz*, but I've never used Pivot Tables. *I once used an Excel spreadsheet to automate 3D model creation in Solidworks. go on.... VBA FTW Used Excel for the user interface with different drop downs and bullets for the inputs. Solidworks can be controlled very easily using VBA, so the coding took those inputs and created/modified the models from within Solidworks at the click of a button. It was pretty cool. Unfortunately, I left that job after only working on it for 3-4 weeks, so I wasn't able to complete it. But I got far enough into it to give my employer a taste and they went and found a firm that created custom software to accomplish the same task after I left. I loved doing that stuff. Wish I would have stuck around there. Now I design "rigid" products for engines.
|
|
Please see this thread as well.
http://www.ar15.com/forums/t_1_5/1384276_.html&page=1 |
|
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Just go to youtube and type in any of these terms people are using and there will be a video explaining what they do and how to do them. I just learned about pivot tables and I am going to try it out myself now! Try explaining pivot tables to people who used Lotus on a 5.25" floppy. /sigh/ They are darn cool, though! Data filtering - how did I do without this?! Which version of Excel - 2003, 2007, 2010, or something else? 2010 and doing conditional formatting is horrible IMO. Much easier to do it in the earlier version. Get the add in that converts the menu options back to the 2003 style. Winning! Is this the UBitMenu menu add-in, or something else? Link for UBitMenu: http://www.ubit.ch/software/ubitmenu-languages/ Yes, this is the one |