101 Ready to Use Excel Formulas
It’s for sale. If you pre-ordered, it’s probably on the way. If you didn’t, well, you know what to do.
I got my copy last night. My wife, who is not an Excel geek by any stretch of the imagination, is excited to read it. At 240 pages, it’s not the usual phone book sized tech book, and it seems more accessible to her. What she doesn’t realize is that it’s packed with awesome formulas and she’s actually going to learn something. That’ll show her.
After being on sale for a few hours it’s the 643,122nd ranked book in the world. Let’s see if we can get that up to 643,121.
643122 out of…
Yay! It’s also available for Nook at http://www.barnesandnoble.com/w/101-ready-to-use-excel-formulas-michael-alexander/1117917275?ean=9781118902684
I reckon that must be in the top percentile of books in the world Jeff.
You’ve been swindled Dick, you wrote it and Walk still gets his picture on it!
@jeff: Not sure. Maybe 100 trillion. It’s up to 216,554. If my projections are correct, it should be #1 sometime Sunday morning.
@bob: They asked me for a picture to put on the cover, but I didn’t have any where I wasn’t holding a beer. So they went with Jwalk. Probably for the best.
Congrats on finishing the book :)
I have just received the book and read among others, formula 3: Calculating Percent Variance with Negative Values.
The authors state the following formula for percent variance: =(C4-B4)/ABS(B4) where:
B4 =Budget = -10,000
C4 = Actual = 12,000
The formula returns 220% which the authors claim to be the correct percent variance.
This topic may have been discussed to death elsewhere, but I can’t resist the temptation to question the proposed formula. I know it is risky business to challenge such authorities as the authors of this book and even Microsoft, cf. http://support.microsoft.com/kb/214078. None the less I will hold that absolute changes are meaningful whereas percentage change are not particularly meaningful when you go from a negative value to a positive or the other way round.
The above formula gives for example the following results:
B4 = -10
C4 = 10
Result = 200%
B4 = -1
C4 = 10
Result = 1100%
How can an increase from a smaller number (-10) to 10 be a lesser percentage than an increase from a larger number (-1) to 10?
I’m not a mathematician, but I don’t think percent change with values of opposite signs is defined.
See also:
http://online.wsj.com/public/resources/documents/doe-help.htm
(the section named Net Income)
You might want to try the following in an empty sheet:
In B1 enter -100, and in C1 enter -99
Select B1:C1 and drag to GT1
In A2 enter -100 and in A3 enter -99
Select A2:A3 and drag to A202
In B2:GT202 enter the formula: = =B$1/$A2-1
Make an XY-chart of A1:GT202
Thanks for pointing that out Hans. Very interesting. Please ignore those black Microsoft helicopters in your neighborhood.
Maybe:
=(A1-A2)/IF(A2<0;1;A2)