Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

All articles with 'if()' Tag

Highlighting Repeat Customers using Conditional Formatting [Part 2 of 2]

Published on Jan 6, 2010 in excel apps, Learn Excel
Highlighting Repeat Customers using Conditional Formatting [Part 2 of 2]

This is second part of 2 part series on conditionally formatting dates in excel.

Highlighting Repeat Customers using Conditional FormattingIn yesterday’s post we have learned how to conditionally format dates using excel. In this article, you will learn how to use these conditional formatting tricks to highlight repeat customers in a list of sales records.

Continue »

Top X chart – Show Top X values of a chart Interactively

Published on Nov 12, 2009 in Charts and Graphs, Learn Excel
Top X chart – Show Top X values of a chart Interactively

Two charting principles we hear all the time are,

  • Sort your data in a meaningful order before plotting it.
  • Show only relevant information, not everything – because un-necessary information clutters the chart.

Today we will learn a dynamic charting technique that will mix these two ideas in a useful way. I call this a Top X chart.

Continue »

Baby Feeding Chart using Excel

Published on Oct 12, 2009 in Charts and Graphs, Learn Excel
Baby Feeding Chart using Excel

While feeding the babies last night, I had this idea. Why not make an excel sheet where you can keep track of the baby’s feeding and sleeping activities on day to day basis. It would probably help you understand your baby’s needs better and may be give you some insights.

Continue »

29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn Excel

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Continue »

Create a number sequence for each change in a column in excel [Quick Tip]

Published on Jun 29, 2009 in Excel Howtos, Learn Excel
Create a number sequence for each change in a column in excel [Quick Tip]

Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]

Continue »

Team To Do Lists – Project Tracking Tools using Excel [Part 2 of 6]

Published on Jun 25, 2009 in excel apps, Featured, Learn Excel
Team To Do Lists – Project Tracking Tools using Excel [Part 2 of 6]

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…

Continue »

Array formula to check if a number is prime [just for fun]

Published on May 29, 2009 in Learn Excel

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

Continue »

Formula Debugging in Excel – Tip [spreadcheats]

Published on Dec 15, 2008 in Learn Excel
Formula Debugging in Excel – Tip [spreadcheats]

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.

Continue »

Advanced Data Validation Techniques in Excel [spreadcheats]

Published on Nov 25, 2008 in Learn Excel
Advanced Data Validation Techniques in Excel [spreadcheats]

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

Continue »

Learn Excel IF Formula – 5 Tricks you Didnt know

Published on Jun 9, 2008 in Featured, Learn Excel
Learn Excel IF Formula – 5 Tricks you Didnt know

Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn […]

Continue »