My nemesis - IF statements that need to do a few things...

[mickyd67]

In this scenario I have

Cell I6: the total amount of an item shipped year to date

Cell J6: the forecast amount for the year

Cell K6: where you can enter an adjustment to your forecast in Cell J6 (i.e. + or -)

Cell L6: the difference between Cell J6 & Cell K6

= = =

In Cell M6 I want it to return a status:

So far I have

=IF(L6<0,"Demand Reduction",IF(L6>0,"Demand Increase","No Change"))

However - I also want the formula to look at Cell I6 and return an alert to say "Your Demand Cannot be Below Shipments YTD"

Not sure how I do that - can you help, please?

 

[Joe4]

However - I also want the formula to look at Cell I6 and return an alert to say "Your Demand Cannot be Below Shipments YTD"

I don't think you have clearly expressed where you want this to happen and when.
Are you trying to add it to the existing formula that you currently have, or do you want a new formula in a new cell?
If the same cell, what is the order of preference between this new condition and your existing ones?

I think it would probably be very helpful if you posted a few simple examples of your data, and show us your expected results in each case, being sure to represent each possible scenario.

 

[mickyd67]

Hi Joe - thanks for the response. Let me try and make it clearer with the below image:


To answer your question: "Are you trying to add it to the existing formula that you currently have, or do you want a new formula in a new cell?"

Ideally, yes - it would mean the formula in Column N looking at the change in demand in Column L to return a status, but also look to ensure the data in Column K isn't below Column I

Does that help?

 

[Joe4]

So, you want the column K/I comparison to be the predominant one, it sounds like.
Then you just need to add another level of nesting to your current nested IF formula, like this:

=IF(K6<I6,"Your Demand Cannot be Below Shipments YTD",IF(L6<0,"Demand Reduction",IF(L6>0,"Demand Increase","No Change")))

 

[mickyd67]

Absolutely perfect. Thank you again Joe4!

 

[Joe4]

You are welcome.
Glad I was able to help!