+ Reply to Thread
Results 1 to 4 of 4

Filling blank values in a non-linear series with linear values with a formula

  1. #1
    Registered User
    Join Date
    10-29-2014
    Location
    70816
    MS-Off Ver
    2011
    Posts
    2

    Filling blank values in a non-linear series with linear values with a formula

    I am trying to fill in some null values in my data with a linear step between 2 known values. In the example between row 5 and 10, there is 4 blank cells and a total difference of 2 (9-7), so each cell in that range would increase by .4 resulting in a linear step from 7 to 9. I can not figure out a formula to put into all the blank cells to generate values. I need a certified genius on this one lol

    Current Data Set:
    3
    4

    6
    7




    9





    23




    45




    67


    Answer Set:
    3
    4
    5
    6
    7
    7.4
    7.8
    8.2
    8.6
    9
    11.33333333
    13.66666667
    16
    18.33333333
    20.66666667
    23
    27.4
    31.8
    36.2
    40.6
    45
    49.4
    53.8
    58.2
    62.6
    67
    Attached Files Attached Files
    Last edited by hendo1416; 01-04-2020 at 12:02 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Filling blank values in a non-linear series with linear values with a formula

    Hi Hendo, try this into B2 and copy down:


    Please Login or Register  to view this content.

    Edit:- Asked unnecessary questions plus a typo.
    Edit Again: Actually, one question was relevant, as my formula will only work if numbers are in sequential order like in your sample.
    Last edited by Beamernsw; 01-03-2020 at 11:41 PM.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Filling blank values in a non-linear series with linear values with a formula

    Please try at B2
    =IF(A2,A2,B1+(INDEX(A3:A$28,MATCH(1,INDEX(--(A3:A26<>""),),))-LOOKUP(9^9,A$1:A1))/(MATCH(1,INDEX(--(A3:A$28<>""),),)+ROW()-MATCH(9^9,A$1:A1)))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-29-2014
    Location
    70816
    MS-Off Ver
    2011
    Posts
    2

    Re: Filling blank values in a non-linear series with linear values with a formula

    awesome. this place is the best! both formulas worked but the first was more versatile and could be applied to any set. thanks again everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fill in blank cells with linear values with linear percentage increases
    By jstanley41 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2018, 03:31 PM
  2. [SOLVED] Linear Interpolation of table values
    By tsmuts in forum Excel General
    Replies: 9
    Last Post: 12-27-2017, 03:57 PM
  3. [SOLVED] Linear Values Dissolve
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2017, 05:55 PM
  4. Solver: How to maximize a mark average (non-linear problem) in a linear way?
    By Sunday4th in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-07-2017, 07:42 AM
  5. [SOLVED] Fill in between cells with non linear values
    By Jamesera27 in forum Excel General
    Replies: 6
    Last Post: 10-24-2015, 07:32 AM
  6. Replies: 2
    Last Post: 01-15-2006, 05:10 PM
  7. Can you interpolate a non-linear set of values?
    By Piffas in forum Excel General
    Replies: 2
    Last Post: 11-18-2005, 06:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1