find anywhere in A and B anything from C

    Re: find anywhere in A and B anything from C


    I have amended this formula in D column a lil bit but I dont think it is good :


    =(GrpSize-SUMPRODUCT((HOLfrom>=F5)*(HOLtill<=G5)))*CapPerHead

    yours was:


    =(GrpSize-SUMPRODUCT((HOLfrom<=F5)*(HOLtill>=F5)))*CapPerHead


    Which one is correct??


    Because with your formula I dont get any results when date for holidays is entered(I guess it was a typo on you side)


    But with my amended formula I get results except they are not quite correct


    Can you check this out and let me know
    thnx
    Saban

    Re: find anywhere in A and B anything from C


    my original formula was:
    =(GrpSize-SUMPRODUCT((HOLfrom<=F5)*(HOLtill>=F5)))*CapPerHead
    This checks on "datum" only, i.e. the group's capacitiy for this job is calculated based on the nbr of people available at "datum"


    the following formula calculates the groups's capacity based on the lowest nbr of people available from "datum" till day befor "rok":
    =(GrpSize-SUMPRODUCT((HOLfrom<G5)*(HOLtill>=F5)))*CapPerHead


    Your ammended formula only "works" when start and end of holiday is between "datum" and "rok", which e.g. means that an absence starting befor "datum" is not considered at all.


    So I guess one of the two above will be what you want.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    But what is bothering me is that if one is on vacation for one day than it deduct workrate for 6 but even if translator is away for one day and the next day comes back it will consider as he didnt return the workarate would still be 42 instead of 48


    What do you think if I would divide 6/day difference(how many days is document being translated) and the workrate would be deducted for only 2,3 or 4... pages not always just 6


    Thnx

    Re: find anywhere in A and B anything from C


    =(GrpSize-SUMPRODUCT((HOLfrom<=G5)*(HOLtill>=F5))/NETWORKDAYS(F5;G5))*CapPerHead


    Do you think this could work like i had in mind?(as described above)


    and do you have any idea how can I anmend this formula


    =(GrpSize-SUMPRODUCT((HOLfrom<=G2)*(HOLtill>=F2))/NETWORKDAYS(F2;G2)*NETWORKDAYS(AA2;AB2))*CapPerHead


    so that this part NETWORKDAYS(AA2;AB2)) will refer to holfrom and holtill on another sheet NETWORKDAYS(Sheet2! HOLfrom;HOLtill) or something like that

    Re: find anywhere in A and B anything from C


    there are all kinds of ways you could try to calculate the work capacity. However I do not see any way to come to satisfying results. Meaning each possibility will have its drawbacks one way or the other. What you would need is a day by day approach in the actual start-end-calculation, looking at each end every day how many translators are available, skipping weekends of course and public holidays. With standard xl-functions I don't think this is possible.
    I guess we have reached the point to which I draw your attention in the very beginning of your task in the other thread.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    You mean like excel is not apropriate tool anymore the things are getting to complexed


    this kinda gave me right results


    =(GrpSize-SUMPRODUCT((HOLfrom<=G2)*(HOLtill>=F2))/NETWORKDAYS(F2;G2)*NETWORKDAYS(AA3;AB3))*CapPerHead


    AA3=HOLfrom
    AB3=HOLtill


    It is like when translator is absent for whole period of translation of one document then the workrate should be deducted by 6 else should be deducted by workrate(6)/networkdays(date : deadline)


    Or something like that


    Thnx for all your help

    Re: find anywhere in A and B anything from C


    yes, basic excel functions are not the right tool for this. I guess it could be handled with VBA.
    Still, you can use what you have got as long as you are aware of the fact that you will not necessairuly get 100% correct results.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    Hi Saban
    in the attached file have a look at sheet2, Rows 30 and 31 .
    Col A = size of a group, e.g. the 8 from previous posts
    Col B = the number of pages one translator can manage per pay
    ColC = beginning day, e.g. "datum"
    ColD = enddate, e.g. "rok"
    in F31 you fin a user defined function that hopefull does what you need to determin a groups capacity in a certain time span.


    =grpnetcap(A31;B31;C31;D31;PubHol;Absences)


    mind pubHol must be a single column range e.g. F2:F15, Absences a double column range e.g. G3:H24


    When i get time to do so, I will extend this to calculate the End-Date based on the same criteria

    Re: find anywhere in A and B anything from C


    what is this named range grpnetcap I cant find this in your example
    What are these functions written in VBA what do they do ??(do i need to use them)


    thnx man really apreciate this
    stay cool


    Saban

    Re: find anywhere in A and B anything from C


    see new file
    grpnetcap is not a named range but a user defined function. It calculates the group's capacity between two dates considering weekends, hollidays, absences.


    the new function GrpNetDate calculates the end date based on start date, number of pages etc.


    So to answer your question about the functions: yes you need them. the two mentioned above to use them in your worksheet, the other two are used by the former two functions.

    Re: find anywhere in A and B anything from C


    Sorry for my stupidity but I dont quite get it how to use your new example is this end date (with new formula) new end date and should be placed into column Q instead of old date or what??

    Re: find anywhere in A and B anything from C


    in the attched the new functions are used in ColH and in ColN,O,P. There you can see how they are used. Mind that group size is a named range pointing to one cell on sheet2. You could also do the same with pages per head if that is not changing from job to job.

    Re: find anywhere in A and B anything from C


    Does this example deducts criteria if translator is on vacation ??
    I cant figure out what it does


    Where do I write holidays in Absence named range or what?
    I dont get this quite well could you explain it a little bit more
    Because if I will try to figure this out it will take me a week :)


    Just kidding but I would very like to have a detail explanation when you have some time


    thnx again

    Re: find anywhere in A and B anything from C


    ok, let's start with sheet2:
    Cell D3 which also is a named range called GrpSize holds the information of how many translators are in a group


    Cells F2:F16 (named range PubHol) shows public hollidays where the office is closed such as e.g. xmas, good friday or whatever there may be.


    Cells G3:I25 (named ranges HOLfrom and HOLtill) hold absences of translators (usually there vacation). The name in Col I is optional as it is not needed for calculation.



    on sheet 1:
    col A and B are infos you know.
    col C shows the number of pages belonging to this order.
    col D the number of pages a single translator can translate per day.
    col E your info
    col F when order is dispatched
    col G deadline when translation mus be finished. According to one of you previous posts this day is reserved for reviewing. See also col J
    col H here the groups capacity from datum till rok is calculated. You may want to change the reference to colG to refer to col J. The calculation considers goup size, absences, holidays, weekends, pages per head per day.
    Cil I you know
    col J is the date when translation must be completed. This is one day before rok in order for rok to be available for reviewing.
    col K adds the number of pages of one lines order to any possible remaining capacity from order above.
    col L day when translation starts for this order. It is either the end date of previous order if there is remaining capacity on that day, or the day after the end of previous one but never befor dispatch day. You might remove the two max statements referring to col F. see col M
    col M takes the higher value of col L and F. This allows to remove the max calc in col L
    col N is the calculated enddate when translation is finished, based on startdate, group size, pages poer day per head, hollidays, weekends, absences.
    col O calculates how many pages are translated on the last day.
    col P calculates the remaining capacity on the end date. This to determin if next job starts on end date or the next day.
    Mind cells N4:P4 (not 3) must remain empty


    Hope that helps

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    thnx for your explanation it helped me a lot
    I just have one problem:
    I have sheet1 and sheet2-for vacation etc
    now I wanna do 4 same sheets(4 groups) where every sheet has its own sheet for vacation except the name of the sheet will be diferent
    so the sheet3 will refer to sheet4 for vacation and so on
    but when I make a copy of these two sheets all new sheets are refering to same sheet for vacations


    why


    I that new example of yours is it possible to add two columns for dates when translator is missing just half a day so if he is missing just half a day then i need to deduct criteria for 3


    thnx again

    Re: find anywhere in A and B anything from C


    since all references to the vacation sheet are via named ranges you have to give the different ranges in the different sheets different name. e.g. instead of HOLfrom you will need HOLfrom1, HOLfrom2 etc and then ammend the formulae accordingly (with find - replace).
    You may, however, have all absences on the same sheet with different range names. Different sheets is not a must here. I would even suggest to keep all on one sheet. Public Holidays will be the same for all anyway. The rest may differ but still, all such info on the same sheet seems easier to maintain and overview.


    Halfday absence could be included, yes. However this would mean that the self made functions have to be adapted to evaluate an additional range. E.G. next to from/till an indicator if the absence is full or partial could be evaluated.
    If this is important, either give it a try yourself or let me know and I will take care of it.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: find anywhere in A and B anything from C


    Could you please insert that for half day of vacation cause I dont have a clue how to deal with that user defined function


    And about that named ranges is something so strange I did make another named range and amended all the formulas with (find replace) but it is still refering to old sheet or to old range tried it both to have just new range on same sheet and also tried to make a new sheet with new named ranges and still the same I dont get this (It is still the same : when I write dates for Skupina2 in vacation sheet from Skupina2 that is dopusti2 it does nothing and if I write it in vacation sheet from Skupina1 then it is ok - so strange)- Ok dont bother with that I figured it out


    but this I would still need:
    I must say U did a splendid job !!!
    All that would be very nice now is with that vacation and workrate:
    1. figure out how many days actually vacations represent in range from Date till deadline
    2. Then figure out the number of weekdays between date and deadline
    3. divide : 6/nr.of weekdays between date and rok = y
    4. multiply y with nr of days in 1 point(how many days actually vacations represent in range from Date till deadline)
    5 deduct this result from workrate


    So I guess this would be pretty much correct information for decreasing workrate? What do oyu think


    and thnx man for everything

    Re: find anywhere in A and B anything from C


    I shall include the half day absence thing tomorrow and upload a new version with absences for the various groups. How many groups are there? And are all groups of the same size?


    Your suggestion is correct. However this calculation is not needed anymore since the self made function takes care of all that allready.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login