Remember Excel School? It was all I talked about between Jan last week and middle of Feb. Then as if someone has pressed mute button, there was not even a single mention about the school. So I thought, why not give you all an update on Excel School and tell you how the classes are going.
Some stats about the program:
- A total of 147 students signed up for the program.
- 3 Students dropped out for various reasons.
- Out of total 12 weeks, we have finished 8 weeks of lessons now.
- We have covered extensively on topics like excel formulas, charting, conditional formatting, formatting, tables and pivot tables until now. We also did a class project.
- In the next 4 weeks we will be talking about data validation, filters, importing data, advanced formulas, macros and do another class project.
- There were a total of 25 lessons, with roughly 800 minutes of video lessons and several downloadable excel workbooks.
- A total of 219 comments were posted by students discussing lesson topics, asking questions and doubts.
What is the student feedback?
While it is a little early, I am very happy to tell you that students are really enjoying Excel School and have been liking what is shared so far. Some encouraging comments I have received are,
I just wanted to give you some feedback on our lessons so far. While I wouldn’t consider Excel school to be a beginner’s 101 course, I wish I had found something with this level of instruction years ago.
I’m having a blast looking at things that I thought I understood well. You have given some new insight into several things that I’ve been able to apply to some workbooks at my job. Just with one powerful little formula tweaks I figured you saved me and a colleague 475 minutes per month on a monthly report that we have to prepare. Over a years time that equates to over $3500. That’s only one instance.
This is the best instruction that I’ve ever come across. You make Excel fun! THANK YOU! Thank you for being so generous with all of the great information that you share. You truly are awesome!
I am a member of your Excel School and continuously watching your postings. I just want to thank you personally for providing me such a good excel tutorials.
I really am learning a lot…I am stubborn and will keep trying till I get the ideas. And you have so many wonderful tricks I never knew about. I got started with you in charting and I have learned so much and done some nifty charts. I am looking forward to learning a lot more from you in the future. I am amazed how much you know and how well you are able to explain things. I thought I was pretty proficient in formatting till I watched week 2 and I have discovered that there is much more I do not know than I do. Can’t wait for the rest of the classes.
I just wanted to tell you that so far in the Excel School, I am blown away with the quality and amount of information I’m getting out of the course. I have always been the excel expert where ever I work, and it’s inspiring to see what you’ve been able to do with it. I didn’t realize there was so much more to learn.
Been taking an online class in Excel. About 6 weeks into it and wow, I have learned a lot. And I am the best Excel user in my department at work. Class is hosted by Chandoo and he is good, really good. Runs a great blog called Pointy Haired Dilbert. A lot of value in this thing: class was approximately $100 for 15 weeks of awesome-ness. … I am loving it. … The surprising thing I have learned is how to better present information for others. I have always taken pride in making good spreadsheets instinctively, but this has made me much better.
My thoughts on the program so far,
To be frank I was *very* nervous when I closed the signups for the excel school. Having 147 students in the class, each with different and probably very high expectations psyched me a bit. To top it, after running barely one week of the program, I had to move from Denmark to India and that posed different challenges (mainly on bandwidth, internet connectivity, free time fronts). Having 2 small babies at home didn’t help either.
But, the students are generous, eager and fun. They lapped up the lessons with enthusiasm and discussed topics with a sense of humor. They supported me when I told them I had to lie low on comment replies during my transit from DK to IN. They didnt mind when a video link was broken or download went 404. They just gave me time to correct it. Few enthusiastic fellas even emailed me and told how *awesome* the classes are and helped me gain confidence. Thank you.
The promised goodies…
I am hoping to start the next batch of Excel School at End of May 2010. I am actively looking for students now…
So if you are interested in an online excel training program please tell me your name and email address. I will update you once the program is ready for registration. Also, you will be receiving 3 free lessons,
- Excel conditional formatting lesson – as soon as you sign up below
- SUMPRODUCT Formula lesson – by End of April
- Excel Pivot Table Tricks (lesson by Debra Dalgleish and me) – by end of May.
Please use the below form (click here if are not able to see it) to express your interest in Excel School Program:
PS: For Excel School Curriculum & Details pls. visit – http://chandoo.org/wp/excel-school/
PPS: The price of Excel School will remain same at $97
Are you a student? What do you think of Excel School?
If you are an Excel School student, please take a minute and share your opinion of the program thru comments. I would love to know how it is helping you be more awesome in excel. Also, pls. share your suggestions for improvements for future batches.
12 Responses to “29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]”
Some great contributions here.
Gotta love the Friday 13th formula 😀
Great tips from you all! Thanks a lot for sharing! bsamson, particularly you helped me on a terribly annoying task. 🙂
(BTW, Chandoo, it's not exactly "Find if a range is normally distributed" what my suggestion does. It checks if two proportions are statistically different. I probably gave you a bad explanation on twitter, but it'd be probably better if you fix it here... 🙂 )
Great compilation Chandoo
For the "Clean your text before you lookup"
=VLOOKUP(CLEAN(TRIM(E20)),F5:G18,2,0)
I would like to share a method to convert a number-stored-as-text before you lookup:
=VLOOKUP(E20+0,F5:G18,2,0)
@Peder, yeah, I loved that formula
@Aires: Sorry, I misunderstood your formula. Corrected the heading now.
@John.. that is a cool tip.
Hey Chandoo,
That p-value formula is really great for a statistics person like me.
What a p-value essentially is, is the probability that the results obtained from a statistical test aren't valid. So for example, if my p value is .05, there's a 5% probability that my results are wrong.
You can play with this if you install the Data Analysis Toolpak (which will perform some statistical tests for you AND provide the P Value.)
Let's say for example I've got two weeks of data (separated into columns) with the number of hours worked per day. I want to find out if the total number of hours I worked in week two were really all the different than week one.
Week1 Week2
10 11
12 9
9 10
7 8
5 8
Go to Data > Data Analysis > T-Test Assuming Unequal Variances > OK
In the Variable 1 Box, select the range of data for week 1.
In the Variable 2 Box, select the range of data for week 2.
Check "Labels"
In the Alpha box, select a value (in percentage terms) for how tolerant you are of error.
.05 is the general standard; that is to say I am willing to accept a 95% level of confidence that my result is accuarate.
Select a range output.
Excel calculates a number of results: Average (mean) for each week's data, etc.
You'll notice however that there are two P Values; one-tail and two-tail. (one tail tests are for > or .05), the number of hours I worked in week two is statistically equivalent to the number of hours I worked in week one.
So here’s a way you might want to use this. You put up a new entry on your blog. You think it’s the best entry ever! So you pull your webstats for this week and compare it to last week. You gather data for each week on the length of time a visitor spends on your website. The question you’re trying to prove statistically is whether there’s an average increase in the amount of time spent on your website this week as compared to last week (as a result of your fancy new blog post). You can run the same statistical test I illustrated above to find out. Incidentally, it matters very little to the stat test whether the quantity of visitors differs or not.
Anyhow, the Data Analysis toolpack doesn't perform a lot of stat tests that folks like me would like to have access to. In those cases I have to either use different software, or write some very complicated mathematical formulas. Having this p-value formula makes my life a LOT easier!
Thanks!
Eric~
Fantastic stuf..One line explanation is cool.
Thanks to all the contributors
OS
Take FirstName, MI, LastName in access (you can fix it to work in excel) capitalize first letter of each and lowercase the rest and add ". " if MI exists then same for last name:
Full Name: Format(Left([FirstName],1),">") & Format(Right([FirstName]),Len([FirstName])-1),"") & ". ","") & Format(Left([LastName],1),">") & Format(Right([LastName],Len([LastName])-1),"<")
I teach excel, access, etc etc for a living and i have my access students build this formula one step at a time from the inside out to show how formulas can be made even if it looks complicated. Yes I know I could just do IsNull([MI]) and reverse the order in the Iif() function but the point here is to nest as many functions as possible one by one (also I illustrate how it will fail without the Not() as it is)
Extract the month from a date
The easiest formula for this is =MONTH(a1)
It will return a 1 for January, 2 for February etc.
if in a column we write the value of total person for eg. 10 if we spent 1.33 paise each person then how we get total amount in next column and the result will in round form plzzzzz solve my problem sir................... thank u
@Anjali
If the value 10 is in B2 and 1.33 paise is in C2 the formula in D2 could be =B2*C2
If the values are a column of values you can copy the formula down by copy/paste or drag the small black handle at the bottom right corner of cell D2
kindly share with me new forumulas.
How to convert a figure like 870.70 into 870 but 871.70 into 880 using excel formula ? Please help.