CODEWITHSUNDEEP
excelvbaexcel-formulainterpolationforecast
abo jarba
abo jarba

Reputation: 23

How to fill empty cells with a sequence of numbers?

I have been facing this problem for a long time. Sometimes i get excel files that only some of cells have value and i need to fill empty cells with sequence of numbers.
for example see following:
enter image description here In the example above, the first column contains the names and the first row contains the dates.
Take a look at row 31 to understand my problem. there are 2 empty cells before cell D31 and i must fill this cells with sequence of numbers. For example, the number 41500 is suitable for cell B31 and the number 41750 is suitable for cell C31. after cell D31, the number 42250 is suitable for cell E31 and the number 45266 is suitable for cell H31 and all empty cells must be filled with appropriate numbers.
I hope you understand what I mean because I cannot explain my problem better than this.
I can't use excel FORECAST feature because some rows like row 2 or 3 or 4 have high number of empty cells and if i use FORECAST feature i get following error:
enter image description here i want to use this date in flourish studio website to create chart bar race. flourish studio website have interpolate feature for fill blank/empty cells but i don't know why that feature not working! i need something like flourish studio website interpolate feature
are there any idea for this problem?
my excel file(in csv format) for testing: https://drive.google.com/file/d/1EYnRotT7vahWygFp4Lk-BIfKACp3z-yF

Upvotes: 0

Views: 694

Answers (1)

P.b
P.b

Reputation: 11588

I have my doubt on the idea behind the question itself, but we could use MAKEARRAY combined with TREND (FORECAST can't work with dates in past):

=LET(all,     'asli(1)'!A1:GP72,
     data,    DROP(all,1,1),
     years,   DROP(TAKE(all,1),,1),
     country, DROP(TAKE(all,,1),1),
HSTACK(
       VSTACK("",
              country),
       VSTACK(years,
              MAKEARRAY( ROWS(data), COLUMNS(data),
                 LAMBDA( r,          c,
                         IF(INDEX(data,r,c),
                            INDEX(data,r,c),
                            LET(y,TOROW(years/(INDEX(data,r,)>0),3),
                                d,TOROW(INDEX(data,r,)/(INDEX(data,r,)>0),3),
                            TREND(d,y,INDEX(years,,c)))))))))

enter image description here

LET first declares some names we can use for reference in the formula: all refers to the complete data set including the year header and country names. data drops the years and names and leaves the range containing the data. years is the header of the data (starting from column B). country is the list of country names from the data (starting from row 2).

The HSTACK/VSTACK part stacks the headers for including them in the outcome, but the filling of missing data-part starts at MAKEARRAY:

MAKEARRAY checks for the current row/column of the data (INDEX(data,r,c) if it contains data. If it does it will use that data, else y filters out the years where the current row's data contains a value and d filters the data for current row where it contains a value. These are used in TREND, where d are the known_ys, y are the known_xs and the year of that current column is the new_x : TREND(d,y,INDEX(years,,c))

I hope it helps with whatever you want to accomplish with it.

Edit: in case of Excel 2021 you don't have access to some of the functions used in the solution above. You could use the following in another tab in cell B2 (dragged down to B72):

=LET(rowdata,    'asli(1)'!B2:GP2,
     headerdata, 'asli(1)'!$B$1:$GP$1,
     n,          ISNUMBER(rowdata),
     x,          FILTER(headerdata,n),
     y,          FILTER(rowdata,n),
IF(n,
   rowdata,
   TREND(y,x,headerdata)))

This will only calculate the data, I excluded the headers for this, but you can use ='asli(1)'!B1:GP2 in B1 and ='asli(1)'!A2:A72 in A2.

And for even older Excel use this in a new tab in Cell B2 and drag down/right:

=IF(ISNUMBER('asli(1)'!B2),
    'asli(1)'!B2:GP2,
    TREND(
          INDEX('asli(1)'!2:2,,AGGREGATE(15,6,COLUMN('asli(1)'!B2:GP2)/ISNUMBER('asli(1)'!B2:GP2),ROW($A$1:INDEX(A:A,SUMPRODUCT(--(ISNUMBER('asli(1)'!B2:GP2))))))),
          INDEX('asli(1)'!$1:$1,,AGGREGATE(15,6,COLUMN('asli(1)'!B2:GP2)/ISNUMBER('asli(1)'!B2:GP2),ROW($A$1:INDEX(A:A,SUMPRODUCT(--(ISNUMBER('asli(1)'!B2:GP2))))))),
          'asli(1)'!B$1))

Upvotes: 0

Related Questions