Forum Discussion

agh620's avatar
agh620
Copper Contributor
Jun 23, 2023

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: 

 

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: 

 

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. 

Sorry to say this but I don't have knowledge of powerBI and because of this i use flourish studio. 
are there any idea for this problem? 

my excel file(in csv format) for testing: https://drive.google.com/file/d/1EYnRotT7vahWygFp4Lk-BIfKACp3z-yF

BI & Data Analysis
Charting
excel
Formulas and Functions
Macros and VBA
power bi

4 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 23, 2023

    agh620 

     

    IMHO you have to explain how you calculate your suitable numbers. With a logic that's probably wrong I can reconcile some numbers (a variance of 0.5 in column H though):

     

     

    and following your calculation logic what would be the suitable numbers in the yellow cells?

     

    AND What version of Excel do you run + on which OS (Windows / Mac)?

    • agh620's avatar
      agh620
      Copper Contributor
      Jun 23, 2023
      If the number trend is upward : 42500 - 42000 = 500 one empty cell between them then 500/2 = 250
      If there is a blank between two cells and both cells are the same = fill all empty cells with same numbers. for example in your screenshot all yellow cells must fill with 48533
      If the number trend is downward : like cells between FK25 and FO25 then 463667 - 448284 = 15383 there are 3 empty cells between that then 15383/4 = 3845 and cells must fill like : 463667,459822,455977,452132,448284

Resources