• 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.

on the basis of respected region, multiple flexible conditional formula...

A

Ajinkya

Member
Hi Friends,


I have Analysis sheet where data is occurring from the two different sheets (Sales Data and Import Data)

with the help of INDEX, MATCH and SUMPRODUCT formula.


now I need help to build formula for following condition in the same "Analysis sheet"


(excel file is uploaded, https://hotfile.com/dl/163495154/6c32ccd/Cycle_Time.xlsm.html )


E.g.


objective of the requirement,


In ROI (DEL) region wise model’s lots are received in shorter time duration,till the time previous lot is not billed completely.

hence the billing of next lot (15 May and 22 May) it should not considered from the respective region (highlighted yellow Cells)


So require help in the formula, as the yellow highlighted cells should be update by "0" ZERO,


because till date previous lot of Arena model of qnty. 2,000 has been not invoiced completely.


and 0nce first lot invoiced completely then only formula should consider second/another lot i.e.(15 and 22 May)


Thanks in advance :)
 
Hi Ajinkya


This time I am not able to understand your query clearly.


Probably, could you please explain it bit clearly/ put it in other way?


Kaushik
 
Hi, Ajinkya!


I agree with kaushik03, please elaborate a bit more.

Taking a blind shoot, did you mean that cells J5:J6 of sheet Analysis should be zero as there were 1188 units of Arena product in stock (1200-12)?... if so, why L5:L6 have non zero values if still 886 available (1188-302)?


Regards!
 
Ok fine Koushik, let me try to explain once again,


if at same place and material of same model is received more than one time, till the time first lot is not billed completely the billing of next lot should not considered from the respective place and for the respective model.


e.g

if model Arena's lot we have received in Delhi more that one time on

11 May- first lot of 2,000 models

15 May- second lot of 3,000 models

22 May - third lot of 135 models


now from the first material import date (11 May) to till date we are able to sale only 302 models. the figure 302 is coming with the help of formula and it is taking figure from the date of import to till date count of total sales qnty. from the Sales Data Sheet.


but formula is again taking sales qnty for second material lot, from date 15 May to till date i.e 300 qnty.

and same for third lot, formula is considering sales qnty from the 22 May to till date i.e 240 qnty.


here my requirement is, formula should not consider 300 and 240 sales qnty.

for second and third material received lot, because first lot's total received 2,000 models are not invoiced compleatly.


hope i'm able to brief in detials.


pls let me know if any doubts...other wise we can have verbal discussion on the same
 
Ok..


Let me summarize my understanding:


You mean to say, as of now you have sold 302 models out of 2000 models received for the first lot (of Arena Model) dated 11 May.For the next lot date (May 15), formula should not calculate the number 300 as the first lot models are not yet completely sold. Till the time it is not completely invoiced, the cell L5 should update as 0 (zero) instead of 300 figure.


So in a word, for the next lot date(here 15 May), you want to restrict the formula from calculating 'the sum of models sold' till the time your first lot models (of date 11 May) are not completely sold.


Am I correct Sir? Please confirm.


Kaushik
 
In case my understanding is correct, then you can do something like this at L5:

=IF(L4=Analysis!E4,SUMPRODUCT(('Sales Data'!$B$4:$B$5=D5)*('Sales Data'!$C$4:$C$5=F5)*('Sales Data'!D$3:pP$3>=B5)*('Sales Data'!D$3:pP$3<=$L$3)*'Sales Data'!$D$4:$PP$5),"First lot not invoiced completely")
 
Hello Koushik and SirJB7,


extremelly sorry i was compleately stucked in stupid thing, so couldnt able to update the feedback.


well, koushik you have understood correct the requrement, but if im using your formula it gives me wrong details.


hope following details can help lot..


in short i want, values should come in column J&L as per following.


COLUMN J COLUMN L

11-May-12 DEL ROI G9 Arena 2,000 12 302

15-May-12 DEL ROI G9 Arena 3,000 0 0

22-May-12 DEL ROI G9 Arena 135 0 0

16-Jun-12 HYD SW G9 Arena 3,000 2,200 2,700


if till date sale is happened more than first lot (means first lot got completly liquidated )then formula calculate like this, suppose till date sale is 2,100 units, values should appear in column J&L like ....


COLUMN J COLUMN L

11-May-12 DEL ROI G9 Arena 2,000 12 2,000

15-May-12 DEL ROI G9 Arena 3,000 60 100

22-May-12 DEL ROI G9 Arena 135 0 0

16-Jun-12 HYD SW G9 Arena 3,000 2,200 2,700


I know its look like critical, but.....
 
Ajinkya,


The formula that I entered above, it should give you "First lot not invoiced completely".


Are you getting some different output?


Kaushik
 
Yup Koushik,


your formula is giving me same like "First lot not invoiced completely"

for row no. 7 means for HYD import location whereas formula should not consider above remark for HYD location.


for reference,i have given exact required output in my above blog.

can we use VBA coding to get the output?
 
Hi Ajinkya,


I have tried something that might (apparently) change the look of your sheet but it will give the status of the lot. Have a look at this:


http://dl.dropbox.com/u/60644346/Ajinkya_Proposed%20Solution.xlsx


if i had been at your place i would have had-a-go with this approach, or else i have not understood your end requirement correctly. Am Waiting for feedback!!


Faseeh
 
Hi Faseeh ,


I think you should use LOOKUP ; it does the job of the INDEX , MATCH and other functions you have used , especially when you have the CUMULATIVE STOCK column.


Narayan
 
Hi Faseeh,


Sorry to say....NO dear,


I have been tried to get output as per your formula but fail to get it done as desired.


I think i fail to present my query properly in the forum :-(
 
Hi Ajinkya,


Am too very sorry for that, for i have, some what wasted ur time actually :( I thought that the objective of the report is to see which lot is the "Current Lot". The upload files shows at any given value of sales that which lot will be then the current lot.


Will you keep adding data to the "Analysis" sheet of your ur workbook? Can't we sager-gate tables for each product? Don't worry if not me then our excel ninjas will certainly fix your problem :p


@Narayan,


Sir, Ajinkya is not satisfied with the solution, so... I will certainly use it once the original postee is satisfied :)


Faseeh
 
Dear Faseeh,


Dont feel bad and we are not wasting our time dear , we are trying to share knowledge with each other.


well can we use following condition in the formula or in the Analysis sheet can we add seperate column....


1. If region and model matches (column D&F) and if sales from first material import date to till date (Column L) is less than first import material qnty then "Actual sale from the FIRST import to till date"

2. If region and model matches (column D&F) and if sales from first material import date to till date (Column L) is equal to first import material qnty then "Actual sale from the FIRST import to till date"

3. If region and model matches (column D&F) and if sales from first material import date to till date (Column L) is grater than first import material qnty then "Actual sale from SECOND import date to till date"


thanks
 
Hi Ajinkya,


Sorry for this much late reply. See this file:


http://dl.dropbox.com/u/60644346/Cycle%20Time.xlsm


Note: I have worked out for just one location. Go to Sheet Analysis(2), see whether working in right direction.


Regards,
 
Dear Faseeh,


I think you are very near to reach 100%.


Just try and put the value 7,000 in Sales Data sheet Cell CM4 (against 14-Jul)


and check the cell M8 in Analysis (2) sheet, it is occuring in HYD location i think cell M7 should calculate 7,302 (total sale from first day import to till date) instead of 5,135.


and i think cell M8 should calculate 2,700 (first date of import to till date,16-june to 14 july sale)
 
You must log in or register to reply here.
Back
Top