# Tag Archives: IF

## SUM the last X non-blank values in a column in #Excel

Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He … Continue reading

### Rate this:

Posted in Excel in Action, Formula | Tagged , , , | 2 Comments

## Determine leap year with #Excel

Today is a special day.  Today is February 29th, which happens only once every four years.  And today is Saturday, when I usually post a new blog.  So let’s talk about how to determine if a year is leap year using … Continue reading

### Rate this:

Posted in Formula | Tagged , , , , , , , | 2 Comments

## Apply Conditional Format for alternate row color based on groups…

…that works even when filter is applied. The situation: We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, … Continue reading

### Rate this:

Posted in Formula | | 2 Comments

## Display descriptive words instead of numbers with Custom Format in #Excel

Sometimes we want to add a descriptive like “Good Job” for figures above target; “Work Harder” for those below… like the following screenshot: To many people, the top-of-mind solution should be using IF function. =IF(A2>0, “Good Job”, IF(A2=0, “Just Met”,”Work … Continue reading

### Rate this:

Posted in Format | Tagged , | 2 Comments

## How to turn “1st January, 2017” into #Excel recognizable date?

Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc… In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, … Continue reading

### Rate this:

Posted in Formula | Tagged , , , , , , | 8 Comments

## Using MIN/MAX to answer whichever lower/higher questions

What is “Whichever higher”? Situation: You call to your favourite restaurant to reserve a private room for family dinner on Saturday.  The restaurant manager tells you that there will be a minimum charge of \$2000 for a private room.   That mean, … Continue reading

### Rate this:

Posted in Formula | Tagged , , | Leave a comment

## Nested IF vs. VLOOKUP – Which one to use?

A well-drawn flowchart or a well-organized table helps you write a successful formula effectively Continue reading

### Rate this:

Posted in Excel Tips | Tagged , , | 6 Comments

## IF, it is too complicated…

Writing an IF function is quite straight forward, if there is only one logical test involved. For example, we want to comment on a shop’s performance by looking at its actual sales. If Actual hits Budget, then Good job, else … Continue reading

### Rate this:

Posted in Formula | Tagged | 12 Comments