Excel Magic Trick 735: Find Max Running Three Week Totals SUBTOTAL / OFFSET Array Formula
Loading...
Uploader Comments (ExcelIsFun)
All Comments (10)
-
...More from above:
For a 1 dimensional array (1 row by multiple columns, or, 1 column by multiple rows), it usually will not matter (like in this example or with the LARGE function). But as soon as you are dealing with a 2 dimensional arrays (multiple rows by multiple columns), then it may cause trouble (like with multiplying arrays)
-
The cols OFFSET argument can handle the array of numbers regardless of whether they are in columns (comma) or rows (semi-colons). If it did matter that we put only an array across columns (commas), we could use the TRANSPOSE function like this:
TRANSPOSE(ROW(INDIRECT("1:"&CO
LUMNS(A4:H4)-$E$1+1))) more below...
-
Thinks a Zillion times Mike this A very helpful formula......
specially with my "Moving Average" formulas.....
>>>>>>>> i have a Q...
i noticed that [Time 412] you used a comma ","
which is move to next column..............
but [Time 9:42] the second formula - with the row function - this changed to samicomma ";" which is move next row............
What is the effect of this?!
-
Thanks, planiolro!
The reason that I show how "Excel is Thinking" is becasue if I did not, I would not understand it myself. Plus, the Really Fun Part of Excel is the "Thinking"!
-
What I really appreciate about your clips is the detailed explanations about how “Excel” is thinking.
6:57
Excel Magic Trick 736: Formula & Conditional Formatting To Show Waste From Cutting Rebarby ExcelIsFun1,793 views- 2:16
Excel Magic Trick 751: Freeze Panes For Large Workbooks Freeze either a Row, Column, or Bothby ExcelIsFun1,702 views
- 2:12
Excel Magic Trick 716: Max of Absolute Values MAX & ABS functions Array Formulaby ExcelIsFun2,532 views
- 15:12
Excel Magic Trick 758: Extract Data With Two Criteria - 5 Formula Methodsby ExcelIsFun4,825 views
- 1:09:39
Excel Magic Trick 778: INDEX & MATCH Functions Beginning To Advanced (18 Examples)by ExcelIsFun20,371 views
- 1:36
How to use the OFFSET formulaby MHELP2U6,591 views
- 10:27
Mr Excel & excelisfun Trick 54: Approximate Lookup To Return Multiple Itemsby ExcelIsFun3,727 views
- 8:49
Excel Subtotal tool tutorialby TheITService1,845 views
- 5:34
Use Subtotal in Excel 2007.aviby Computergaga1,544 views
- 15:06
Excel Magic Trick 758.5: Extract Data With Four Criteria VLOOKUP & INDEX SUM, AND, OR Functionsby ExcelIsFun5,533 views
- 6:54
Consolidating Data from Multiple Excel Worksheets By Positionby DannyRocksExcels23,612 views
- 7:34
Excel Magic Trick 606: INDEX & MATCH Two Lookup Valuesby ExcelIsFun16,269 views
- 14:31
Excel Magic Trick 741: Avoid Errors & Blanks In Chart Using Formulas & Defined Name Dynamic Rangesby ExcelIsFun2,683 views
- 2:44
Excel 2007 functions: Sum, Count, Max, Min, Round, Rand.by luisjoseprofesor1,228 views
- 9:45
Excel Magic Trick 584: Dynamic Range for Periodic Data Dumps into Excel OFFSET & Defined Namesby ExcelIsFun6,159 views
- 23:31
Excel Magic Trick 703: Extract Records Multiple Criteria (AND OR logic) Filter, Formula, Adv. Filterby ExcelIsFun9,097 views
- 6:38
Excel Magic Trick 738: Goal Seek, PMT function for Loan Payment, Cash Flows and Balloon Paymentby ExcelIsFun2,755 views
- 4:22
Excel Magic Trick 770: Count Cells With Color Using Filter Feature or SUBTOTAL functionby ExcelIsFun5,650 views
- 4:08
Highlight Winning Lottery Numbers With Excel Conditional Formattingby contextures3,554 views
- 10:14
Excel Magic Trick 409: VLOOKUP MAX Value, Return More Than 1 Recordby ExcelIsFun7,535 views
Great video Mike, thanks for teaching us all Excel the easy way and slaying those Excel Dragons. "Good plug, right?"
krn14242 1 year ago
You are welcome! Thanks for the plug!
If it ever comes out, that is... I will believe it when I see it!
ExcelIsFun 1 year ago
Mike,
Excel is crazy
but you make it FUN!!!
33sameer 1 year ago
I am glad that it is crazy-fun!
ExcelIsFun 1 year ago