Forum Discussion

ajfbh's avatar
ajfbh
Copper Contributor
Jan 22, 2020

Excel Formula

I was wondering if anyone could help me with a formula. I'm trying to automatically fill in numbers in groups but every time there is an empty cell the number increase by 10 for example all the items...
excel
Formulas and Functions
PReagan's avatar
PReagan
Bronze Contributor
Jan 22, 2020

ajfbh 

 

Assuming your list starts at E1 and there are never more than 5 blank cells between entries, then cell F7 could be:

 

=IF(ISBLANK(E7),"",IF(ISBLANK(E6),OFFSET(F7,-COUNTA(E1:E6)+1,0)+10,F6))

 

Copy down as necessary. 

  • ajfbh's avatar
    ajfbh
    Copper Contributor
    Jan 24, 2020

    Hey PReagan 

    sorry for the long reply, I have tried to implement this formula but not sure If I am doing it correctly because I haven't really used that tool before, when I type in the formula  it just makes the top highlighted square 0. Furthermore the list starts at e3727 rather that e1. 

    Thanks for your help, any further advisement would be incredible.

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Jan 24, 2020

      ajfbh 

       

      Adjust the formula to the following and place in cell F3733:

      =IF(ISBLANK(E3733),"",IF(ISBLANK(E3732),OFFSET(F3733,-COUNTA(E3727:E3732)+1,0)+10,F3732))

       Please let me know if this still does not work.

Resources