All articles with 'OFFSET()' Tag

Welcome back to another gripping episode of “When good formulas go GREAT”.  Just like the immortal combatants in the classic 1986 movie Highlander, over the last three posts in this series our Excel nerds have been locked in an ages-old battle to decapitate. A text-string, that is. Not each other. So far we have seen […]

Today, Formula Forensics examines 3 techniques to determine an interpolated value along a curve.

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.

Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.

Hurricane Sandy has taken front seat in all major news channels, papers, websites even in far off places like India. I hope & pray that our readers in US East coast are safe.

Today, lets understand the journey of Hurricane Sandy in this animated chart, prepared by Chris from Excel365.

How do we perform a Sumif on seemingly randomly spaced columns?
Today in Formula Forensics we look at a solution to do just that.

Today, lets learn OFFSET formula.
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.

Read on…,

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Is my number a Prime Number?
What is a Prime Number?

Today in Formula Forensics we answer both questions.

Today at Formula Forensics, we look at how to Count and Sum data using Criteria on Filtered data sets.

How can we Add Up the Odd numbers between 1 and 100 ?

This week many Excel bloggers are celebrating VLOOKUP week. So I wanted to chip in and give you a comprehensive guide to VLOOKUP & Other lookup formulas. Read on …,

What is VLOOKUP Formula & how to use it?

I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.

As part of Speedy Spreadsheet Week, I have emailed few renowned Excel experts and asked them to share their tips & ideas to speedup Excel. Today, I am glad to present a collection of the tips shared by them. Read the Excel optimization & speeding up tips shared by Hui, Luke, Narayan, George, Gregory & Jordon.

Cornelia wanted to cumulatively increases prices to a lot of products.
See how it was accomplished using an Array Formula to assist.