All articles with 'Microsoft Excel Formulas' Tag

In today’s installment of project management using excel, we will learn about project tracking tool – to-do lists. Projects are nothing but a group of people getting together and achieving an objective – like building system or constructing a bridge. While it is important to have a overall project plan and vision, it is equally important to understand how various day to day project activities are going on. This is where to do lists can help you a lot. Read on…

This is a 6 part tutorial on project management using microsoft excel. These posts represent few of the things related to project management using excel that I have learned over the years. The first installment deals with Preparing & tracking a project plan using Gantt Charts in MS Excel.

The other parts I am planning are, Day to day operations – using to do lists, Preparing a project time line, Time sheets and Resource management, Tracking issues and risks, Project Status Reporting – Dashboard.

Read the first part of the article to learn how to make excel gantt charts / project plans.

Dot plots are a very popular and effective charts. According to wikipedia “Dot plots are one of the simplest plots available, and are suitable for small to moderate sized data sets. They are useful for highlighting clusters and gaps, as well as outliers.”

Today we will learn about creating in-cell dot plots using excel. We will see how we can create a dot plot using 3 data series of some fictitious data.

We all know that networkdays() an extremely powerful and simple excel formula can help you calculate no. of working days between 2 given dates.

But there is one problem with it. It assumes 5 day workweek starting with Monday to Friday. Not all countries have workweek from Monday to Friday.

This got me thinking and I ended up writing a user defined formula (UDF) to calculate working days between 2 given dates with any criteria. This will be good for calculating payrolls for temporary workers, offshore partners and of course people working countries where Saturday or Sunday or not usually holidays.

Okay, that may appear a bit too fast. But the new improved version of our excel formulas e-book, now called “Excel Formula 1” is out today. I have included 25 more formulas and improved the content and layout greatly. The book costs $10 and you can buy it from here. If you want to be […]

I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth’s Art of Computer Programming and thus began my love with all things random and prime. I never really became the math-geek I always wanted to, instead I became an insurance expert with tons of passion for data and visualization. But when I get a chance to poke with randomness or numbers, I always lap it up with joy. And that brings us to an interesting array formula trick to check if a number is prime or not.

(assuming the number is in the cell B2) type the below formula and

=IF(MIN(MOD($B$5,ROW(INDIRECT(“2:”&INT(SQRT($B$5))))))=0,”not prime”,”prime”)

hit ctrl+shift+enter and bingo, it tells you if the number is prime or not. Read on to find how it works

Here is a quick excel printing tip. Use the page setup options from print preview area of the spreadsheet to select how you want to handle the formula errors when the worksheet is printed. You can set it to blank from the “sheet” tab of the page setup dialog in Microsoft excel

In the third installment of your week at PHD, we have a fantastic tutorial on R1C1 style of formula referencing, a nifty vlookup hack and adding web toolbars to excel UI. We have one more day to go, so rush!

That is right. Next week is your week on this blog. I will post content that is shared by you. So go ahead and share your tips and tricks with us. Read more to find out how you can share your tips with the community.

Here is a fun way to edit formula ranges. Just select the formula, press F2 and when you see the borders around the ranges used in that formula start dragging / resizing these borders to edit the formula range.

In this installment of spreadcheats we will learn how to understand excel formula errors and fix them. The #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE! errors and how to fix them.

Do you know that it is not so straight forward to use credit card numbers in excel. Yes, excel uses a precision of 15 digits and thus when you enter a credit card number (16 digits) it is converted in to scientific format and the details are lost. Read on to learn the work around.

In excel conditional formatting basics article, we have learned the basics of excel conditional formatting. In this article we will learn how to highlight top 5 or 10 values in a list using excel as shown aside. We will use LARGE excel formula and conditional formatting to do this.

Good news!!! We have upgraded the excel formula help page to a new, more easily accessible and better laid out page. Find out more…

Do you know What is excel conditional formatting? Learn the basics, few examples and see how you can use it in day to day work in this installment of spreadcheats.