Two Plus Two Publishing LLC
Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Notices

Other Other Topics Discussion of arts & entertainment, pop culture, food & drink, health and exercise, fashion, relationships, work, and just about anything else in life except poker, sports, religion and politics.

Reply
 
Thread Tools Display Modes
Old 09-18-2010, 02:28 PM   #126
Tito Jackson
journeyman
 
Join Date: Oct 2006
Location: *
Posts: 361
Re: Ask me anything about Microsoft Excel

I'm possibly going to be tested in Excel for a job interview in the near future. The job description lists intermediate to advanced Excel skills needed. Do you have any idea on what they'll test me on most likely? Here is the job description:

"- Assist Senior level Telecommunications Analyst

- Support Full Life Cycle Management of Telecom Audit and Analysis projects

- Assist in Developing and maintaining inventory of clients' telecommunications services

- Analysis and processing of Client's telecom invoices focusing on identifying and eliminating invoicing errors

- Analysis and management of client telecom contracts

- Providing Customized Reports for Clients (Associating services by Cost Centers, G/​L codes, etc)

- Provisioning and Management of Clients’ Telecommunications services (Moves, Adds, Changes, Disconnects)

- Support for Daily interaction with clients and carriers in regards to invoicing issues, dispute resolution, recommendations, etc.​

- Filing claims with carriers on behalf of clients

- Obtaining and negotiating pricing on behalf of clients

- Developing recommendations for the optimization of existing services

- Perform all other duties as assigned"
Tito Jackson is offline   Reply With Quote
Old 09-18-2010, 04:36 PM   #127
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Hmm, tbh I'm not sure but with that in mind:

The only interviews i've had where they 'tested' me in excel, I had to send code examples up front. Specific questions I can think of that I have been asked in an interview "why would you use array formulas?" and "whats the point of option explicit?" both have been covered ITT. I'm not sure they how they would test your ability to do any of the things listed unless you are on a computer and even then it's the kind of thing that can vary widely.

From the description it looks like they are more interested in you than your excel ability, the specific things you will need to do in excel would be secondary to that. IME most job descriptions look a lot more daunting than they turn out to be.

Either way it looks like an interesting job that will have you covering a lot of things, good luck in the interview let us know how it goes. Sorry I couldn't be more helpful.
zomg is offline   Reply With Quote
Old 09-18-2010, 04:59 PM   #128
jeromy
enthusiast
 
Join Date: Feb 2010
Posts: 63
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by z28dreams View Post
Here's one for you:

Do you have any best practices for handling the formatting of dates?

I've spent countless hours trying to convert various cells into a date format that I wanted, but with little luck.

For example, going between Jan 4th, 2010, 1/4/2010, and a more generic 1-2010 / Jan-2010 always results in nightmares for me.

Sometimes it seems to work perfectly, other times Excel refuses to cooperate. I'm aware of the Cell Formatting options (and even tried using the custom formatting for like M/YYYY), but it doesn't always work.

Any tips?
I use YYYY-MM-DD or YYYY-MM-DD HH:MM (depending on if I want to display hours). Then I wrote a very easy little macro to update all highlighted dates. I keep often used macros in another workbook that I open when the app starts.

Sub DateFormat()
Selection.NumberFormat = "yyyy-mm-dd hh:mm"
End Sub

Sub DateFormatNoTime()
Selection.NumberFormat = "yyyy-mm-dd"
End Sub

I use Excel hours a day and never really use pivot tables. I'm all about array functions. I've never found anything I can't really do.
jeromy is offline   Reply With Quote
Old 09-18-2010, 05:00 PM   #129
jeromy
enthusiast
 
Join Date: Feb 2010
Posts: 63
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by 1724g View Post
Is there a way I can set up a cell to automatically enter the timestamp of a certain entry? I know there is the NOW() function but that changes everytime Excel is calc'd. I also know I can copy the time entry and paste special values. Just too manually bothersome to do. I have a workbook where I enter my time spent on certain projects. For now, just date and # hours spent is fine. But I'd like to be able to know what time of day I spent those hours. Thanks!
Couldn't you just enter Now() in the cell and then copy/paste special > values to retain the time?
jeromy is offline   Reply With Quote
Old 09-18-2010, 08:28 PM   #130
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,164
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
I've actually never written a standalone VB app, but from what I understand they are essentially the same thing except VBA has to be launched from within an application.

I think originally VBA was a stripped down version of VB but now VB uses VBA as its code base and other than a couple of commands you should be able to copy a VB program into VBA and run unchanged.
This isn't true now that VB generally refers to VB.net. VB6 was horrible.
jjshabado is offline   Reply With Quote
Old 09-18-2010, 11:10 PM   #131
1724g
centurion
 
Join Date: Aug 2010
Posts: 170
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
You could do something in the sheet change macro. Right click on the sheet tab and go "view code" then paste...
Thanks for the help, zomg! This was what I was looking for. I slightly modified it, but your tip got me to where I needed to be.
1724g is offline   Reply With Quote
Old 09-19-2010, 06:57 PM   #132
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by jjshabado View Post
This isn't true now that VB generally refers to VB.net. VB6 was horrible.
Oh i wasn't aware of that, but from some googling you are definitely correct. I always classified them as three 'different' subsets of VB (VBA, VB and .NET).

It seems like there is a belief that microsoft is trying to phase out VBA in favour of vb.net, are you a .net programmer? any comments on advantages? switching process? I think i remember reading that it is OO, I have some history with java so i imagine the switch, at least on a conceptual level wouldn't be too difficult.

Even if they do phase out VBA in office 2012+ (conservative guess, according to a quick search it looks like they have removed it from autoCAD 2010) at the current rate of adoption I can't really seeing it being a huge issue for the next 5 years.

How does it run? Are they standalone applications? Can i put a button in excel that will run a .NET script? I assume you need an IDE (visual studio?) I'll probably download it in the next couple of weeks when I have some time.
zomg is offline   Reply With Quote
Old 09-24-2010, 07:58 AM   #133
guller
adept
 
Join Date: Dec 2004
Posts: 828
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Sorry i didn't actually test it, i had to put a space in between the colon and the D because it changed it to an emoticon

=if(ISERROR(vlookup(A2,sheet1!A,2,0)),"*",vlooku p(A2,sheet1!A,2,0))

the part If(ISERROR(vlookup(A2,sheet1!A: D,2,0)) checks if the footage is not found (it will return an error) and returns "*" if its not an error (i.e. it is found) then it return the result of the vlookup

try removing the space between the colon and D and make sure the sheet names are correct and see if it still doesnt work

You can just try =vlookup(A2,sheet1!A: D,2,0) but will return an N/A if the value isn't found
This worked. Thank you so much. I have never tried any of the insert functions before. Any way you can give a quick rundown on what the most useful ones do?

Last edited by guller; 09-24-2010 at 08:07 AM.
guller is offline   Reply With Quote
Old 09-24-2010, 08:18 AM   #134
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

I like to think i'm pretty good with Excel so I'll try to follow this.

Last edited by Malfunction; 09-24-2010 at 08:24 AM.
Malfunction is offline   Reply With Quote
Old 09-24-2010, 12:42 PM   #135
stakman1011
Carpal \'Tunnel
 
stakman1011's Avatar
 
Join Date: Dec 2006
Posts: 10,464
Re: Ask me anything about Microsoft Excel

Why did you lie to your boss about your wife being in an emergency? Why not just explain to him that you had an interview for a better company?
stakman1011 is offline   Reply With Quote
Old 09-24-2010, 12:49 PM   #136
sinndar11
journeyman
 
Join Date: May 2008
Posts: 228
Re: Ask me anything about Microsoft Excel

bahaha ^^^^
sinndar11 is offline   Reply With Quote
Old 09-24-2010, 02:00 PM   #137
J-Lo
old hand
 
Join Date: Mar 2004
Location: Eating Food
Posts: 1,671
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by stakman1011 View Post
Why did you lie to your boss about your wife being in an emergency? Why not just explain to him that you had an interview for a better company?

<3
J-Lo is offline   Reply With Quote
Old 09-24-2010, 03:06 PM   #138
natediggity
Carpal \'Tunnel
 
natediggity's Avatar
 
Join Date: Feb 2008
Posts: 13,730
Re: Ask me anything about Microsoft Excel

is there a way to make a graph with the x and y axes in a log scale?
natediggity is offline   Reply With Quote
Old 09-24-2010, 03:10 PM   #139
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by natediggity View Post
is there a way to make a graph with the x and y axes in a log scale?
I have no 2007 at hand, but in Office XP:
Add a graph, right click on Axis, Format Axis, go to 2nd tab (Scale), click Logaritmic scale at the bottom.

This only works for the Y-axis on 'normal' graphs.
If you need it on the X-axis too, switch Graph type to X-Y Scatter.
Malfunction is offline   Reply With Quote
Old 09-24-2010, 03:37 PM   #140
UbinTook
veteran
 
UbinTook's Avatar
 
Join Date: Jan 2007
Posts: 2,729
Re: Ask me anything about Microsoft Excel

this may be simple:
We have an excel document that tracks when people clock in it contain 10's of thousands of entries over time.
What we are trying to do is sort the sheet by the number card swipes so that the names with the fewest entries are at the top and order the rest of the sheet ascending order with the people with the most entries are at the bottom while still keeping the clock in of each individual grouped together.
UbinTook is offline   Reply With Quote
Old 09-24-2010, 04:31 PM   #141
natediggity
Carpal \'Tunnel
 
natediggity's Avatar
 
Join Date: Feb 2008
Posts: 13,730
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Malfunction View Post
I have no 2007 at hand, but in Office XP:
Add a graph, right click on Axis, Format Axis, go to 2nd tab (Scale), click Logaritmic scale at the bottom.

This only works for the Y-axis on 'normal' graphs.
If you need it on the X-axis too, switch Graph type to X-Y Scatter.
Thanks. That was easy enough.
natediggity is offline   Reply With Quote
Old 09-24-2010, 05:14 PM   #142
john voight
Carpal \'Tunnel
 
john voight's Avatar
 
Join Date: Apr 2006
Posts: 13,540
Re: Ask me anything about Microsoft Excel

say you have a degree in econ or finance, would passing the certification exam for excel be useful?
john voight is offline   Reply With Quote
Old 09-24-2010, 05:16 PM   #143
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

they have a cert for excel?
PolvoPelusa is offline   Reply With Quote
Old 09-24-2010, 07:34 PM   #144
lonely_but_rich
ɔarpal \ʇunnel
 
lonely_but_rich's Avatar
 
Join Date: Mar 2009
Location: Wearing a suit
Posts: 20,844
Re: Ask me anything about Microsoft Excel

Given the, uh, local-to-world coordinate transformation matrices for two distinct objects in 3d space (tacos), how can you tell if the objects are facing each other?
This is probably pretty basic stuff but yeah... I just got done doing 300 pushups or whatever.
lonely_but_rich is offline   Reply With Quote
Old 09-25-2010, 01:17 AM   #145
Buzz-cp
Carpal \'Tunnel
 
Buzz-cp's Avatar
 
Join Date: Sep 2005
Location: :noitacoL
Posts: 10,902
Re: Ask me anything about Microsoft Excel

I use Excel for grading. I'd like to prepare progress reports that I could print out.

I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:

Code:
            Exam                Lab
Name          1   2   3   4      1   2   3   4  
 Possible -> 100 100 100 100     25  25  25  25
Bob Barker    95  89  75  79     24  15  23  0
Jens Kruger   88  59  65  47     19  17  20  21
So we make a report for Bob Barker on a new worksheet, and it looks like this:

Code:
Name: Bob Barker
Date: 1/1/2010
           Possible  Score
Exam 1        100      95
Exam 2        100      89
Exam 3        100      75
Exam 4        100      79
Lab 1          25      24
Lab 2          25      15
..etc
Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.
Buzz-cp is offline   Reply With Quote
Old 09-25-2010, 04:37 AM   #146
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by UbinTook View Post
this may be simple:
We have an excel document that tracks when people clock in it contain 10's of thousands of entries over time.
What we are trying to do is sort the sheet by the number card swipes so that the names with the fewest entries are at the top and order the rest of the sheet ascending order with the people with the most entries are at the bottom while still keeping the clock in of each individual grouped together.
If you have headers in A1, B1 etc,
select every column, go to Data -> Sort, if he doesn't see you're using headers, tick the box, select which column/header to sort by...done!
Malfunction is offline   Reply With Quote
Old 09-25-2010, 04:44 AM   #147
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Buzz-cp View Post
I use Excel for grading. I'd like to prepare progress reports that I could print out.

I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:
<removed code>
Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.
Ive experimented a bit but it seems easier to order you first sheet differently, so you can use VLOOKUP on your second sheet.
Its gonna be very hard now with HLOOKUP, INDEX, MATCH, some string concatenation and transposing.

Like this:
Code:
	PossibleBob	Joe
			
Exam 1	100	100	60
Exam 2	100	90	91
Lab 1	25	20	15
Lab 2	25	6	18
Malfunction is offline   Reply With Quote
Old 09-25-2010, 07:01 AM   #148
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
If you are just talking about shortcuts:
1) Copy cell B1
2) left, ctrl+down
3) right, ctrl+shift+up
4) paste
Just saw this, thanks. It's not as easy as it was in Lotus 1-2-3 but it's still quite handy if they are long columns.

Re pivot tables - can someone tell me how easy it is to see what they are actually doing? I've never really set them up but when sheets have them I've not found it easy to follow exactly what they are doing. It's the same with data tables - very powerful but moreso when you set them up rather then adjust etc.
CopTHIS is offline   Reply With Quote
Old 09-25-2010, 07:02 AM   #149
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
If you are just talking about shortcuts:
1) Copy cell B1
2) left, ctrl+down
3) right, ctrl+shift+up
4) paste
Just saw this, thanks. It's not as easy as it was in Lotus 1-2-3 but it's still quite handy if they are long columns.

Re pivot tables - can someone tell me how easy it is to see what they are actually doing? I've never really set them up but when sheets have them I've not found it easy to follow exactly what they are doing. It's the same with data tables - very powerful but moreso when you set them up rather then adjust etc.
CopTHIS is offline   Reply With Quote
Old 09-25-2010, 09:08 AM   #150
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Malfunction View Post
Quote:
Originally Posted by UbinTook View Post
this may be simple:
We have an excel document that tracks when people clock in it contain 10's of thousands of entries over time.
What we are trying to do is sort the sheet by the number card swipes so that the names with the fewest entries are at the top and order the rest of the sheet ascending order with the people with the most entries are at the bottom while still keeping the clock in of each individual grouped together.
If you have headers in A1, B1 etc,
select every column, go to Data -> Sort, if he doesn't see you're using headers, tick the box, select which column/header to sort by...done!
this won't work. you need to aggregate the data first...perfect opportunity for a pivot table.


Select the data range and create a pivot table. Lets assume its simply 2 columns: employee and swipe-timestamp. Add [employee] to the row labels group and the [swipe-timestamp] to the values group. Now just sort the employee row label by [swipe-timestamp] ascending.
PolvoPelusa is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 06:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ę 2008-2017, Two Plus Two Interactive
 
 
Poker Players - Streaming Live Online