18.2 Tips on Rounding numbers using Excel Formulas

18.2 Tips on Rounding numbers using Excel Formulas:

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. To know how to use all these formulas and how to round numbers based on any criteria, just read on.

Rounding Formulas in Excel

Before learning the tips, first lets understand various rounding formulas & what they do. Look at this:

Formula	What it does?
ROUND	Rounds a number to specified decimal points (or multiples of 10)
ROUNDUP	Rounds up a number
ROUNDDOWN	Rounds down a number
MROUND	Rounds to nearest multiple of specified number
INT	Rounds down to nearest integer
TRUNC	Gives you only integer portion
CEILING	Rounds up a number to nearest multiple of 1,10,100…
FLOOR	Rounds down a number to nearest multiple of 1,10,100…
EVEN	Gives next even number
ODD	Gives next odd number
FIXED	Rounds and converts to text format (with commas if you want)

18 Rounding Formula Tips

1. Round to 2 decimal points

Example: 1.2649 to 1.26

=ROUND(A1,2) Rounds value in A1 by 2 decimal points

2. Round up to 2 decimal points

Example: 1.2649 to 1.27

=ROUNDUP(A1,2) Roundsup value in A1 by 2 decimal points (ie away from zero)

3. Round to nearest integer

Example: 1.2649 to 1

=ROUND(A1,0) By using 0, we can round the value to nearest integer

4. Round to nearest multiple of 10

Example: 544.234 to 540

=ROUND(A1,-1) By using negative numbers, we can round the value to nearest multiple of 10, 100…

5. Round up to nearest multiple of 10

Example: 544.234 to 550

=ROUNDUP(A1,-1)

6. Round to nearest thousand

Example: 312789123 to 312789000

=ROUND(A1,-3)

7. Round to nearest million with one decimal point

Example: 312789123 to 312.8

=ROUND(A1/1000000,1) First we divide the number by million (1,000,000) and then round this to 1 decimal point.

8. Round to nearest multiple of 2

Example: 43 to 44

=MROUND(A1,2) Just like round formula, but for any multiple. So MROUND(A1,2) takes value in A1 and rounds it to nearest multiple of 2

9. Round to nearest multiple of 5

Example: 93 to 95

=MROUND(A1,5)

10. Round down to hundred

Example: 301 to 300

=FLOOR(A1,100) To round down, we can use FLOOR formula.

11. Get only the integer portion of a number

Example: -23.34 to -23

=TRUNC(A1,0) To extract only the integer portion of number, use TRUNC formula. Note: INT formula gives same result for positive numbers.

12. Round a number to 2 decimals and convert to text

Example: 312789.26921 to 312,789.27

=FIXED(A1,2,FALSE) In one shot, round and convert the number to text. Useful when you want text output.

13. Get next even number

Example: 42.1 to 44

=EVEN(A1) Gets you next EVEN number (away from zero)

14. Get next odd number

Example: 44.93 to 45

=ODD(A1)

15. Round to nearest quarter ($0.25)

Example: 19.14 to 19.25

=MROUND(A1,0.25) MROUND can be used with fractions too.

16. Round to next 9 (ie 19,29,39 etc.)

Example: 23 to 29

=ROUNDUP(A1,-1)-1 To do this, we just roundup the number to next 10 and then subtract 1 from it.

17. Round up to next 1000

Example: 124567 to 125000

=CEILING(A1,1000) Just like FLOOR, but takes you to next value.

18. Get only decimal portion of a number

Example: 23.345 to 0.345

=A1-TRUNC(A1) To get only decimal portion, subtract TRUNC value from original

Download Rounding formula example workbook

Click here to download example workbook & understand these formulas better.

What about .2 tips?

Well, those are for you to fill down. Go ahead and write formulas for both these situations & you have the .2 tips!

18.1 Waiter friendly pricing

Lets say you run a hotel where customers usually tip 15% of bill amount. Now, to make it easy, you want to price your items such that when 15% is added, the total amount becomes a round number like $1.00, $2.00 etc.
For example: If a dish’s current price is $2.50, then 15% tip on it would be $0.37. This makes the total $2.87.

If you modify the price to $2.60, with tip the total would be $3.00.
Assuming current price of a dish is in A1, what formula will give you new price?

18.2 Rounding to nearest Monday

Lets say you have some dates in a list and you want them to round to nearest Monday. Assuming you have a date in A1, what formula gives nearest Monday?
Go ahead and figure them out. Post your answers using comments.
Click here to comment.

Introducing Excel School + Excel Hero Academy Bundle

Introducing Excel School + Excel Hero Academy Bundle:
Hello friends & Chandoo.org readers,
I am super-excited to announce an my first joint Excel course with none other than Excel Hero – Daniel Ferry. For the the first time, you get basic, intermediate, advanced & super-advanced Excel + VBA course in one neat package.
I am happy to present Excel School + Excel Hero Academy course bundle for your consideration.

What is this course bundle & How it can help you?

Simply put, this course package is designed to make you 2x awesome in Excel, VBA & Dashboards. Please watch below video to understand how our course can help you.

Who is this for?

Excel School is for you, if you are,

• New to Excel or have been using it for last few years
• Using Excel for data analysis, presentations, dashboard reporting & pivoting
• Keen to know various techniques, productivity tricks, short-cuts & hints to make you awesome at work
• Eager to become awesome at your work by using Excel

Excel Hero Academy is for you, if you are,

• Very good at Excel but want to know advanced formulas, chart interaction, animations, workbook optimization
• Using VBA, but want to write better macros, understand how everything works & create jaw-dropping forms & workbooks
• A learner at heart, wants to know different ways to solve your work problems and constantly improve
• Looking for a course that can take you cutting-edge of Excel development

Choose the bundle:
Our bundle is for you if want to start from a beginner level and move to highly advanced level in Excel & VBA.

It was so easy to be able to understand the methods. Most of the downloads can be adapted to my job and suddenly I have, in the eyes of my boss, become an excel expert. cannot think of a better or more efficient method of learning excel. I could never had learned this much from books.Thank you Chandoo.

- Terry Price

Benefits for you

We have designed this bundle to give you a lot. You get the following benefits by joining us.

• More than 50 hours of Excel & VBA training to make you awesome at your work. Right from Excel basics to writing your own VBA classes, you get everything. The course is peppered with practical examples, tips, best practice suggestions & hacks.
• Learn at your own pace: Whether you want to learn it slow or hungry for content, we got you covered. The course content is available 24×7 from our online classroom so that you can go thru the lessons whenever you want, wherever you are.
• Make Dashboards that your colleagues envy: In the exclusive 8 hour module on Excel Dashboards, I teach you how to create world-class, jaw dropping Dashboard reports using nothing but Excel. This portion includes full length examples, detailed explanations & design techniques. Go ahead and make your colleagues green with envy.
• 60 + Example workbooks: Learn by dissecting our work. Understand the lessons better by breaking apart our workbooks. Each of the files are well designed to teach you how to create workbooks that look awesome too.
• Homework & Class Projects: No learning is complete with out testing. So we give you well thought out challenges, home work assignments & class projects. By working on these practical problems, you will sharpen your skills and be able to respond better to work problems.
• 6 month access to our Online Classroom: Our online classroom is where all the students converge, learning from each other, discussing alternative ideas, sharing course notes,exploring homework solutions & networking with other Excel users. You can access our online classroom 24×7 for 6 months from the date you joined.
• 30 Day Money-back guarantee: Your membership comes with a 30 day money back guarantee. If you don’t like what you see in Excel School or EHA, just drop us an email and we will refund your money. No questions asked.

The content and videos in Daniel’s class are the best I have seen.

– Wanda Norrick

Wait, we have more…,

FREE Bonus #1: Excel Formula Crash Course – 31 Lessons

When you join EHA or EHA + Excel School, you get my Excel Formula Crash Course. It teaches you all aspects of Excel formulas – from beginner to advanced level in just 31 bite-sized lessons. This course valued at $62, is yours for free. Just make sure you join EHA or EHA + Excel School using below links & email me to get your copy.

FREE Bonus #2: Excel PDF guides – 3 pack

When you join any Excel School or EHA option, you will get 3 PDF guides. These are,

1. Excel Formula Cheat sheet – One page quick reference guide to common formulas, reference styles, error messages.
2. Keyboard Shortcut poster – Two page poster to remind you important productivity shortcuts when using Excel.
3. Chart design e-book – 25 page guide to creating awesome Excel charts in simple steps.

FREE Bonus #3: Interviews with Excel Experts

To make Excel School even more awesome, I have asked fellow Excel experts, MVPs to share their tips. With any Excel School enrollment, you get:

• Interview with Debra Dalgleish – Excel MVP & author on Pivot Tables
• Interview with Mike Alexander – Excel MVP & author on Excel Access Integration
• Interview with Robert Mundigl – Excel expert & blogger on Excel Dashboards

These are some of the top most experts on Excel in world. By learning from these experts, you widen your Excel perspective and become even more awesome at what you do.

Join Excel School + EHA

Excel School	Excel Hero Academy	Excel School + EHA
32 hours of Excel training 45 Excel workbooks Download HD Quality videos 6 Months access to online classroom Bonus material & PDF guides All this for just $247	18 hours of Advanced Excel + VBA 20+ Excel workbooks View HD Quality videos 6 Months access to Excel Hero Academy Special Bonus from Chandoo.org Prices from $499	50+ hours of Excel & VBA training 60+ Excel workbooks View HD Quality videos 1 year access to Excel Hero Academy Bonus material & PDF guides Most comprehensive & advance Excel training Prices from $677
Full feature comparison & Pricing details

Alternative Payment Options:

If you have trouble enrolling for the class with above links, use below options.

• Excel School alternative payment option
• EHA or EHA + ES alternative payment option

Frequently Asked Questions:

Q. What payment methods are accepted?

A. We accept all major cards (Visa, Master, Amex etc.) and various methods including PayPal, Google check out.
Q. Can I upgrade to another option after joining?

A. Yes, you can. You will get upgrade information inside our academy.
Q. Which version of Excel is used?

A. We use Excel 2007 & 2010 in this program. That said, the ideas you learn can be applied to all versions of Excel since Excel 2000.
More questions? Please email me at chandoo.d @ gmail.com or call me at +91 814 262 1090.

Chandoo – I’m glad you pointed folks to Excel Hero Academy. I think between the two of you, you’ve got a solid educational path for those of us wishing to master Excel. I too am an Excel Hero Academy student and I’ve been fully impressed with Daniels efforts and teaching technique. Some of you reading this may be able to convince your boss to pay for this training (as I did). It will surely pay for itself in productivity enhancements if you use Excel regularly (which I’m guessing you do if you are at this site!). C’mon aboard!

– Tom

PS: We are starting this class on October 1st. So hurry up and join us.

Using Excel for Business Analysis by Danielle [Book Recommendation]

Using Excel for Business Analysis by Danielle [Book Recommendation]:
Business analysis & modeling are new areas where a lot of fresh graduates find themselves when they start work. Unfortunately, this is also an area where there is very little structured information.  Thanks to Danielle’s new book, Using Excel for Business Analysis, you can understand the basics of financial modeling, good spreadsheet design & business analysis
First up, let me congratulate my good friend Danielle for the successful launch of her first book. Today, I want to share my review of this book with you.

Who is this book for?

• If you are someone who uses Excel to analyze & model parts of a business,
• If you are not sure how to go about it
• If you started using Excel in last few years and lack structured approach to spreadsheet design
• If you are a fresh MBA hoping to become an analyst

Then this book is for you.

What does it teach?

This book aims to teach us 3 things:

1. Lay clear foundations on financial modeling, business analysis and define best practices
2. Explore various features of Excel that help you in business analysis & modeling
3. Explain uses of Excel in various modeling & analysis scenarios.

1. Foundations:
In this Danielle explains what financial modeling is, how to choose a model and what best practices to follow. The casual tone & variety of examples in this section helps in understanding various steps in modeling and relevance of them. Towards the end of this section, Danielle shares various modeling best practices & pitfalls to avoid.
[Related: Introduction to Financial Modeling - 6 part tutorial]
2. Excel features:
This where you get to learn various Excel tools that we need to design & analyze. Danielle gives detailed explanations about various Excel formulas, formatting options, key board shortcuts, form controls etc. Once you know how to use these, you can combine them to analyze and model almost any business situation.
3. Using Excel for modeling & analysis:
In the last few chapters of her book, Danielle various common components in financial models like escalation methods, payback periods, WACC (weighted average cost of capital), tiering table, modeling depreciation etc. She also explains how to review models, rebuild inherited models, stress testing a model, performing sensitivity analysis & presenting model outputs using various Excel charts.
All in all, the 12 chapters in this book act as a handy guide to help you understand the business analysis & modeling process and how to implement it using Excel.
Read a sample chapter here.

Should you buy it?

Back when I started working as a business analyst (in 2006), fresh after MBA, I felt like a fish in the ocean. Lost and confused. It may be due to the fact that I was part of a very large company with more than 100,000 employees. Some of my first assignments involved in-depth analysis, modeling of insurance businesses. I spent count-less hours learning Excel, Power Point, analysis techniques and made a few mistakes before understanding things. I am still learning today.
But I feel that a guide like Danielle’s book can be very handy for someone starting their career or moving to modeling stream today.
If that sounds like you, please get a copy of her book.
Note: This book does not cover in-depth modeling or advanced Excel topics. For that you should consider one of my other recommendations.

Do you have this book? How do you like it?

Have you read this book? How do you like it? Please share your views using comments.

Disclosure:

Danielle & her publisher have been kind enough to send me a copy of this book. It goes with out saying that Danielle is my good friend & partner in business when I visited Australia recently. Also, When you use above link to purchase this book from Amazon, I get a small commission. That said, my review is purely based on what is in the book and how useful it can be.
PS: Do you think I should write few more book reviews? I do read a lot of Excel, charting & VBA related books. I can write a review once in a while. Let me know thru comments.
PPS: See a collection of books recommended by me here.

Formula Forensics No. 029 SumIf with Inconsistent Column Layouts

Formula Forensics No. 029 SumIf with Inconsistent Column Layouts:
About a month ago, Fred, asked a question at the Chandoo.org Forums:
“I have 2 formulae one using sumif and the other one using sumproduct. Both get the same correct answer but they look a bit too long to me and I can’t find a shorter way to express.
basically I need to find out the sales volume by names and I have names on columns W, AA and AC. There are sales figures on columns Z, AB and AD that goes respectively to the name columns.
Here are my formulae. X1 is the name I’d type in to find out the combined sales figures
SUMIF($W$9:$W$136,$X$1,$Z$9:$Z$136)+SUMIF($AA$9:$AA$136,$X$1,$AB$9:$AB$136)+SUMIF($AC$9:$AC$136,$X$1,$AD$9:$AD$136)
vs.
SUMPRODUCT(($W$9:$W$136=$X$1)*($Z$9:$Z$136))+SUMPRODUCT(($AA$9:$AA$136=$X$1)*($AB$9:$AB$136))+SUMPRODUCT(($AC$9:$AC$136=$X$1)*($AD$9:$AD$136))
or do any of you have an even better idea? Any suggestion?”

Haseeb A followed up with a neat solution that used both the Sumproduct() and Sumif() functions and threw in two Offset() functions just for fun, namely:
=SUMPRODUCT(SUMIF(OFFSET(B4:B9,,{0,4,6}),C1,OFFSET(E4:E9,,{0,2,5})))
Note: the Column Numbers have been altered from the original post

So today at Formula Forensics we will pull apart Haseeb A’s formula and see what makes it tick.
At Formula Forensics you can follow along using a sample file: Download Here – Excel 97-2013

The Problem

First lets look at the data and describe Fred’s problem
Fred wants to add up the values in Columns E, G & J when the preceding Columns B, F & H contain a value which is in cell C1 or “a”

This is shown diagrammatically below:

The solution is 183 = 10+40+60+7+10+14+42

Fred had a Sumproduct based solution:
=SUMPRODUCT(($B$4:$B$9=$C$1)*($E$4:$E$9)) + SUMPRODUCT(($F$4:$F$9=$C$1)*($G$4:$G$9)) + SUMPRODUCT(($H$4:$H$9=$C$1)*($J$4:$J$9))
Which is simply 3 Sumproduct formulas, one for each column pair, with separate criteria in each

Fred also had a Sumif based solution:
=SUMIF(B4:B9,C1,E4:E9)+SUMIF(F4:F9,C1,G4:G9)+SUMIF(H4:H9,C1,J4:J9)
Which similarly is 3 Sumif formulas, one for each column pair, with separate criteria in each

Haseeb A’s solution: =SUMPRODUCT(SUMIF(OFFSET(B4:B9,,{0,4,6}),C1,OFFSET(E4:E9,,{0,2,5})))
Combines a Sumproduct(), Sumif() and two Offset() functions to do the same as the above two formulas.
It is actually 2 characters longer than Fred’s Sumif’s based formula but this is quickly overcome if further ranges are added.

Hasseb A’s Solution

Haseeb A’s formula
=SUMPRODUCT(SUMIF(OFFSET(B4:B9,,{0,4,6}),C1,OFFSET(E4:E9,,{0,2,5})))
Is based around the Excel Sumproduct() function.
=SUMPRODUCT(SUMIF(OFFSET(B4:B9,,{0,4,6}),C1,OFFSET(E4:E9,,{0,2,5}) ) )
As we saw in Formula Forensics 007, Sumproduct, Sums the Products of the included arrays.
In this case there is only a single included array consisting of a Sumif() function and so Sumproduct will simply Sum the values returned from the Sumif() function.

Lets look at the Sumif() function.

SUMIF(OFFSET(B4:B9,,{0,4,6}),C1,OFFSET(E4:E9,,{0,2,5}))
The Excel Sumif() function has the following syntax:

In our example: SUMIF(OFFSET(B4:B9,,{0,4,6}), C1, OFFSET(E4:E9,,{0,2,5}))
Range: OFFSET(B4:B9,,{0,4,6})
Criteria: C1
Sum_Range: OFFSET(E4:E9,,{0,2,5})

This reads as follows: Sum the Range OFFSET(E4:E9,,{0,2,5}) when the Range OFFSET(B4:B9,,{0,4,6}) is equal to the value in cell C1.

What are these OFFSET(B4:B9,,{0,4,6}) and OFFSET(E4:E9,,{0,2,5}) parts doing ?

Let’s start with: OFFSET(B4:B9,,{0,4,6})
The offset function has the following syntax:

In our first Offset() example: OFFSET(B4:B9,,{0,4,6})
Reference: B4:B9
Rows: Blank = Nil or 0
Cols: {0,4,6} is an array of 0, 4 & 6
Height: Not Used (Optional)
Width: Not Used (Optional)

So Offset is taking the Range B4:B9 and offsetting it by the Column values of 0, 4 & 6.
This is the same as saying use:
B4:B9 (Offset 0) = B4:B9
B4:B9 (Offset 4) = F4:F9
B4:B9 (Offset 6) = H4:H9

Similarly in the second Offset function

In our example: OFFSET(E4:E9,,{0,2,5})
Reference: E4:E9
Rows: Blank = Nil or 0
Cols: {0,2,5} is an array of 0, 2 & 5
Height: Not Used
Width: Not Used

So Offset is taking the Range E4:E9 and offsetting it by the Column values of 0, 2 & 5.
This is the same as saying use:
E4:E9  (Offset 0) = E4:E9
E4:E9  (Offset 2) = G4:G9
E4:E9  (Offset 5) = J4:J9
If you don’t believe me that OFFSET(E4:E9,,5) is the same as saying J4:J9
In a spare cell, G28 enter: =COLUMN(OFFSET(E4:E9,,5)) and press Enter
Excel responds with 10, the column number of Column J.

For your information if you enter: =Row(OFFSET(E4:E9,,5))
Excel will return 4, which is the top Left corner of the new Range which now goes from J4:J9

So lets try and put all this together:

The Sumif() part of the formula is the same as using three separate Sumif() formulas, one for each value in the value array part of each offset
It is forced to be evaluated three times because it is in the array component of the Sumproduct function.
We can see how each Sumif part works if we look at each array component separately:

The First array values
In a spare cell G23 enter =SUMIF(OFFSET(B4:B9,,0),C1,OFFSET(E4:E9,,0)) and press Enter
Excel returns 110 which is the value of the three marked cells E4, E7 & E9

The Second array values
In a spare cell G24 enter =SUMIF(OFFSET(B4:B9,,4),C1,OFFSET(E4:E9,,2)) and press Enter
Excel returns 17 which is the value of the two marked cells G6 & G9

The Third array values
In a spare cell G25 enter =SUMIF(OFFSET(B4:B9,,6),C1,OFFSET(E4:E9,,5)) and press Enter
Excel returns 56 which is the value of the two marked cells J5 & J9

Finally summing the three values together (110+17+56) gives 183 which is what the Sumproduct() function does with the three values returned from the Sumif() function.

Extension

You can see that this technique is easy to extend to more than 3 columns by simply adding extra column offsets in the two Offset functions in the formula
=SUMPRODUCT(SUMIF(OFFSET(B4:B9,,{0, 4, 6, Col 4, Col 5, Col 6, etc}),C1,OFFSET(E4:E9,,{0, 2, 5, Col 4, Col 5, Col 6, etc})))

Download

You can download a copy of the above file and follow along, Download Here – Excel 97-2013.

Formula Forensics “The Series”

This is the 29th post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Customize Zebra lines Quickly using Table Styles [tip]

Customize Zebra lines Quickly using Table Styles [tip]:
Zebra lines, highlighting applied to alternative rows is a very good way to make tables readable & pretty.
We can use either conditional formatting or table formats to quickly add zebra lines to our data.
But what if you want a little more?
What if you want to highlight, lets say 3 rows in one color and 3 in another and repeat this …

Of course, we can use conditional formatting and come-up with some clever mix of ROW & MOD. But why waste so much of creative sauce on something as trivial as zebra line? So here is a quick alternative.
Use Table styles and tell excel how you want to highlight the lines.

Steps to create custom zebra lines

1. First convert your data to table, if you have not already done it.
2. Now, go to Table Design ribbon and right click on the table style you want and choose duplicate
3. Excel creates a duplicate table style and opens a box to edit it.
4. Give it a friendly name like myTableStyle1 or zebra-v1
5. Select “First Row Stripe” and set stripe size to 2 or 3 as you want.
6. Repeat the step for “Second Row Stripe” as well.
7. Click OK & save your style.
8. And now, apply this style to your table by selecting it from Table styles gallery.
9. Your custom zebra is ready to bray and jump around.

Bonus tip #1: You can have 2 different stripe sizes too

For example you can have 5 rows for first stripe and  2 for second stripe, thus highlighting weekends in a different color.

Bonus tip #2: You can apply the same to columns too

You can apply the same concept to column stripes (banded columns) and set their sizes using table styles.

Bonus tip #3: Turn on / off zebra lines with a click

If you ever feel tired looking at all the stripes, you can quickly turn them off /on from Design Ribbon > Banded Rows

Homework: Change color when value changes

When you have few values with some duplicates, it makes sense to apply a band color whenever there is a change in value. How to set up zebra lines then? See here for your homework.
[Related: Zebras & Checker boards using Excel]

Do you use custom table styles?

Custom table styles are an easy way to tell Excel how we want our data to look. I use them often when designing a report or spreadsheet model.
What about you? Do you use custom styles? Have you tried the stripe size feature? What is your experience like? Please share using comments.

OFFSET formula – Explained

OFFSET formula – Explained:
Today, lets learn OFFSET formula.

What is OFFSET and why bother using it?

OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.

OFFSET formula syntax

OFFSET formula looks like this:

=OFFSET(starting point, rows to move, columns to move, height, width)

• Starting point: This is a cell or range from which you want to offset
• Rows & columns to move: How many rows & columns you want to move the starting point. Both of these can be positive, negative or zero. More on this below.
• Height & width: This is the size of range you want to return. For ex. 4,3 would give you a range with 4 cells tall & 3 cells wide.

And yes, All the arguments to OFFSET can be references to other cells. That means, you can write =OFFSET(A1,D1,D2,D3,D4) which will refer to a range

• Starting from A1
• Offset by D1 rows & D2 columns
• having the size of D3 rows & D4 columns

See below examples to understand the formula better.

OFFSET formula examples

Why use OFFSET?

Why not write a reference like A1:C4 directly?
Here are a few reasons why,

1. Dynamic ranges: Reference like A1:C4 always refers to the range A1:C4. ie it is static. But sometimes, we want our ranges to be dynamic. This is required because our data is changing (every month new row is added, every time we launch a product new column is added etc.)
2. We don’t know the exact address: Sometimes, we don’t know what our ranges actual address is. Rather, we just know it is starting from a certain cell etc. In such situations OFFSET is useful.

Understand OFFSET formula – Interactive Workbook

Since OFFSET formula is somewhat tricky to get, I created an interactive workbook so that you can understand how it works. When you input all the 5 parameters, the workbook highlights the range that your OFFSET will give. After playing with it for a few minutes, you will understand the formula better.

Practical use for OFFSET – Average of latest week

Lets say we monitor quality of a plant producing purple puppets. One of the KPIs we monitor is % of rejected puppets. We have been tracking the % of rejects by day in a spreadsheet that looks like this:

So how do we calculate average of latest week?
Assuming the values are in range C3:C18, we can write =AVERAGE(C12:C18)
BUT, WE NEED TO CHANGE THIS FORMULA EVERYDAY!!!
Even puppets would find that boring.
By using the OFFSET awesome sauce, we can write the AVERAGE formula once and forget about it.
=AVERAGE(OFFSET(C3,COUNTA(C3:C300)-7,0,7,1))

Lets break-apart this formula and understand

• To calculate latest week’s average, we need to go all the to the last data point and then get 7 rows from it and average those values.
• This is where COUNTA(C3:C300) – 7 comes in to picture. It counts how many values are there in column C and then subtracts 7 from it.
• The OFFSET would then starting point from C3 to latest week’s starting point.
• To know how this formula works, watch below demo.

OFFSET limitations

While offset formula can return with a dynamic range when you beckon, it does have few limitations:

• OFFSET formula is volatile: In plain English it means, whenever there is any change in your workbook, OFFSET formula is recalculated, thus keeping Excel busy a tiny bit longer. This is not an issue if you use OFFSET formula in a small workbook. But when you use lots of OFFSET formulas in large workbooks, you will end up cursing Excel as it takes too much time to recalculate.
• OFFSET formulas are tricky to debug: Because the references are dynamic, debugging a workbook with lots of OFFSETs can get tricky quickly.

Alternatives to OFFSET formula

There 2 fine alternatives to OFFSET formula.

• Use Excel Tables: Since Excel 2007, we can create tables from structured data and write formulas, create charts that refer to dynamic ranges with ease. Click here to know more about tables.
• Use INDEX formula:  Although not exactly same as OFFSET, INDEX formula can also be used to generate dynamic range references. Plus, INDEX is a non-volatile formula, so it wont keep Excel busy unnecessarily. Know more about INDEX formula.

Do you use OFFSET formula?

For most of my dynamic range needs, I rely on tables or INDEX formula. I use OFFSET formula when I have to calculate values like average of latest week. In such cases OFFSET is an elegant solution.
What about you? Do you use OFFSET formula? In which situations do you use it? Please share your tips & examples with us using comments.

Know More about OFFSET

Check out below examples to understand OFFSET formula better:

• Calculations: Sum of values between 2 dates | Moving averages | Average of closest numbers| More…
• Modeling: Calculate IRR of dynamic ranges | Manage scenario analysis
• Charting & Dashboards: Dynamic range charts | Top x chart | Analyzing large datasets | KPI dashboards
• Validations & Pivots: Dynamic Data Validation | Dependent Drop downs | De-duplicate & Sort data
• And many more uses of OFFSET