+ Reply to Thread
Results 1 to 15 of 15

need urgent help with VLookup or overtime excel formula.

  1. 01-06-2016, 04:28 AM #1
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    need urgent help with VLookup or overtime excel formula.

    =IF((L8:L334 ="OT1"),K9*1.5,IF(L8:L334="OT2"),K9*2,IF(L8:L334="N"),K9))

    I have tried above formula, that doesn't make sense to excel. What I am trying to do is

    If cell L8 to L334 contains a cell called OT1, then the previous cell (K something) is multiplied by 1.5. If cell L8 to L334 contains a cell called OT2, the previous cell (K some number) is multiplied by 2. If cell L8 to L334 contains a cell called N, that previous cell (K) exist as it is. Please see the attachment for what I mean in excel.

    Thank you so much in advance for excel experts.

    John
    Attached Images Attached Images
    Last edited by Tar Yar; 01-06-2016 at 07:07 AM.
    Reply With QuoteRegister To Reply
  2. 01-06-2016, 04:50 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    try

    =IF(L8="OT1",K9*1.5,IF(L8="OT2",K9*2,IF(L8="N",K9,"")))

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 01-06-2016, 06:16 AM #3
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    thanks a lot for quick help.

    I am also wandering how to add "and" symbol in excel if I want to add IF(L8= "N","A" AND "V", K9,IF(L8="U",0,))))
    A = Absense (sick leave)
    V= vocation (annual leave)
    then they are paid the same as they have leave entitlements.

    if it is U (Unpaid), then that cell should be zero etc.
    Reply With QuoteRegister To Reply
  4. 01-06-2016, 06:17 AM #4
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    thanks a lot for quick help.

    I am also wandering how to add "and" symbol in excel if I want to add IF(L8= "N","A" AND "V", K9,IF(L8="U",0,))))
    A = Absense (sick leave)
    V= vocation (annual leave)
    then they are paid the same as they have leave entitlements.

    if it is U (Unpaid), then that cell should be zero etc.
    Reply With QuoteRegister To Reply
  5. 01-06-2016, 06:26 AM #5
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    thanks a lot for quick help.

    I am also wandering how to add "and" symbol in excel if I want to add IF(L8= "N","A" AND "V", K9,IF(L8="U",0,))))
    A = Absense (sick leave)
    V= vocation (annual leave)
    then they are paid the same as they have leave entitlements.

    if it is U (Unpaid), then that cell should be zero etc.
    Reply With QuoteRegister To Reply
  6. 01-06-2016, 06:31 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    Try

    =IF(OR(L8="N",L8="A",L8="V"),K9,IF(L8="U",0,))
    Reply With QuoteRegister To Reply
  7. 01-06-2016, 06:45 AM #7
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    thanks a lot for quick help.

    I am also wandering how to add "and" symbol in excel if I want to add IF(L8= "N","A" AND "V", K9,IF(L8="U",0,))))
    A = Absense (sick leave)
    V= vocation (annual leave)
    then they are paid the same as they have leave entitlements.

    if it is U (Unpaid), then that cell should be zero etc.
    Reply With QuoteRegister To Reply
  8. 01-06-2016, 06:47 AM #8
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    Did you check post#7??? and please don't post repeated post.
    Reply With QuoteRegister To Reply
  9. 01-06-2016, 06:50 AM #9
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    sorry for many posts as I am new and system a bit slow.

    I means if L8 is any one of these"N","A" "V", I want K9. what is the symbol for any one of these "N", "A" or "V".

    thanks a lot.
    Reply With QuoteRegister To Reply
  10. 01-06-2016, 06:52 AM #10
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    Try

    =IF(OR(L8={"N","A","V"}),K9,IF(L8="U",0,))
    Reply With QuoteRegister To Reply
  11. 01-06-2016, 06:53 AM #11
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    @Shula.ankur. your help previously works perfectly. I just want to get a shorter version for any one of these instead of typing if statement multiple times. thanks
    Reply With QuoteRegister To Reply
  12. 01-06-2016, 06:54 AM #12
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    Did you check the post no #11???
    Reply With QuoteRegister To Reply
  13. 01-06-2016, 07:04 AM #13
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    @Ankur, Fantastic mate and it works perfectly. you can help me right away without any research. how did you study excel? any special text you recommend me to study in my free time.

    thanks heaps mate. I really appreciate it.
    Reply With QuoteRegister To Reply
  14. 01-06-2016, 07:07 AM #14
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: need urgent help with VLookup or overtime excel formula.

    Glad to help you Google for excel study material you will get bunch of site where you can learn and understand the excel functions and as well as formulas. If you liked my solutions please add reputations by clicking on star icons * left corner on your screen.
    Reply With QuoteRegister To Reply
  15. 01-06-2016, 07:10 AM #15
    Tar Yar
    Tar Yar is offline
    Registered User
    Join Date
    01-30-2013
    Location
    Ground
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: need urgent help with VLookup or overtime excel formula.

    thanks buddy.
    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)

Similar Threads

  1. Urgent need help for excel formula
    By kumar.vinod82 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2015, 09:07 AM
  2. Urgent need help for excel formula
    By kumar.vinod82 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2015, 03:06 AM
  3. Excel 2003 VLOOKUP HELP (URGENT)
    By babajunga in forum Excel General
    Replies: 6
    Last Post: 06-06-2012, 12:34 AM
  4. Overtime pay witholding tax Excel formula
    By bert regala in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-29-2012, 01:41 AM
  5. Overtime Excel formula for my work policy
    By mohamedelgaafary in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2012, 07:41 AM
  6. Excel wont add row 32 into the formula of an overtime calculator
    By v2win in forum Excel - New Users/Basics
    Replies: 14
    Last Post: 01-10-2009, 08:12 PM
  7. [SOLVED] excel formula to recognize overtime in a day as you prep schedule
    By H.L.S. 11 West in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2005, 11:05 AM

Tags for this Thread

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