Calculating a running difference then returning last non-negative date

[emdee]

Hi all,

I have a spreadsheet with three column values:
Column A is YYYYWK
Column B is sales
Column C is stock

For any given current week (i.e. today would be week 1), I want to be able to take current week ending inventory and subtract sales, until I get to a negative value, then return the column value for the last week that had a positive cumulative value. My goal is to see how many weeks I have to sell if I didn't replenish stock

[djapigo]

Try this... =INT(C1/B1)

This should give you the number of weeks before negative inventory.

[djapigo]

Try this... =INT(C1/B1)

This should give you the number of weeks before negative inventory.

[emdee]

The sales values vary per week, and the week end inventory value also varies per week, so this would not work.

[djapigo]

B1 and C1 are the cells that you want to enter the sales values and week end inventory values, change the cell reference if needed

[djapigo]

Can you send a sample spreadsheet?

Click on Go Advanced and look for a paperclip...

re-post: please give some sample problems and the results that you want to see...

Is this what you have in mind?

[emdee]

I'd want to find the week for today(), then take the stock for that row and subtract sales starting at same row, and continue down the sales column until the value is negative, then return the week prior to the week it became negative.