Can I used a nested IF function here?

[vinvinvin123]

Hello!

Here is my formula..

=IF($A$2>DATE(2017,MONTH(L$3&"/1")+1,1),0,($D78-L76+L77))

A2 = today's date
Column D = stock
L76 = monthly forecast
L77 = deliveries due in month
L78 contains the above formula & as is referred to as the 'availability'
So basically if the month is in the past the availability figure can be 0 but if it is in the future then (stock - forecast + deliveries) = availability

This falls down & I get negative availability if there is a delivery due in the previous month but the stock figure is still 0 as it hasn't arrived yet.

For example:

August: stock= 0, forecast = 0, deliveries= 30,000 therefore availability = 30,000.
September: stock=0 (still 0 as this is the present day figure, the delivery is in the future), forecast = 5,000, deliveries =0. Using my current formula I get availability = -5,000 but it should be 25,000

So I need a formula that says:

(stock - forecast + deliveries) = availability BUT if the previous month Availability > 0 THEN (previous months availability - forecast + deliveries) = availability

Sorry if that makes no sense.. please post questions & I will try to clarify if so.

I am still trying to get the hang of explaining exactly what I am trying to do!

 

[vinvinvin123]

Sorry to bump my own thread but does anyone have any suggestion?

 

[inactiveuserps07]

@vinvinvin123, the simple answer to your question is "Yes, you can use a nested IF."

In general terms:

=IF($A$2>DATE(2017,MONTH(L$3&"/1")+1,1),0,(IF(previous month's total > 0, previous month's total, stock) -L76+L77))

In order to go much beyond that, even with all you posted, you'd really have to post actual sample data. Remember: you know what you're looking at, but we don't. And if it's that hard to explain it when you get to look at it ... imagine how it is from "out here" where we can't see the sheet.

For instance, when you refer to "the previous month," while I may understand you in concept, I have no idea what the setup of your sheet is, where the information for the previous month (or present month, or any month) resides in your sheet, what's in L3, etc.

If my tip above doesn't get you where you need to go with it, post some data, including column letters and row numbers, and I'm confident I or someone else here can help you.

 

[vinvinvin123]

Hi Erik,

Thanks for your response.

I used your advice & the correct formula was:

=IF($A$2>DATE(2017,MONTH(L$3&"/1")+1,1),0,(IF(K78>0,(K78-L76+L77),($D78-L76+L77))))

Thanks for your advice !