Re: Subtracting Given Values From Number

Hi Risto85

Assuming a layout similar to your example with:

- Headers in Row 1 starting in column A

- Stock in column B

- Forecast for April-December in columns C:K

then:

Number of months covered in N2:

=IF($B2>=SUM($C2:$K2),"OK",SUMPRODUCT(--(SUBTOTAL(9,OFFSET($C2,0,0,1,COLUMN($C2:$K2)-COLUMN($C2)+1))<=$B2)))

Out of stock month in M2:

=IF(N2="OK","OK",INDEX($C$1:$K$1,N2+1))

"OK" means you have enough stock for the all months forecasted.

HTH

lecxe[hr]*[/hr] **Auto Merged Post Until 24 Hrs Passes;**[dl]*[/dl]Hi again

Quote

Hi,

Thanks for your quick relpy. I seem to have some sort of problem getting this formula to work, do you have idea what could be wrong?

http://img214.imageshack.us/my.php?image=excelvg5.jpg

I've just tried the solution on a newly created workbook and it worked.

I'm sorry but I don't see what can be the problem.

In some contries the parameters of a function are separed by a semicolon ";" instead of by a comma as in the English version.

Using the formula in N2 I get the value 2 which seems correct. The total of the first 2 months is 2100, still covered by the stock. The total of the first 3 months is 6100, no longer covered by the stock.

Maybe someone else can try it and give feedback?