• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

Always thought Excel tests in interviews were a lazy ploy of a weak interviewer. Its not like you're engrained in the company data, so unless its something fairly common (baseball stats, for example), you're automatically putting your candidate at a disadvantage. There are more effective ways to determine a candidate's analytical abilities if you just put some effort into it.

That said, I'm sure they'll throw a pivot table exercise at you. If they don't, they're stupid in addition to being lazy.

have you ever used Excel for what it can do? Knowing if someone can use Excel for some of the very advanced reasons it is used is a huge differentiator of candidates especially in lower-level data intensive jobs.
 
This is new to me. So ability to do analytical work is based on how you work Excel, but someone that rules with SQL (or whatever database query language is your pref) that only uses Excel for presentation data sucks? Oooookay. I see more value is measuring how many words per minute someone can type than Excel expertise.
 
This is new to me. So ability to do analytical work is based on how you work Excel, but someone that rules with SQL (or whatever database query language is your pref) that only uses Excel for presentation data sucks? Oooookay. I see more value is measuring how many words per minute someone can type than Excel expertise.

no. if you apply to a job that requires a lot of SQL work than you'd probably take an SQL evaluation.
 
Is there a simple way to convert all relative cell references to absolute cell references in a worksheet?
I've used this macro I found online in the past.

Dim RdoRange As Range
Dim i As Integer
Dim Reply As String

'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Relative row/Absolute column = 1" & Chr(13) _
& "Absolute row/Relative column = 2" & Chr(13) _
& "Absolute all = 3" & Chr(13) _
& "Relative all = 4", "OzGrid Business Applications")

'They cancelled
If Reply = "" Then Exit Sub

On Error Resume Next
'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

'determine the change type
Select Case Reply
Case 1 'Relative row/Absolute column

For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Next i

Case 2 'Absolute row/Relative column

For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
Next i

Case 3 'Absolute all

For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i

Case 4 'Relative all

For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next i


Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select

'Clear memory
Set RdoRange = Nothing
End Sub
 
This is new to me. So ability to do analytical work is based on how you work Excel, but someone that rules with SQL (or whatever database query language is your pref) that only uses Excel for presentation data sucks? Oooookay. I see more value is measuring how many words per minute someone can type than Excel expertise.

In an analyst role, Excel skills are pretty key to go along with SQL skills in my opinion. I predominately use SQL now, but Excel is the primary format for communicating with various managers and executives. Knowing how to quickly manipulate data in Excel is huge. There are advantages to both Excel and SQL, so knowing the intricacies of both will make you much more efficient and successful at your job.
 
I have a large .txt file that I need imported into Excel.

The data looks like this:

Joyce Akwe, Preoperative Clinics, Hospital Medicine Clinics, Volume 1, Issue 4, October 2012, Pages e548-e557, ISSN 2211-5943, 10.1016/j.ehmc.2012.05.001.
(http://www.sciencedirect.com/science/article/pii/S2211594312000755)
Keywords: Preoperative clinic; Evaluation; Standardization; Efficiency

Donald W. Richardson, Mary Elizabeth Mason, Aaron I. Vinik, Update: Metabolic and Cardiovascular Consequences of Bariatric Surgery, Endocrinology and Metabolism Clinics of North America, Volume 40, Issue 1, March 2011, Pages 81-96, ISSN 0889-8529, 10.1016/j.ecl.2010.12.009.
(http://www.sciencedirect.com/science/article/pii/S0889852910001039)
Keywords: Obesity; Weight loss; Bariatric surgery; Diabetes mellitus

So I need to import it like in columns author, title, journal, volume, issue, date, pages, issn, doi, url, keywords, like you see here. However, sometimes there is only one author, and sometimes there are multiple authors. Otherwise, everything else is comma separated (or I can make it comma separated) and will neatly import into its own column, but the authors, since they're separated by commas, are each getting their own columns. Any way to go about working on this either pre-or post import to excel instead of doing each of the 2000+ rows by hand?
 
Change the , to ; but leave the , between authors ?
 
What do you mean change? If I do a find and replace with all the commas for semicolons, I'll be able to delimit by semicolon but I'm in the same place as before. How could I change the rest without changing the authors by hand?
 
I have a large .txt file that I need imported into Excel.
This isn't a perfect solution. But you could import as comma separated as you said. Every record with only one author should then be in a certain number of fields (lets say 10 columns). In a new column to the far right, you can use the COUNTA formula to count the number of imported fields in each row. The ones that have a result of "10" imported correctly and don't need to be maniuplated.

You can then use an autofilter to isolate the "11's" and concatenate the two authors into one field, filter on the "12's" and join the three author using concatenate, and so forth.
 
didn't get the position.

Here is the data set and the questions.

Instructions:

Analyze the attached data set. I'm interested in seeing your thought process. There is more than one right answer - I'm curious how you approach the data. To give some context, the attached data set is an export for display advertising campaigns.

Please send back answers to the following in a Word or PDF document, along with your modified Excel Workbook (renamed in the format: LastName_FirstName_MarketingAnalysis), with clearly broken out answers to each question.

Questions:

1) How would you set up the analysis in excel and what factors would you analyze?
2) What are two questions you have about the data set that would inform your analysis?
3) Include a screen shot of the three cuts / views of data that you think are the most telling.
4) Name two strategy recommendations from each of the three cuts from #3.
5) The CEO just asked you to create a slide deck for the Board of Directors describing
• The performance of display ads in March
• Strategy recommendations for April

Please include at least 3 images and 2 to 3 sentences to accompany each image
 
rather than start a new excel help thread, i thought i'd just add on to this one.

i work at the corporate headquarters for a retail organization. we recently built a report that we want some of our regional controllers to use to evaluate some of their stores. in order to take some of the pressure off of my department, i built the macro so that they could run it themselves without having any macro knowledge (it just prompts them to choose a folder where the reports can be dumped). however, the way it is now, there's just a long list of stores for each of them, and the macro will run through ALL of them whenever they hit the button to run the macro. we want to give them the ability to run any number of stores at once. not default to all of them.

biggest problem is that none of our regional people are very good with excel. so i can't rely on them to know how to change the named range that this macro runs through. what we want to do is have the macro prompt them to select the rows that they want to run, and then have the macro run for only those stores.

this is what i have. it's not working. it always stops on the line that starts with "For i =1":

Sub RunMeToCreateStoreFiles()
'
'
Dim eCode, eName As String
Dim RunRange As range



Set RunRange = application.InputBox(prompt:="Select Range", Type:=8)
Call choosefolder


With RunRange

For i = 1 To range(RunRange).Rows.Count
Sheets("entity").Select
eCode = range("runrange").Cells(i, 1)
eName = range("runrange").Cells(i, 2)
eRegion = range("runrange").Cells(i, 4)
range("entity") = eCode



and then it runs through the rest of the macro (this macro has been used for a few months with no problems, so i know the rest of it isn't the issue. it's just that i can't get it to recognize that the range i'm trying to run through has been selected by the user). any thoughts?
 
Excel Question

Ok excel studs, I feel like this should be an easy one.

In column A I have 750 rows with six unique values in varying amounts. How can I create a pie chart showing how many of each amount show up? Can I really not do this with one click? (aka do I have to Data --> Subtotal and then create a graph based on the counts that I retype into a new workbook?).

It gets a lot trickier when my next set of data is 750 rows with 55 unique values in varying amounts. I don't want to do a count then type each one into a row so I can pie chart. Please help.
 
I wish I could Neo Matrix all of this shit into my brain. Excel can be so frustrating.......
 
Not sure I follow exactly but it sounds like to could convert to pivot table and do a count of value.
 
Ok excel studs, I feel like this should be an easy one.

In column A I have 750 rows with six unique values in varying amounts. How can I create a pie chart showing how many of each amount show up? Can I really not do this with one click? (aka do I have to Data --> Subtotal and then create a graph based on the counts that I retype into a new workbook?).

It gets a lot trickier when my next set of data is 750 rows with 55 unique values in varying amounts. I don't want to do a count then type each one into a row so I can pie chart. Please help.

Run a =countif( on the six unique values and create the chart off of those values. If you want to sum something corresponding with the unique unidentifier go with a sumif or ifs if you need multiple criteria.

Sent from my SCH-I545 using Tapatalk 2
 
pivot.jpg
 
Back
Top