Nate Oliver's blog - All Comments

re: Pivot Tables/Excel 2007/.xls file format - Save before you start

Ross A — Thu, 19 Nov 2009 16:55:34 GMT

I learned through trial and error that if you've already created a pivot table using 12, once you save the file as Excel 2003, you must delete the original pivot table before you create the new one. It doesn't appear that Excel can run one pivot table on 12 and one on 10 at the same time.

re: Excel on a Mac - My Experience

Tony Stein — Sun, 25 Oct 2009 10:45:20 GMT

Hi Guys,

Just converted to Mac - swapped out my Nokia for an iPhone, my PC for an iMac and my laptop for a Mac Book Air. Huge investment but was tired of the way my PC wanted to take over with MS poking it's "recommendations", upgrades, patches, sales of other products at me all the time.

I'm what could be described as an Excel power user - it is integral to what I do so I bought Excel for Mac.

So far I'm pretty disappointed. EVERY TIME and I emphasize that this is without exception that I right click (it's ingrained into how I work on Excel) the programme crashes and I have to Force Quit. This then prompts a "Report to Apple" then nothing. Come on guys. Even Microsoft can be more helpful than this, suggesting reasons for the crash and possible solutions. Time to sort yourselves out before us PC converts start to drift back......

re: Introduction to Excel - Top-5 Functions

LilA — Wed, 21 Oct 2009 22:49:07 GMT

I'm gonna go with Nate here and agree with SUMIF and COUNTIF versus SUMPRODUCT. Those are both very simple functions and yet get plenty of usage.

Personally, I use VLOOKUP all the time and it's widely used where I work.

Explaining VLOOKUP to someone is far far easier than INDEX/MATCH.

Generally when users need more advanced functions, they pass the request on to a superuser. When you have users that use a CALCULATOR to do the math and then manually enter the results into Excel so the numbers line up all nice and pretty, about the extent of "stretching" you're going to achieve is SUM or other basics like that.

re: Reversing Excel Cell Contents

Nate Oliver — Thu, 10 Sep 2009 15:51:59 GMT

Understood, it's a contrived example, although based on a real question.

While contrived, there are scenarios where the smaller the number, the better. E.g., scratches on break, interceptions thrown in a season, payback period on investment, etc...

re: Reversing Excel Cell Contents

Alan Wolfson — Thu, 10 Sep 2009 15:14:36 GMT

The result is not the same mathematically as the original. 5 is not greater than 195, etc.

re: Reversing Excel Cell Contents

Nate Oliver — Thu, 10 Sep 2009 00:24:23 GMT

I hope so, I'd like to see it.

But I also would prefer for it to be very efficient, I'm not a believer that native is better when it requires a lot more overhead.

re: Reversing Excel Cell Contents

Robert — Wed, 09 Sep 2009 23:17:33 GMT

Bob Umlas can probably come up with a non VBA soluition :-)

re: Fantasy Football Draft Board Tracking

poker rakeback — Mon, 07 Sep 2009 15:01:03 GMT

Once my fantasy draft got out of the 5th or 6th round last night it became difficult for people to keep track of who was picked. I think coming up with more tools to eliminate some of the hassle with the draft and scoring will be a great asset.

re: Fantasy Football Draft Board Tracking

Nate Oliver — Fri, 04 Sep 2009 17:06:30 GMT

It's funny you should mention WiFi.

I was thinking if I really ramp this thing up, and show people how to set up a secure, virtual WAN, with oh, say Groove:

technet.microsoft.com/.../cc261835.aspx

Where multiple people could securely modify the Workbook at the same time, from anywhere...

That might be useful in some very non-trivial circumstances.

re: Fantasy Football Draft Board Tracking

Mike M. — Fri, 04 Sep 2009 16:21:59 GMT

Nope...no laptops. In this day and age, you'd think someone (read: the Commissioner) could find somewhere with at least Wi-Fi.

re: Fantasy Football Draft Board Tracking

Nate Oliver — Thu, 03 Sep 2009 17:26:05 GMT

You're welcome.

I'll probably put a new one, next year, earlier - updated players, projections, etc... That shouldn't take too long, actually.

Did anyone in your draft have a laptop? Could psych out the competition!

re: Fantasy Football Draft Board Tracking

Mike M. — Thu, 03 Sep 2009 14:24:42 GMT

Funny...I pulled in data from the same website for my own draft, but since I wasn't planning on taking a laptop, I didn't create the fancy spreadsheet. If I would have seen this before 6:00 last night, I would have taken my laptop. I hate trying to find someone's name to cross off on paper.

Here's to saving the file and hoping I remember that I have it this time next year.

Thanks, Nate.

re: Fantasy Football Draft Board Tracking

Nate Oliver — Tue, 01 Sep 2009 19:11:35 GMT

Good point, Jeff - Sharepoint guru. I'll force the case, too, if I suspect there could be an issue. In this case, the casing looked alright, so I didn't add that extra overhead.

What Jeff is saying is that String comparison can be tricky, partial matches don't work - one needs to be the exact same as the other, which sometimes requires a bit of trickery.

This is where the human eye beats the computer by a long shot - we can see they're the same. A computer needs to see them as the exact same.

re: Fantasy Football Draft Board Tracking

Jeff — Tue, 01 Sep 2009 01:18:43 GMT

Cool post Nate! Go football season! I like the new blog layout too. Very aesthetic.

Trimming spaces can be a real pain but necessary part of living in a word of strings. In C# I almost always do "string.Trim().toUpper()" before comparing.

re: Interesting question - How to learn Excel?

Robert Martim — Mon, 17 Aug 2009 14:55:51 GMT

Nice post... This weekend I faced a course where I had to teach "Excel for Dummies". I had not tried teaching veeeery, veeeery, veeeery basic stuff. So, I guess that learning and teaching can be a challenge.

re: Let's talk sentence-casing in Excel

James_B — Wed, 29 Jul 2009 13:30:04 GMT

Dude, I love your stuff--both on this blog and elsewhere. Anyway, just to say, this SentenceCase function is turning out to be useful in the extreme: (when I first read this thread, I doubted this).

For my own purposes, I've had to modify (butcher?) the start and end of it so that it can handle arrays (that can be written back to a worksheet). There's doubtless a more optimised way of doing such a thing, but this does the job (I think) as a quick workaround. Cheers again, James.

P.S. Shout if I've made some stupid error in the below

Public Function SCase(ByRef sIn As Variant) As Variant

Dim e As Long

If IsEmpty(sIn) Then Exit Function

If TypeName(sIn) = "String" Then sIn = Array(sIn)

For e = LBound(sIn) To UBound(sIn)

Dim i As Long, ii As Long, UB As Long

Dim bArr() As Byte

If LenB(sIn(e)) = 0 Then Exit For

bArr = sIn(e)

UB = UBound(bArr) - 1 'since we're not looping the last byte

If bArr(1) = 0 Then '<-- only if zero

Select Case bArr(0)

Case 97 To 122, 224 To 246, 248 To 255 'some more lcase characters

bArr(0) = bArr(0) - 32

End Select

End If

For i = 2 To UB Step 2

If bArr(i + 1) = 0 Then 'only look at letters with 2nd byte = 0

Select Case bArr(i)

Case 105

If i <> UB Then

Select Case bArr(i + 2)

Case 32, 33, 39, 44, 46, 58, 59, 63, 148, 160

If bArr(i - 2) = 32 Then

bArr(i) = bArr(i) - 32

End If

End Select

ElseIf bArr(i - 2) = 32 Then

bArr(i) = bArr(i) - 32

End If

Case 33, 46, 58, 63

For ii = i + 2 To UB Step 2

Select Case bArr(ii)

Case 97 To 122, 193 To 246, 248 To 255

bArr(ii) = bArr(ii) - 32

i = ii: Exit For

End Select

Select Case bArr(ii)

Case 32, 33, 46, 63, 160

'Do nothing

Case Else

i = ii: Exit For

End Select

Next ii

End Select

End If

sIn(e) = CStr(bArr)

Next e

SCase = sIn

End Function

re: Interesting question - How to learn Excel?

Debra Dalgleish — Mon, 20 Jul 2009 02:12:22 GMT

I learned by helping my daughter with her homework. We created some charts in Excel 3.0 on our Mac.

Shortly after that, a training company asked me to write instructions for all the menu commands in that version, so I learned a lot by doing that.

re: Interesting question - How to learn Excel?

Joe Mako — Sun, 19 Jul 2009 23:42:20 GMT

The first thing I recommend to anyone looking learn how to use Excel effectively is Juice Analytics' Excel Training Worksheet at www.juiceanalytics.com/.../excel-training-worksheet

I have also recommended Pointy Haired Dilbert's Excel Formula Helper E-Book at chandoo.org/.../excel-formula-helper-e-book It is not free, but defiantly a better formula reference that the built in help for beginners.

re: Interesting question - How to learn Excel?

Nate Oliver — Sat, 18 Jul 2009 21:11:27 GMT

I've actually been a guest on one of Bill's podcasts, in Toronto.

I don't think J-Walk was involved in the 'Dummies' series, another name is coming to mind...

But you're right, there are resources out there and you can start on your own. I'm trying to describe what I think is an easier learning path. Very subjective stuff.

re: Interesting question - How to learn Excel?

Jeff Weir — Sat, 18 Jul 2009 20:56:15 GMT

From memory, Walkenbach's Excel for Dummies was a very good primer for me. I went from that to his Excel Bible, and also discovered Bill Jelen's podcasts at www.mrexcel.com which are truly outstanding as they use real world problems.

Only problem with these podcasts is that it takes months to work your way through them all...but they are worth it!