Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function - missing 1 IF and more
Hello this is my formula
=IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week=-37.5,"",IFVLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) Week = named ranged for 37.5 (normal working hours in a week) if the person has not submitted the report I do not want the answer to be -37.50 but "" (blank) if the person submitted the report and did not work overtime or worked less than 37.5 the answer should also be blank, if the person did work overtime then the hours less 37.50 Example hours worked 45.50 so overtime would be 8 hours . thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function - missing 1 IF and more
Wanna Learn;232425 Wrote: Hello this is my formula =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week=-37.5,"",IFVLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) Week = named ranged for 37.5 (normal working hours in a week) if the person has not submitted the report I do not want the answer to be -37.50 but "" (blank) if the person submitted the report and did not work overtime or worked less than 37.5 the answer should also be blank, if the person did work overtime then the hours less 37.50 Example hours worked 45.50 so overtime would be 8 hours . thanks Does this help ? =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)<=37.5,"",V LOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64887 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function - missing 1 IF and more
Thanks This works , except if the person did not submit the report I get
#NA. If the person did not submit the report I want it to be blank so I added the following to the formula =IF(ISERROR(VLOOKUP(B3,TB!B3:J125,9,FALSE))," ",IF(VLOOKUP($B3,TB!$B$10:$J$125,9,FALSE)<=37. 5," ",VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week)) I still bet #NA thanks in advance "Pecoflyer" wrote: Wanna Learn;232425 Wrote: Hello this is my formula =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week=-37.5,"",IFVLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) Week = named ranged for 37.5 (normal working hours in a week) if the person has not submitted the report I do not want the answer to be -37.50 but "" (blank) if the person submitted the report and did not work overtime or worked less than 37.5 the answer should also be blank, if the person did work overtime then the hours less 37.50 Example hours worked 45.50 so overtime would be 8 hours . thanks Does this help ? =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)<=37.5,"",V LOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64887 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function - missing 1 IF and more
Wanna Learn;232510 Wrote: Thanks This works , except if the person did not submit the report I get #NA. If the person did not submit the report I want it to be blank so I added the following to the formula =IF(ISERROR(VLOOKUP(B3,TB!B3:J125,9,FALSE))," ",IF(VLOOKUP($B3,TB!$B$10:$J$125,9,FALSE)<=37. 5," ",VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week)) I still bet #NA thanks in advance "Pecoflyer" wrote: Wanna Learn;232425 Wrote: Hello this is my formula =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week=-37.5,"",IFVLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) Week = named ranged for 37.5 (normal working hours in a week) if the person has not submitted the report I do not want the answer to be -37.50 but "" (blank) if the person submitted the report and did not work overtime or worked less than 37.5 the answer should also be blank, if the person did work overtime then the hours less 37.50 Example hours worked 45.50 so overtime would be 8 hours . thanks Does this help ? =IF(VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)<=37.5,"",V LOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'IF function - missing 1 IF and more - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=64887) A little faster would be =IF(or(countif(B3,TB!B3:B125)=0,VLOOKUP($B3,TB!$B$ 10:$J$125,9,FALSE)<=37. 5),"",VLOOKUP($B3,TB!$B$3:$J$125,9,FALSE)-Week)) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64887 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR - Function Missing | Excel Discussion (Misc queries) | |||
Missing function in Excel | Excel Worksheet Functions | |||
Function Missing | Excel Discussion (Misc queries) | |||
Excel Missing Function | Setting up and Configuration of Excel | |||
Missing function in Excel 2003 | Excel Discussion (Misc queries) |