• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for Target Achievement

N

Nandakumar

Member
Hi,

I request formula for target achievement Column D for target achievement on daily basis.
If target is achievement,then balance should be zero.

Attached excel sheet for your reference.

Please help.
 

Attachments

Hi:

If the daily Qty sold is compared to the target you can use the following formula
Code: 
=IF(INDEX($G$2:$G$4,MATCH(B2,$F$2:$F$4,0),1)=C2,0,"Not Achieved")

Thanks
 
Hi:
Use the following
Code: 
=IF(C2>=INDEX($G$2:$G$4,MATCH(B2,$F$2:$F$4,0),1),0,C2)
If the quantity sold is greater than or equal to target quantity it will give zero else the quantity sold.

Thanks
 
Dear Nebu,

If quantity sold is greater than the target quantity,it should be balance of target quantity.If there is no balance in target quantity,it should be zero as i mentioned in column D for row13 & row 14.

I hope you understand.

Thanks
 
Dear Nebu,

There is a small error in the formula.I need your help.
Attached excel sheet for your reference.In this sheet Row11,actually row AR11 should be zero (Target for A (TOTAL) is one.Actual is two put together.So actual should be one only.

Please help
 
Hi:

The formula I wrote was based on a fixed target as per your OP, now you have a dynamic target which change as per date which is a completely different scenario. I have tweaked the formula to accommodate this, you have to get your target data right for the formula to work. In your example you had given a target of 1 for Product A, in that case the formula will assume that the stock have been replenished hence it can use one more unit of Product A. If there is no additional unit available I guess you should keep the target as zero then the formula will return zero even if the quantity sold column have a number greater than zero. I hope this is what your business case is. Find the attached.

Thanks
 

Attachments

Dear Nebu,
I think i confused you.I am extremely sorry for that.what i did is i compared target (which is fixed for each quantity) but i compared in each line for actuals.It is similiar like FIFO (first in first out) method.I tried your latest formula what you shared also not working in my sheet.
Let say if target is one for Qty A,but actual are two,in first row it should be one (FIFO) and next row target should be zero.
I hope now you understand my query.
 
You must log in or register to reply here.
Back
Top