Search code examples
excelexcel-formulasplitreferenceoffset

How do I avoid Invalid Cell Reference when =OFFSET is referring to a cell filled by =TEXTSPLIT

I have an Excel file with a template page that uses the TEXTSPLIT function as follows.

=TEXTSPLIT(A3,";",,FALSE,,)

This is meant to take data from a CSV file and split it out in columns B to F. I want to take the data in column C for the rolling average using:

=AVERAGE(OFFSET(C4,0,0,-10,0)

however when I try to apply this I am getting an Invalid Cell Reference Error, if I click on the cell I am trying to reference

To start the process I take the formulas in my template where column B is filled down with =TEXTSPLIT for each row that the CSV file makes automatically along with my averaging calculations and fill across worksheets to the imported CSV data.

I have tried typing in the data into my Template sheet but I still get the same reference error for the rolling average.

Some example data, had to split it up weird sorry:

-2E-5;0;-0.0060090136;-0.0002997003;0


-1.98E-5;-0.0040444895;0.0040060091;-0.0002997003;-0.00010111224


-1.94E-5;-0.0040444895;-0.0020030045;-0.0002997003;0


-1.9E-5;0.0040444895;-0.0040060091;-0.0002997003;-0.00020222447


-1.88E-5;-0.0040444895;0.0020030045;0;-0.00040444895

The first value is an initial time stamp, then there are 4 channels of saved data. The only data I'm interested in, what I'm trying to extract is from the first channel.

Solution

  • To get the average of the first channel:

    =AVERAGE(BYROW(A1:A5,LAMBDA(r,--CHOOSECOLS(TEXTSPLIT(r,";"),2))))

    That splits the values on the ; and then chooses the second column and average the returned array.

    enter image description here