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-14-2010, 04:34 AM   #101
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by KanMan View Post
CopThis/zomg/anyone else,

I'm entering into the actuary field and I need to touch up on my excel. I never took any classes and my only experience with excel were at my job, so i'd say im pretty much a beginner.

Are there any good books out there I can pick up to get acclimated?
I'm sure there are but everyone has tended to learn on the job in my team - the thing is, once you know how formula work and how to look them up you can usually find out what you need. Most firms will have their own functions built it (eg mortality tables) too.
CopTHIS is offline   Reply With Quote
Old 09-14-2010, 10:11 AM   #102
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 growingskin View Post
thanks for your reply

But I can't seem to get it to work on 2003, I haven't tried it on my 2nd computer with 2007, I'll have to try it later although I really wanted it to work on 2003 > this my main pc
Format > Conditional Formatiting
Formula Is.. =A1="Paid"

i thought you could do it without specifically referencing the cell but i just checked and it doesnt work

Quote:
Originally Posted by sockhead2 View Post
Anyway, one other thing. I can do anything in Excel, including use pivot tables. Nanner nanner boo boo!
Cool, feel free to answer some questions.

Quote:
Originally Posted by CopTHIS View Post
I'm sure there are but everyone has tended to learn on the job in my team - the thing is, once you know how formula work and how to look them up you can usually find out what you need. Most firms will have their own functions built it (eg mortality tables) too.
+1 to never reading an excel book you will probably just be wasting your time
zomg is offline   Reply With Quote
Old 09-14-2010, 10:30 AM   #103
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

From PM:
Quote:
I was wondering if you could tell me a little bit about how you made that AFL draft picker, not so much the programming side of things but how you collected the data and how much weighting you placed on various aspects of a players performance.
The fantasy site was www.ultimatefooty.com, they have stats for the last few years on goals, kicks, handballs, marks, tackles

1. Import results into excel
Code:
For i = 0 To 19
    Application.StatusBar = i
    tempURL = "http://ultimatefooty.com/" & leagueID & "/players?status=ALL&pos=P&club=ALL&stats=2009_AS_A&sort=rank&count=" & i * 25
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & tempURL, Destination _
        :=Range("$A$" & Range("A65000").End(xlUp).Row + 1))
        .Name = "players?count=" & 25 * i & "&sort=drank"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next i
2. ran a macro to randomise a draft with 12 players then worked out what the average weekly score would need to be to win in each category (i checked at the end of the season and it was almost exact)

3. as the draft was going, kept track of the two stats that were the biggest % away from average and looked at all available players weighted by % behind e.g.
cat1pct = tmpcat1diff / (tmpcat1diff + tmpcat2diff)
cat2pct = tmpcat2diff / (tmpcat1diff + tmpcat2diff)

then id rank the two categories added together (multiplied by each weighting) and the lower score is the best player available in those two stats

"=RANK(" & col1 & "2,$" & col1 & "$2:$" & col1 & "$600)*" & cat1pct & "+RANK(" & col2 & "2,$" & col2 & "$2:$" & col2 & "$600)*" & cat2pct
zomg is offline   Reply With Quote
Old 09-14-2010, 05:33 PM   #104
defixated
enthusiast
 
Join Date: May 2007
Posts: 76
Re: Ask me anything about Microsoft Excel

Excel is Microsoft's killer app and is substantially responsible for the success of Windows. Joel Spolsky, who was one of the early project managers (now sort of a software guru), says that by far the most common use for Excel is manipulating lists. So while all the programmers want to replace Excel with an RDBMS (even one as miserable as Access), this is mostly missing the point.

Btw, if you are just learning to code, you will be amazed at how much time and hair-pulling date arithmetic requires. For a while I tried to get everyone to store dates as integers formatted YYYYMMDD.

Also, cool story bro re: obscure date functions, young Spolsky and Bill Gates: http://www.inc.com/magazine/20080701...lory-days.html
defixated is offline   Reply With Quote
Old 09-14-2010, 09:23 PM   #105
DeezNuts
veteran
 
DeezNuts's Avatar
 
Join Date: Sep 2002
Posts: 2,991
Re: Ask me anything about Microsoft Excel

why do array tables kill my cpu?
DeezNuts is offline   Reply With Quote
Old 09-15-2010, 03:46 PM   #106
guller
adept
 
Join Date: Dec 2004
Posts: 828
Re: Ask me anything about Microsoft Excel

I have been using Excel since around version 5.1 and I know all of the basic stuff. I have never written a macro, nor do I know were to begin to write one. Where do I start?

Lets say I have several sheet inside of a workbook that have 4 columns. I'll use random numbers here. Multiple sheets would have similar data.

Each row is a different data point consisting of a footage, 2 data measurements, and remarks.

Column A would represent a common measurement say footage for instance. The footages between sheets may or may not match exactly every time.

Column B and C would represent different data measurements at that footage.

Column D would be remarks

Sorry the columns don't line up since this site does not like spaces. (, = next column), * = blank cell in column

Sheet 1
A, B, C, D,
1, 25, 35, Random Remark,
2, 19, 26, Random Remark,

Sheet 2
A, B, C, D,
1.5, 15, 55, Random Remark,
2, 49, 86, Random Remark,

I want to make a master sheet inside of the workbook that would organize these numbers together for evaluation. So the master sheet would look like this:

A, B, C, D, E, F, G,
0.5, *, *, *, *, *, *,
1, 25, 35, *, *, Remark Sheet 1, *
1.5, *, *, 15, 55, *, Remark Sheet 2
2, 19, 26, 49, 86, Remark Sheet 1, Remark Sheet 2,

Column A would be a series of numbers in 0.5 foot increments and may or may not have any information transferred from the individual sheets.

Columns B and C would take the measurements from sheet 1 and place them at the correct footage in the master sheet. Column F would be the remark from that footage.

Columns D and E would take the measurements from sheet 2 and place them at the correct footage in the master sheet. Column G would be the remark from that footage.

Does this make sense? How would I begin to write a macro that would accomplish this. I have seen some (If this = that then do this) type of stuff before but I have no idea how it works. I think that is what I have to do here.

Any ideas? Thanks,
guller is offline   Reply With Quote
Old 09-15-2010, 04:19 PM   #107
deesnuts
veteran
 
deesnuts's Avatar
 
Join Date: Apr 2008
Location: Maltese
Posts: 2,958
Re: Ask me anything about Microsoft Excel

So basically you excel at Excel
deesnuts is offline   Reply With Quote
Old 09-15-2010, 10:03 PM   #108
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 defixated View Post
Also, cool story bro re: obscure date functions, young Spolsky and Bill Gates: http://www.inc.com/magazine/20080701...lory-days.html
wow, really interesting thanks for that

Quote:
Originally Posted by DeezNuts View Post
why do array tables kill my cpu?
Do you mean array formulas? they take huge amount of processing power if you do it on huge ranges. The formula i posted earlier:


SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*(d ata!$C$2:$C$1000))

froze my excel when i did

SUM((data!$A:$A=$A2)*(d ata!$B:$B=B$1)*(d ata!$C:$C))

because it multiplies every cell by every cell in that range

Quote:
Originally Posted by guller View Post
I have been using Excel since around version 5.1 and I know all of the basic stuff. I have never written a macro, nor do I know were to begin to write one. Where do I start?
Use the macro recorder then edit it as you need, i posted an example of a recorded sort earlier

Quote:
Originally Posted by guller View Post
Lets say I have several sheet inside of a workbook that have 4 columns. I'll use random numbers here. Multiple sheets would have similar data.

Each row is a different data point consisting of a footage, 2 data measurements, and remarks.

Column A would represent a common measurement say footage for instance. The footages between sheets may or may not match exactly every time.

Column B and C would represent different data measurements at that footage.

Column D would be remarks

Sorry the columns don't line up since this site does not like spaces. (, = next column), * = blank cell in column

Sheet 1
A, B, C, D,
1, 25, 35, Random Remark,
2, 19, 26, Random Remark,

Sheet 2
A, B, C, D,
1.5, 15, 55, Random Remark,
2, 49, 86, Random Remark,

I want to make a master sheet inside of the workbook that would organize these numbers together for evaluation. So the master sheet would look like this:

A, B, C, D, E, F, G,
0.5, *, *, *, *, *, *,
1, 25, 35, *, *, Remark Sheet 1, *
1.5, *, *, 15, 55, *, Remark Sheet 2
2, 19, 26, 49, 86, Remark Sheet 1, Remark Sheet 2,

Column A would be a series of numbers in 0.5 foot increments and may or may not have any information transferred from the individual sheets.

Columns B and C would take the measurements from sheet 1 and place them at the correct footage in the master sheet. Column F would be the remark from that footage.

Columns D and E would take the measurements from sheet 2 and place them at the correct footage in the master sheet. Column G would be the remark from that footage.

Does this make sense? How would I begin to write a macro that would accomplish this. I have seen some (If this = that then do this) type of stuff before but I have no idea how it works. I think that is what I have to do here.

Any ideas? Thanks,
For future reference if you use CODE tags it will preserve your spacing

You wouldn't need to use a macro unless you wanted to automate the populating of column A, but if you are happy entering .5 increments then all you need is a vlookup

Master Sheet

B2: =if(ISERROR(vlookup(A2,sheet1!A: D,2,0)),"*",vlookup(A2,sheet1!A: D,2,0))

C2: =if(ISERROR(vlookup(A2,sheet1!A: D,3,0)),"*",vlookup(A2,sheet1!A: D,3,0))

etc

Is that what you meant?

Quote:
Originally Posted by deesnuts View Post
So basically you excel at Excel
Basically, I also just realised that you are not "deeznuts"
zomg is offline   Reply With Quote
Old 09-15-2010, 10:25 PM   #109
Wayne's Pain
enthusiast
 
Join Date: Aug 2006
Posts: 64
Re: Ask me anything about Microsoft Excel

One very simple question that will greatly improve the lives of many restaurant managers.

1. Is there a way to just plug in numbers to a cell without using the "=" (instead of =343.34+343.34+909.99 I type 343.34+343.34+909.99 into the cell).

We just converted to Excel from Lotus and moving your left hand all the way over to the equal button is just too much to bear. (for my colleagues)

I would be a hero if I could solve this.
Wayne's Pain is offline   Reply With Quote
Old 09-15-2010, 11:39 PM   #110
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 Wayne's Pain View Post
One very simple question that will greatly improve the lives of many restaurant managers.

1. Is there a way to just plug in numbers to a cell without using the "=" (instead of =343.34+343.34+909.99 I type 343.34+343.34+909.99 into the cell).

We just converted to Excel from Lotus and moving your left hand all the way over to the equal button is just too much to bear. (for my colleagues)

I would be a hero if I could solve this.
Woops, i forgot if you add a + sign first it will act as an equals so

+343.34+343.34+909.99 is the same as =343.34+343.34+909.99
zomg is offline   Reply With Quote
Old 09-16-2010, 01:01 PM   #111
guller
adept
 
Join Date: Dec 2004
Posts: 828
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
You wouldn't need to use a macro unless you wanted to automate the populating of column A, but if you are happy entering .5 increments then all you need is a vlookup

Master Sheet

B2: =if(ISERROR(vlookup(A2,sheet1!A: D,2,0)),"*",vlookup(A2,sheet1!A: D,2,0))

C2: =if(ISERROR(vlookup(A2,sheet1!A: D,3,0)),"*",vlookup(A2,sheet1!A: D,3,0))

etc

Is that what you meant?
This is very close. I have never heard of vlookup before.

I have been messing around with the insert vlookup function and I keep getting an error. I tried pasting your formula in directly but that also didn't work.

I think the problem is my master sheet contains some footage numbers in column A that don't match the table array that I have selected and it causes an error.

Is there a way to make it ignore the rows that do not match any of the footage numbers in the master sheet column A and have it leave those rows blank or put in an *?
guller is offline   Reply With Quote
Old 09-16-2010, 01:15 PM   #112
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 guller View Post
This is very close. I have never heard of vlookup before.

I have been messing around with the insert vlookup function and I keep getting an error. I tried pasting your formula in directly but that also didn't work.

I think the problem is my master sheet contains some footage numbers in column A that don't match the table array that I have selected and it causes an error.

Is there a way to make it ignore the rows that do not match any of the footage numbers in the master sheet column A and have it leave those rows blank or put in an *?
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
zomg is offline   Reply With Quote
Old 09-16-2010, 02:59 PM   #113
kylephilly
Carpal \'Tunnel
 
kylephilly's Avatar
 
Join Date: Jan 2007
Location: It's Always Sunny...
Posts: 9,373
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Jimbo232 View Post
What new features in Office 2007 do you feel are the most useful? Are most of your clients still on the older version of office or have they mostly upgraded by now?
DATA -> Remove Duplicates is a nice feature
kylephilly is offline   Reply With Quote
Old 09-16-2010, 03:15 PM   #114
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
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

Or in 2007 (and 2010):
=iferror(vlookup(A2,sheet1!A: D,2,0),"*")

(remove the space after : )
Hellrazor is offline   Reply With Quote
Old 09-16-2010, 03:35 PM   #115
Jeremy517
Carpal \'Tunnel
 
Join Date: Jul 2004
Posts: 6,079
Re: Ask me anything about Microsoft Excel

Is there any way to have Excel increment the row instead of the column when filling a row, and vice versa?

For example, if I have =A1 in a cell and fill horizontally, have it fill in =A2, =A3, ..., =An instead of =B1, =C1, ..., =Cn.

Thanks
Jeremy517 is offline   Reply With Quote
Old 09-16-2010, 05:06 PM   #116
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 Hellrazor View Post
Or in 2007 (and 2010):
=iferror(vlookup(A2,sheet1!A: D,2,0),"*")

(remove the space after : )
Amazing!

Quote:
Originally Posted by Jeremy517 View Post
Is there any way to have Excel increment the row instead of the column when filling a row, and vice versa?

For example, if I have =A1 in a cell and fill horizontally, have it fill in =A2, =A3, ..., =An instead of =B1, =C1, ..., =Cn.

Thanks
There is no easy way, you would have to use the INDIRECT function

If you put:
=INDIRECT(ADDRESS(COLUMN( )-1,1))

in Cell B1 that is the equivalent of "=A1" (column refers to the column the formula is in, then minus 1 makes it A1)

If you copy that across it will then be =A2, =A3 because it is using the current column making it: address(2,1) address (3,1) etc

hope that makes sense
zomg is offline   Reply With Quote
Old 09-16-2010, 07:12 PM   #117
Jeremy517
Carpal \'Tunnel
 
Join Date: Jul 2004
Posts: 6,079
Re: Ask me anything about Microsoft Excel

That's what was afraid of. Oh well, thanks.
Jeremy517 is offline   Reply With Quote
Old 09-17-2010, 08:56 AM   #118
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Does anyone know if there is an easy way to select a range by reference to another column? Eg if I want to copy a formula from cell B1 down to B100 because column A has data down to row 100. In Lotus 1-2-3 I think you just click on cell B1, then press shift+left, shift+down, shift+right and then you can just copy down as you have highlighted range B1:B100. I know you can do something in Excel with the mouse but it's fiddly and the 1-2-3 way is so much quicker.
CopTHIS is offline   Reply With Quote
Old 09-17-2010, 09:59 AM   #119
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 CopTHIS View Post
Does anyone know if there is an easy way to select a range by reference to another column? Eg if I want to copy a formula from cell B1 down to B100 because column A has data down to row 100. In Lotus 1-2-3 I think you just click on cell B1, then press shift+left, shift+down, shift+right and then you can just copy down as you have highlighted range B1:B100. I know you can do something in Excel with the mouse but it's fiddly and the 1-2-3 way is so much quicker.
If you are just talking about shortcuts:
1) Copy cell B1
2) left, ctrl+down
3) right, ctrl+shift+up
4) paste
zomg is offline   Reply With Quote
Old 09-17-2010, 10:01 AM   #120
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 Jeremy517 View Post
That's what was afraid of. Oh well, thanks.
If you don't mind doing two steps you could enter the formula "=A$1" then copy down (making it a2, a3 etc) then copy the range and right click > paste special then tick the transpose box and it should work
zomg is offline   Reply With Quote
Old 09-17-2010, 10:15 AM   #121
kevin21
SE Spokesman
 
kevin21's Avatar
 
Join Date: Oct 2007
Location: Ireland
Posts: 62,018
Re: Ask me anything about Microsoft Excel

Are you really good with VB as well?
kevin21 is offline   Reply With Quote
Old 09-17-2010, 10:39 AM   #122
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 kevin21 View Post
Are you really good with VB as well?
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.
zomg is offline   Reply With Quote
Old 09-17-2010, 12:25 PM   #123
1724g
centurion
 
Join Date: Aug 2010
Posts: 170
Re: Ask me anything about Microsoft Excel

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!

Last edited by 1724g; 09-17-2010 at 12:38 PM. Reason: Typo" date for data
1724g is offline   Reply With Quote
Old 09-17-2010, 02:57 PM   #124
vbm
adept
 
vbm's Avatar
 
Join Date: Sep 2005
Location: The Holte End
Posts: 1,164
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Stupid things American girls have said to me would probably be worthy of it's own thread,

- Are you from [country that is no where near australia]? e.g. England, South Africa, New Zealand, Ireland

I'll add more when i think of them/they come up
You might be able to forgive them that one eh ?
vbm is offline   Reply With Quote
Old 09-18-2010, 10:53 AM   #125
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 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!
You could do something in the sheet change macro. Right click on the sheet tab and go "view code" then paste
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Target.Offset(, 1) = Now 
    End If
End Sub
Whenever you enter data in range A1:A10 it will put a date in column B

or you could automatically change it like this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Formula = "=NOW()" Then Target.Value = Now
End Sub
Quote:
Originally Posted by vbm View Post
You might be able to forgive them that one eh ?
yes yes
zomg 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:04 AM.


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