Calculate sales forecast using multiple criteria

**franztupaz** · 04-01-2011, 08:05 PM

Hi Everyone,

Can anybody help me convert the following logic to a working formula please?

AL6=Grow D6 by 4% if W6 is "GDP", 6% if W6 is "Same" and 10% if W6 is "New",IF (Grow D6 by 4% if W6 is "GDP", 6% if W6 is "Same" and 10% if W6 is "New") is less then 200,000 then use R6 as 2011 forecast, then round numbers.

This means all Jan 2010 actual sales(D6) will either grow by 4%, or 6% and max of 10% (W6) and minimum forecast will be the average 2010 sales (R6).

I have attached the worksheet I am trying to use as reference, hope someone can help me.

Thank you.

Regards,

Francis

**MarvinP** · 04-01-2011, 08:32 PM

Hi Francis.

Try this one

Please Login or Register  to view this content.

also see it in the attache.

**Palmetto** · 04-02-2011, 06:48 AM

Cross posted at Ozgrid

**franztupaz** · 04-02-2011, 11:45 AM

Hi Marvin,

Big help thanks very much, before I close the thread may I ask for one last parameter to the equation you sent please?

The parameter is: if 2011 Jan forecast will equal to more than 50% or be greater than 50% of the average 2010 sales then 2011 Jan forecast will equal to average 2010 sales.

This means all Jan 2010 actual sales(D6) will either grow by 4%, or 6% and max of 10% (W6) and minimum forecast will be the average 2010 sales (R6), but if Jan 2011 forecast will be greater by 50% from the average 2010 sales (R6) then forecast will equal to (R6).

Thanks very much in advance.

Regards,

Francis