Perform calculation using variable percentage based on other cell value

Please see attached, thank you.

Dear All,

May I request for your kind assistance please in translating the below condition to a working formula for excel?
Parameters:

2010 actual sales (represented by x)
2011 forecast (represented by y)

1st condition
If AQ57="New" then grow AQ57 by 6%, if AQ57="Same" then grow AQ57 by 3% if AQ57="blank" then grow AQ57 by 4%
(1+0.04+IF(AQ57="New",0.06,IF(AQ57="Same",0.03,0)))*AB57

2nd condition (help needed)
If AQ57 is lower than 200,000 then get the average of W:AH then divide by 2 so that AQ57 will alway be equal to 200,000 or higher than 200,00

Hope someone can help me how to include the 2nd condition formula in the equation.

Thanks very much everyone.

Respectfully,

Francis[/QUOTE]

Hi Sir,

Thank you for your reply and assistance to elaborate on my request I am attaching a sample worksheet for reference.

Based on the sheet 2010 will grow by 3% if marketing drive row remains "blank", while if the word same is placed it will grow by 6% and lastly if the word new is placed it will grow by 10% (formula 1).

What I actually want to add in the equation is that I want the forecast to be higher than 200,000. If "formula 1" (which is the one on top) is equal to less than 200,000 then 2011 forecast will get the minimum by monthly forecast of 200,000. And if 2011 forecast is higher by more than 50% growth from the 2010 average then 2011 forecast will equal to half of the actual forecast. (i.e 2010=100 then I will input "new" then 2011=110 but since 110 is less than 200,000 then automatically 2011 will take 200,000 as minimum order), but if 2011 forecast is higher than the average 2011 sales by more than 50% growth then get half of the 2011 forecast and use as target. (i.e. 2010=90 then 2011 forecast is 500 but 2010 average sales is only 200000, then automatically 2011 forecast will be at max 0f 250.(500 forecast/2=250)

Hope this helps and thank you for your response.

Regards,

Francis

Hi Sir 6StringJazzer,

The logic in what I'm trying to figure is that the quota per branch should be at a minimum of 200,000 and since I am unaware what brought about the swings in number I want to take the average as basis of what I can consider as "Norm" but still project a max of 10% growth per month, and in the event that the projection is more than 50% higher than the average I want to take half of the projection as my forecast number.

If I may request for one more parameter to be added in the equation please?

Third Parameter:
The last part of the equation you sent if I'm not mistaken was "if the forecast is greater than 50% from the forecast then the forecast will take 50% only of the forecasted number. (i.e. forecast=100,000 while average sales is 25,000 then forecast number is equal to 50,000) My request is if I may ask you again that in the event that the forecast is lower than 200,0000 after considering the growth of more than 50% in the average can we have a default back to 200,000?. (i.e.forecast=100,000 while average sales is 25,000 then forecast number is equal to 50,000 but if less than 200,000 then forecast is 200,000)

I really am dumb in all this, just trying my best to run the business with a more scientific approach, I really appreciate your help and hope you can extend your help again.

Thank you very very much and God bless you.

Respectfully,

Francis

Originally Posted by 6StringJazzer

Here is the formula you would put AL6, fill to the right and then down to copy.

Please Login or Register  to view this content.

From a business standpoint I don't understand the reasoning, but hopefully this resolves your Excel question.

Sir 6StringJazzer,

You have been a big help and a wonderful blessing, thank you very much!

Respects,

Francis

Originally Posted by 6StringJazzer

Here is the revised formula

Please Login or Register  to view this content.

It simply takes the higher of 200,000 or the final result.