Finding previous number to sum

[GBCTeacher]

I am looking for a function that will find the last number in a row, then add it to another cell. The formula in K13 is =IF(ISBLANK(K12),NA(),IF(ISBLANK(J12),K12,J13+K12)) which works if there are no gaps in the data. In this example there is a gap, and I need the formula to search the row and find the last number to the left. In this case the last number is in H12. In my current formula I've tried various functions to replace J13, such as Lookup and Index, but I can't seem to get these to work. The #N/A is there to make the line chart not drop to 0, so I can't remove that. Sorry about the screenshot, but I tried to add the minisheet and my company blocks addins. Anything to point me in the right direction would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
Are you trying to find the last value in row 12 or 13?

 

[GBCTeacher]

Hi & welcome to MrExcel.
Are you trying to find the last value in row 12 or 13?

Row 13. I need the last number to the left. I need it to skip the #N/A's and look at the next value to the left.

 

[Fluff]

Ok, how about

=IF(ISBLANK(K12),NA(),IF(ISBLANK(J12),K12,LOOKUP(10^9,$E13:J13)+K12))

 

[GBCTeacher]

Ok, how about

=IF(ISBLANK(K12),NA(),IF(ISBLANK(J12),K12,LOOKUP(10^9,$E13:J13)+K12))

Ok, how about

=IF(ISBLANK(K12),NA(),IF(ISBLANK(J12),K12,LOOKUP(10^9,$E13:J13)+K12))

That didn't work. I still get the answer from K12 in cell K13, and it isn't adding in the 1,094,298 from cell H13..

 

[Fluff]

That's because J12 is empty. So what exactly are you trying to do?

 

[GBCTeacher]

That's because J12 is empty. So what exactly are you trying to do?

I am trying to accomplish two things here. One is to get the line chart to start at its value and stop without going back to zero. The second thing is to get the cumulative value to compute even when there are gaps in the data.

 

[Fluff]

That doesn't really tell me much, but maybe

=IF(ISBLANK(K12),NA(),IFNA(LOOKUP(10^9,$E13:J13),0)+K12)

 

[GBCTeacher]

That worked! You are awesome!

 

[Fluff]

You're welcome & thanks for the feedback.