Cumulative sum from the bottom row

[juju]

Hello - In excel, I have a column, E, with values. I'd like to find the sum of values from the last non blank row (summing upwards), where the sum is the closest to a predefined defined value, say 2,500. The values in row E, start at row 6, from the top. The column might also have blanks and non numeric values. I'd also like to return the range for the cells with whose sum from the last value upwards matches my predefined number.

I am doing this in excel online only so array formulas wont work.

ps: I haven't been here in years, however it is interesting to note that using chatgpt, I couldn't come up with an answer for this! I remembered the excellent help I have received here in years past and here I am again! Great resource!

 

[juju]

Anyone ?

 

[Asbestos_Jen]

Can you attach a sample of your data using the XL2BB tool? I can't visualize your request from what you described.

 

[juju]

@Asbestos_Jen Here it is:

Col 1	Col 2	Col 3
2,623		5,979
2,094		1,266
705		2,669
4,165		3,084
4,918		9,549
736		4,952
981		5,905
3,156		9,192
3,757		936
3,508		7,991
4,038		6,503
3,798		1,201
3,171		753
3,402		1,890
3,508		5,244
4,985		9,246

I'd like to find out two things:

1. For each row value in col 3, starting from the same row in col 1 and summing upwards, which summation value is closest to the row value in col 3? For example, for the value 9,246 in col 3, it will be 4,985 + 3,508 .
2. What is the range of cells in col 1 for the question above? I will use that range in col 2 for another calculation.

hope this helps! FYI - Col 1 sometimes have blank cell values or "-"

 

[Asbestos_Jen]

I can't see a way to do this with formulae. You might need a VB solution, but I don't think that works with excel online, and I'm not equipped to help with that. Good luck!

 

[awoohaw]

@juju , would you mind sharing your overall objective? What is the 2nd step you're referring to? Maybe there is another way one of the forum members can propose to help you address your problem?