Copyright Notice

The content in this file was created by Mynda Treacy from My Online Traini
Individual users are permitted to recreate the examples for personal practi
Recreating the examples for training or demonstration to others is not per

The workbook and any sheets within must be accompanied by the following

This sheet must remain in any file that uses this data and or these techniqu
Any uses of this workbook and/or data must include the above attribution.
m My Online Training Hub.
for personal practice only.
o others is not permitted, unless written consent is granted by Mynda Treacy.

ied by the following copyright notice: My Online Training Hub ©.

d or these techniques.
above attribution.
y Mynda Treacy.
TRIMRANGE Function
Current Method TRIMRANGE
{=_xlfn.xlookup(B7:B32,Q7:Q31,P7:P31,"")} {=_xlfn.xlookup(_xlfn.trimrange(G7:G32
Year Product Sales Category Year Product
2024 Socks 3,700.00 #NAME? 2024 Socks
2024 Shorts 13,300.00 #NAME? 2024 Shorts
2024 Handlebars 2,300.00 #NAME? 2024 Handlebars
2024 Jerseys 6,700.00 #NAME? 2024 Jerseys
2024 Road Bikes 3,500.00 #NAME? 2024 Road Bikes
2024 Mountain Bikes 3,100.00 #NAME? 2024 Mountain Bikes
2024 Pumps 700.00 #NAME? 2024 Pumps
2024 Tights 3,300.00 #NAME? 2024 Tights
2024 Helmets 8,300.00 #NAME? 2024 Helmets
2024 Tires and Tubes 8,700.00 #NAME? 2024 Tires and Tubes
2024 Locks 10,000.00 #NAME? 2024 Locks
2024 Bottom Brackets 500.00 #NAME? 2024 Bottom Brackets
2024 Vests 3,300.00 #NAME? 2024 Vests
2024 Pedals 800.00 #NAME? 2024 Pedals
2024 Gloves 13,300.00 #NAME? 2024 Gloves
2024 Bike Racks 300.00 #NAME? 2024 Bike Racks
#NAME? 2024 Saddles
#NAME? 2024 Brakes
#NAME? 2024 Wheels
#NAME? 2024 Touring Bikes
#NAME? 2024 Cargo Bike
#NAME? 2024 Bib-Shorts
#NAME? 2024 Lights
#NAME? 2024 Caps
#NAME? 2024 Chains
#NAME?
 read tutorial watch tutorial

Trim Range Dot Operator

n.trimrange(G7:G32,2),Q7:Q31,P7:P31)} {=_xlfn.xlookup(_xlfn._tro_trailing(L7:L32),Q7:Q31,P7:P31)}
Sales Category Year Product Sales Category
3,700.00 #NAME? 2024 Socks 3,700.00 #NAME?
13,300.00 2024 Shorts 13,300.00
2,300.00 2024 Handlebars 2,300.00
6,700.00 2024 Jerseys 6,700.00
3,500.00 2024 Road Bikes 3,500.00
3,100.00 2024 Mountain Bikes 3,100.00
700.00 2024 Pumps 700.00
3,300.00 2024 Tights 3,300.00
8,300.00 2024 Helmets 8,300.00
8,700.00 2024 Tires and Tubes 8,700.00
10,000.00 2024 Locks 10,000.00
500.00 2024 Bottom Brackets 500.00
3,300.00 2024 Vests 3,300.00
800.00 2024 Pedals 800.00
13,300.00 2024 Gloves 13,300.00
300.00 2024 Bike Racks 300.00
2,100.00 2024 Saddles 2,100.00
2,300.00 2024 Brakes 2,300.00
10,000.00 2024 Wheels 10,000.00
500.00 2024 Touring Bikes 500.00
3,200.00 2024 Cargo Bike 3,200.00
700.00 2024 Bib-Shorts 700.00
1,300.00 2024 Lights 1,300.00
500.00 2024 Caps 500.00
8,700.00 2024 Chains 8,700.00
31,P7:P31)}
Category Product
Accessories Bike Racks
Accessories Helmets
Accessories Lights
Accessories Locks
Accessories Pumps
Accessories Tires and Tubes
Bikes Cargo Bike
Bikes Mountain Bikes
Bikes Road Bikes
Bikes Touring Bikes
Clothing Bib-Shorts
Clothing Caps
Clothing Gloves
Clothing Jerseys
Clothing Shorts
Clothing Socks
Clothing Tights
Clothing Vests
Components Bottom Brackets
Components Brakes
Components Chains
Components Handlebars
Components Pedals
Components Saddles
Components Wheels
Dynamic Named Range Formulas
Dynamic Range with OFFSET Dynamic Range with INDEX
{=_xlfn.xlookup(OFFSET(B7,,,COUNTA(B7:B33)),Q7:Q31,P7:P31)} {=_xlfn.xlookup(G7:INDEX(G7

Year Product Sales Category Year

2024 Socks 3,700.00 #NAME? 2024
2024 Shorts 13,300.00 #NAME? 2024
2024 Handlebars 2,300.00 #NAME? 2024
2024 Jerseys 6,700.00 #NAME? 2024
2024 Road Bikes 3,500.00 #NAME? 2024
2024 Mountain Bikes 3,100.00 #NAME? 2024
2024 Pumps 700.00 #NAME? 2024
2024 Tights 3,300.00 #NAME? 2024
2024 Helmets 8,300.00 #NAME? 2024
2024 Tires and Tubes 8,700.00 #NAME? 2024
2024 Locks 10,000.00 #NAME? 2024
2024 Bottom Brackets 500.00 #NAME? 2024
2024 Vests 3,300.00 #NAME? 2024
2024 Pedals 800.00 #NAME? 2024
2024 Gloves 13,300.00 #NAME? 2024
2024 Bike Racks 300.00 #NAME? 2024
mulas read tutorial watch tutorial

ynamic Range with INDEX Dynamic Range with TOCOL

=_xlfn.xlookup(G7:INDEX(G7:G33,COUNTA(G7:G33)),Q7:Q31,P7:P31)} {=_xlfn.xlookup(_xlfn.tocol(L7:L33,1

Product Sales Category Year

Socks 3,700.00 #NAME? 2024
Shorts 13,300.00 #NAME? 2024
Handlebars 2,300.00 #NAME? 2024
Jerseys 6,700.00 #NAME? 2024
Road Bikes 3,500.00 #NAME? 2024
Mountain Bikes 3,100.00 #NAME? 2024
Pumps 700.00 #NAME? 2024
Tights 3,300.00 #NAME? 2024
Helmets 8,300.00 #NAME? 2024
Tires and Tubes 8,700.00 #NAME? 2024
Locks 10,000.00 #NAME? 2024
Bottom Brackets 500.00 #NAME? 2024
Vests 3,300.00 #NAME? 2024
Pedals 800.00 #NAME? 2024
Gloves 13,300.00 #NAME? 2024
Bike Racks 300.00 #NAME? 2024
ynamic Range with TOCOL
=_xlfn.xlookup(_xlfn.tocol(L7:L33,1),Q7:Q31,P7:P31)}

Product Sales Category Category

Socks 3,700.00 #NAME? Accessories
Shorts 13,300.00 #NAME? Accessories
Handlebars 2,300.00 #NAME? Accessories
Jerseys 6,700.00 #NAME? Accessories
Road Bikes 3,500.00 #NAME? Accessories
Mountain Bikes 3,100.00 #NAME? Accessories
Pumps 700.00 #NAME? Bikes
Tights 3,300.00 #NAME? Bikes
Helmets 8,300.00 #NAME? Bikes
Tires and Tubes 8,700.00 #NAME? Bikes
Locks 10,000.00 #NAME? Clothing
Bottom Brackets 500.00 #NAME? Clothing
Vests 3,300.00 #NAME? Clothing
Pedals 800.00 #NAME? Clothing
Gloves 13,300.00 #NAME? Clothing
Bike Racks 300.00 #NAME? Clothing
Clothing
Clothing
Components
Components
Components
Components
Components
Components
Components
Product
Bike Racks
Helmets
Lights
Locks
Pumps
Tires and Tubes
Cargo Bike
Mountain Bikes
Road Bikes
Touring Bikes
Bib-Shorts
Caps
Gloves
Jerseys
Shorts
Socks
Tights
Vests
Bottom Brackets
Brakes
Chains
Handlebars
Pedals
Saddles
Wheels
2024 Saddles 2,100.00
2024 Brakes 2,300.00
2024 Wheels 10,000.00
2024 Touring Bikes 500.00
2024 Cargo Bike 3,200.00
2024 Bib-Shorts 700.00
2024 Lights 1,300.00
2024 Caps 500.00
2024 Chains 8,700.00
LAMBDA Formulas read tutorial

Fixed Range
{=_xlfn.byrow(C7:E17,_xlfn.lambda(_xlpm.rowrng, AVERAGE(_xlpm.rowrng)))}

Student Name Physics Art Chemistry Average

Alice 64 64 88 #NAME?
Bob 70 58 72 #NAME?
Charlie 56 72 70 #NAME?
David 86 52 85 #NAME?
Eva 76 76 71 #NAME?
Fiona 69 74 67 #NAME?
George 82 54 71 #NAME?
Hannah 76 79 55 #NAME?
Ian 93 57 96 #NAME?
Jasmine 89 77 61 #NAME?
Mynda 61 85 73 #NAME?
utorial watch tutorial

Dynamic Range - allows more rows to be added to the table.

{=_xlfn.byrow(_xlfn.take(I7:K22,COUNTA(H7:H22)),_xlfn.lambda(_xlpm.rowrng, AVERAGE(_x

Student Name Physics Art Chemistry Average

Alice 64 64 88 #NAME?
Bob 70 58 72 #NAME?
Charlie 56 72 70 #NAME?
David 86 52 85 #NAME?
Eva 76 76 71 #NAME?
Fiona 69 74 67 #NAME?
George 82 54 71 #NAME?
Hannah 76 79 55 #NAME?
Ian 93 57 96 #NAME?
Jasmine 89 77 61 #NAME?
Mynda 61 85 73 #NAME?
Trim Range Dot Operator
{=_xlfn.byrow(_xlfn._tro_trailing(O7:Q19),_xlfn.lambda(_xlpm.rowrng,AVERAGE(_xlpm.rowrng)))}

Student Name Physics Art Chemistry Average

Alice 64 64 88 #NAME?
Bob 70 58 72
Charlie 56 72 70
David 86 52 85
Eva 76 76 71
Fiona 69 74 67
George 82 54 71
Hannah 76 79 55
Ian 93 57 96
Jasmine 89 77 61
Mynda 61 85 73
RAGE(_xlpm.rowrng)))}
Num 1 Num 2
9 7 #NAME? =SUM(_xlfn._tro_trailing(A:A))
1 1 #NAME? =SUM(_xlfn._tro_all(A:B))
1 6 #NAME? =SUM(_xlfn._tro_trailing(A:B))
4 3 #NAME? =SUM(A2:_xlfn._tro_trailing(B:B))
8 2
4 2
4 10
5 6
1 1
2 3

5
fn._tro_trailing(A:A)) 3D Ranges are not supported:
fn._tro_all(A:B)) #NAME?
fn._tro_trailing(A:B)) #NAME?
:_xlfn._tro_trailing(B:B))
{=_xlfn.vstack(_xlfn._tro_all($Sheet1.A:A),_xlfn._tro_all($Sheet2.A:A))}
=SUM(sheet1:_xlfn._tro_all($Sheet2.A:A))
Data Validation read tutorial watch tutorial

Category
Accessories Test #NAME?
Bikes
Clothing
Components
Test

Test2
atch tutorial

{=_xlfn._tro_trailing(A5:A13)}
More Resources
Tutorials
Excel Functions................................................................
Charting Blog Posts..........................................................
Excel Dashboard Blog Posts.............................................

Webinar Replays
Excel Dashboards & Power BI..........................................

Courses
Advanced Excel................................................................
Advanced Excel Formulas................................................
Power Query.....................................................................
PivotTable Quick Start......................................................
Xtreme PivotTables..........................................................
Power Pivot......................................................................
Excel Dashboards.............................................................
Power BI...........................................................................
Excel for Decision Making Under Uncertainty..................
Excel for Finance Professionals........................................
Excel Analysis ToolPak.....................................................
Excel for Customer Service Professionals........................
Excel for Operations Management...................................
Financial Modelling...........................................................
Microsoft Word Masterclass.............................................

Support
Excel Forum.....................................................................

Follow Us for more Tips & Tutorials

https://www.myonlinetraininghub.com/excel-functions
https://www.myonlinetraininghub.com/category/excel-charts
https://www.myonlinetraininghub.com/category/excel-dashboard

https://www.myonlinetraininghub.com/excel-webinars

https://www.myonlinetraininghub.com/excel-expert-upgrade
https://www.myonlinetraininghub.com/advanced-excel-formulas-course
https://www.myonlinetraininghub.com/excel-power-query-course
https://www.myonlinetraininghub.com/excel-pivottable-course-quick-start
https://www.myonlinetraininghub.com/excel-pivottable-course
https://www.myonlinetraininghub.com/power-pivot-course
https://www.myonlinetraininghub.com/excel-dashboard-course
https://www.myonlinetraininghub.com/power-bi-course
https://www.myonlinetraininghub.com/excel-for-decision-making-course
https://www.myonlinetraininghub.com/excel-for-finance-course
https://www.myonlinetraininghub.com/excel-analysis-toolpak-course
https://www.myonlinetraininghub.com/excel-for-customer-service-professionals
https://www.myonlinetraininghub.com/excel-operations-management-course
https://www.myonlinetraininghub.com/financial-modelling-course
https://www.myonlinetraininghub.com/microsoft-word-course

https://www.myonlinetraininghub.com/excel-forum