Excel OFFSET Function for Dynamic Calculations – Explained in Simple Steps

28
12



OFFSET tells Excel to “fetch” a cell location (address) from within a data range. Download the Workbook here:

Get the full course:

Excel OFFSET Function Tutorial Summary:

The offset formula looks like:
=OFFSET(starting point, rows to move, columns to move, height, width)
Here are practical uses of OFFSET:
• Dynamic Calculations: The OFFSET formula can be included in other Excel functions to get dynamic ranges. For example, calculating the average sales of the last 3 months based on a selection
• Flexible Chart Ranges: In conjunction with charts it can be used in the NAME manager to add flexibility to chart data. The OFFSET function behaves like an Excel table where the data range automatically expands and contracts when chart data is updated.
• Dynamic dashboards: It can be used together with Excel’s Form Options to allow for dynamic analysis and reports based on user-selection.
You can tell the OFFSET function to return one cell if you specify height and width as 1. It can return an array of cells when the height and width are greater than 1. Height and/or width CANNOT be zero, because you either need 1 cell or a range of cells. In the illustration above, the height and width are 2.
This video shows how you can use the Excel OFFSET function to calculate the average value for the last 6 months. You will also learn how to use the OFFSET formula to calculate the average value based on the date selected by the user.
(You will see formula combinations such as OFFSET, COUNTA and MATCH functions)
In general Excel OFFSET function is an interesting formula and it’s one that can do so much more than meets the eye. It can used inside many formulas to provide them with flexible ranges instead of fixed ranges. This makes it a perfect candidate for dynamic reports and dashboards.

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://nhadattv.net

Xem thêm bài viết khác: https://nhadattv.net/cong-nghe/

28 COMMENTS

  1. i have a problem with using the offset formula as i want to take an average of last 26 numbers only within a column but i don't want to include the empty cells and cells with zero, also, i want my formula to skip the hidden cells within the range, i am using this formula. =AVERAGEIF(OFFSET(F1,COUNT(F2:F59),0,-26,1),">=1") Thank you

  2. Hi Leila,

    Really enjoyed this very informative & well presented video. I will definitely be coming back when I need expert help.

    I have one question though, at 10 mins 46 secs in your video, you explain how to obtain an average using a drop-down list to select a month for data held in columns, is it possible to do the same for data held in rows? If it is, how what is the formula? I have several scenarios but to no avail.

  3. You are brilliant and an awesome teacher !! Love your videos, my understanding of Excel is vastly superior thanks to you

  4. This function looked very scary when I recently attended an Excel webinar of my professional body, however, it seems simpler now.
    Thank you prof!

  5. I'm trying to use this for a matrix operation but i get #N/A for each empty cell, that gives me error later on, is there an other way to do it?

  6. I have different z-level column. The difference in z-level column is constant and it is decreasing by “-1”, However, the numbers of value for each z-level is not same. i.e if z-level column having “ 0” value is 5 and “-1” value has 6 cell and it goes randomly.

    I have a radius-value column. For z-level of “0”, I want to select a radius value from z-level of “-1” (and for z-level of -1 a will select a radius value from z-level of -2 and will put in the last column).

    Although I have done this process by using index and match formula, but there is still one problem, as I have to change the value of radius-value column when I want to go to next z-level, which is very time consuming and chances of error is also enhanced.

  7. Excellent video as always! I do have a question about the horizontal example. After understanding the vertical formula, I attempted to do the horizontal formula on my own based on what I learned on the vertical example before watching your instruction (on the horizontal). I was able to return the same results as yours, which includes the adding of additional month's data in new future months and changing existing values. Every average dynamically returned the same value as your formula. However, when I went to check the syntax of my formula against yours I saw that you started the offset reference in A17 vs B17 (where I started), which I thought was inconsequential so I changed my formula to start in A17 instead. After updating my formula's offset reference to A17, the 6 month range failed to work as before and I don't understand why. Can you help please? Thanks! This was my original formula
    =AVERAGE(OFFSET(B17,0,COUNTA(B17:L17)-6,1,6))

  8. I am struggling with this video for some reason. Others have been great. My difficulty is that although I believe that I have diligently followed your instruction, my array is not dynamic. It is dynamic if I copy and paste the formula into cells below the one containing the original formula. All of those cells are then dynamic. I wonder what I have missed in order for this command (average(offset(count))) work for me.

  9. Question: where some cases have 12-row data so last 6 months is possible and on one case only 4-month data is available and we would like to have sum of that 4 months – Can this be solved with a single dynamic formula for add rows for last 6 months and sum of last less then 6 values

  10. What a pleasant teacher! Your demeanor is such that you make it fun to learn from a pro. I'm so glad you took the time to make these videos. They helped me tremendously with my new job. Thank you!!

LEAVE A REPLY

Please enter your comment!
Please enter your name here