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 are 11190 and then the space makes it 11200 and then 10825 would become 11210. Thanks for any help.

excel
Formulas and Functions

5 Replies

  • 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.

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

    Hello ajfbh,

     

    Can the number of empty cells between values vary? If so, is there a maximum?

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

      Hello PReagan,

      Bare with me as I am not the most well versed in excel; The number of empty cells does sometimes vary but never more than around 5 and that shouldn't change the next number's value. Still only being 10 more than the previous. 

      Thanks for you help.

Resources