Excel Formula for Calculating Net Values with Interpolation

Question

I'm working on an Excel sheet and struggling with a formula that I need help with. I hope someone here can guide me in the right direction!

My Goal: I want to calculate values in column H based on data in column G. The calculation needs to handle three different scenarios:

If G has a value: Simply use the difference between the current value and the previous non-blank value in column G.
If G is blank: Interpolate a value based on the difference between the last non-blank value above and the next non-blank value below, evenly distributing the difference across all blank cells in between.
If the cell above H is not a number: In such cases, I need the formula to take the value from the G column directly.

Currently, I'm using this formula in cell H6, which is adapted from another file:

=IF(ISBLANK(G6),
    IF(ISNUMBER(H5),
        IFERROR(
            (INDEX(G:G, MATCH(TRUE, INDEX(ISNUMBER(G7:G$100), 0), 0) + ROW()) - INDEX(G:G, MAX(IF(G$1:G5<>"", ROW(G$1:G5)))))
            / (MATCH(TRUE, INDEX(ISNUMBER(G7:G$100), 0), 0) + ROW() - MAX(IF(G$1:G5<>"", ROW(G$1:G5)))), 0),
        G5),
    G6 - IFERROR(INDEX(G:G, MAX(IF(G$1:G5<>"", ROW(G$1:G5)))), 0)
)

The Issue: This formula works for some scenarios but is not quite giving me the right results in every case. Specifically, I'm seeing errors when it comes to handling the interpolation correctly, especially when the next non-blank value is far down the column or if the cell above doesn't contain a number.

Mock Example Table: Here is a mock example of what my data looks like, to make it easier to understand:

Day	Water Add (Column G)	Net Water (Column H)
1	80	80
2	90	10
3		62
4		62
5		62
6		62
7	400	310
8		166.67
9		166.67
10	900	500
11	1100	200

What I Need: I need help modifying or correcting this formula so it consistently handles:

Differences between consecutive non-blank cells in column G.
Proper interpolation for blank cells.
Graceful fallback to the value in column G if the cell above in column H is not a number.

Any help would be greatly appreciated!

Thanks so much in advance for your time and expertise!

Best, barc

I tried using the formula mentioned above in cell H6. I expected it to handle all the scenarios consistently: calculating the difference for consecutive non-blank cells, interpolating when there are blank cells between two non-blank values, and falling back to the value in column G if the previous cell in column H was not a number. However, I'm seeing incorrect values, especially when it comes to interpolation, or when there are gaps between values that span multiple rows.

Hi, the formula in my answer is entered in the first cell (H2) and will spill the results. Does H need to have text entries in some cells? ("If the cell above H is not a number") — nkalvi
– nkalvi, Commented Nov 1, 2024 at 11:08

Mayukh Bhattacharya · Accepted Answer · 2024-10-31 16:22:59Z

2

Here is one way of achieving the desired output using One Single Dynamic Array Formula (Assuming there is no Excel Constraints as per the tags posted):

=LET(
     a, B2:B12,
     b, TOCOL(a,1),
     c, FILTER(A2:A12,a),
     ƒx, LAMBDA(α,TOCOL(α-VSTACK(0,α),3)),
     TOCOL(HSTACK(DROP(ƒx(b)/ƒx(c)/(ƒx(c)>=SEQUENCE(,MAX(ƒx(c)))),,1),ƒx(b)),3))

answered Oct 31, 2024 at 16:22

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rachel · Accepted Answer · 2024-11-01 09:03:57Z

Are you sure your desired results is not something like below?

That is, a running sum of "Net Water" would be the same as "Water Add"?

If so, then first number in "Net Water" column would be =B2. Then formula in C3 could be this:

=LET(
    water_add, B2:B12,
    days, SEQUENCE(ROWS(water_add)),
    non_blank_days, FILTER(days, water_add <> ""),
    prev_days, DROP(non_blank_days, -1, 0),
    current_days, DROP(non_blank_days, 1, 0),
    days_betweens, current_days - prev_days,
    net_water, (INDEX(water_add, current_days, 1) - INDEX(water_add, prev_days, 1)) / days_betweens,
    BYROW(DROP(days, 1, 0), LAMBDA(d, XLOOKUP(d, current_days, net_water, "", 1)))
)

The "interpolation" is done in this line:

(INDEX(water_add, current_days, 1) - INDEX(water_add, prev_days, 1)) / days_betweens

Collectives™ on Stack Overflow

Excel Formula for Calculating Net Values with Interpolation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related