+ Reply to Thread
Results 1 to 10 of 10

Incramenting K value in small function

Hybrid View

  1. 10-01-2011, 08:22 PM #1
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Incramenting K value in small function

    Attached is coppy of the project.
    Objective: is to auto fill the Whos Who with each posistion with who will be working there, and at what time.

    Problem: I so not know how to get the formula to incrament (K) value on posistions when using the Small function. If I put a (K) value in it will not work if there is somone if there is a missing position.

    Situation: Not all positions will be filled every day with the maximum amount of workers. some times the K values used will be ,1,2 other days 1,2,3 and other days 1,2,3,4. If I have and assembler position filled with 3 people that day. odds are it 1 for lunch and 2 for dinner so in theory its K 1,3,4 because there is nothing to be filled into the 2 spot.
    Attached Files Attached Files
    Last edited by Nazerith; 10-03-2011 at 11:11 PM.
    Reply With QuoteRegister To Reply
  2. 10-01-2011, 08:55 PM #2
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Incramenting K value in small function

    Hi Nazerith, your requirements aren't very clear. What do you mean by increment K values? Do you mean that for "Estimated Out Time"? Or do you mean for values is column B? I think it may be simpler (maybe) for us here if you reworked you workbook and cut it down to two pages, say Monday_Before and Monday_After. That way you can fill in the fields manually, which will help us see how to fill the fields automatically. Otherwise there will be a lot of guessing on our parts which might deter people from helping.

    Regards:
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---
    Reply With QuoteRegister To Reply
  3. 10-01-2011, 09:26 PM #3
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Incramenting K value in small function

    on the Whos who page I want to fill Cells F18, F22, L18, L22 with the diffrent times on the data table to the right please only use Sunday other sheets are not formated i have some formulas in cells that have some correct data

    F18=T15 from that I need D18=R15
    F22=T17 from that I Need D22= R17
    L18=T29 From that I need I18= R29
    L22=T31 From that I need I22= R31

    this is what i need the formula to do as a end function in the Sunday scenario

    all these values will change positions ever day so it needs to be a look up function
    Reply With QuoteRegister To Reply
  4. 10-01-2011, 09:42 PM #4
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Incramenting K value in small function

    =IF(IF(ISERROR(INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),3))),"",INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),3)))<0.666665,"",IF(ISERROR(INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),4))),"",INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),4))))

    if the formula that does not work because it just repeats T29 the value in I18

    Basicly i need it to recognize how many assemblers work that day befor 4pm and after 4pm and then take that information and determin witch assembler slot is corret to put it in so It can auto fill in the name of the person at that time for that posistion
    Reply With QuoteRegister To Reply
  5. 10-01-2011, 09:59 PM #5
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Incramenting K value in small function

    Sorry Nazerith, I really don't know but I've sent a message to those that are wiser in the ways of science . Hopefully someone will be able to help.

    Good Luck!
    Reply With QuoteRegister To Reply
  6. 10-01-2011, 10:03 PM #6
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Incramenting K value in small function

    I hope I was a bit clearer, but I do thank you for your time, and any help that maybe provided.
    Last edited by Nazerith; 10-01-2011 at 10:30 PM.
    Reply With QuoteRegister To Reply
  7. 10-01-2011, 11:35 PM #7
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Incramenting K value in small function

    =IF(ISERROR(IF(AND(ISNUMBER(L18),ISNUMBER(F22)),INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),4)),INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),3)))),"",IF(AND(ISNUMBER(L18),ISNUMBER(F22)),INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),4)),INDEX(Time_in,SMALL(IF(Posistion=K22,ROW(Posistion)-ROW($S$10)+1),3))))


    with


    =OFFSET(IF(ISERROR(IF(AND(ISNUMBER(L19),ISNUMBER(F23)),INDEX(Time_in,SMALL(IF(Posistion=K23,ROW(Posistion)-ROW($S$10)+1),4)),INDEX(Time_in,SMALL(IF(Posistion=K23,ROW(Posistion)-ROW($S$10)+1),3)))),"",IF(AND(ISNUMBER(L19),ISNUMBER(F23)),INDEX(Time_in,SMALL(IF(Posistion=K23,ROW(Posistion)-ROW($S$10)+1),4)),INDEX(Time_in,SMALL(IF(Posistion=K23,ROW(Posistion)-ROW($S$10)+1),3)))),,-2)

    have solved the issue if anyone has anything that is more functional please let me know.

    the only know issue is if i have more than 4 of the same position in a day then this dosent work. I would like it to count every instance of a job that has been listed and put in the appropate K value needed for that slot.....
    Reply With QuoteRegister To Reply
  8. 10-03-2011, 08:20 AM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Incramenting K value in small function

    you can use COUNTIF for the K value, counting the "position" in column E as you copy down

    eg. replace K in your F18 formula with COUNTIF(E$18:E18,E18) and as you copy down it will incemement based on position count in column E.

    Also, not sure but maybe replacing formula in F18 with this will do?

    =IFERROR(INDEX(Time_in,SMALL(IF(Posistion=E18,IF(Time_in<0.666665,ROW(Time_in)-ROW($T$10)+1)),COUNTIF(E$18:E18,E18))),"")
    CSE confirmed and copied down.

    Then to get name in D18:

    =IFERROR(INDEX(Name,SMALL(IF(Posistion=E18,IF(Time_in<0.666665,ROW(Time_in)-ROW($T$10)+1)),COUNTIF(E$18:E18,E18))),"")

    CSE confirmed and copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  9. 10-03-2011, 11:11 PM #9
    Nazerith
    Nazerith is offline
    Registered User
    Join Date
    09-02-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Incramenting K value in small function

    that Works Perfect your the man!!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1