Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX returns correct column, but row -1?
I want to look up the closest completed Week Ending Date & return the
value of remaining Vacation Time from column I, for the appropriate week ending date. 'WEEKDAY' = Week Ending Dates for 2007 I12:I63 = Vacation Time Remaining I can't figure out why I continue to get an #N/A error with this formula: {=VLOOKUP(MAX(WEEKDAY<=TODAY()),I12:I63,1,FALSE)} Or why it returns the appropriate column, minus 1 row, when a value is entered into column H (Vacation Used for Week). {=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} Example: {=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} returns 20.17 hours remaining if I didn't use any vacation time that week (column H). However, I used '10 hours' of vacation that week (column H), the formula returns the value in the cell above 20.17. Any thoughts? Either formula would work, I believe, if I could figure out Thanks, Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX returns correct column, but row -1?
You are using a cannon to kill a fly. Even worse, you missed. <g
Try something simple like: =INDEX(I12:I63,MATCH(TODAY(),WEEKDAY,1)) __________________________________________________ ______________________ "Ryan" wrote in message ups.com... I want to look up the closest completed Week Ending Date & return the value of remaining Vacation Time from column I, for the appropriate week ending date. 'WEEKDAY' = Week Ending Dates for 2007 I12:I63 = Vacation Time Remaining I can't figure out why I continue to get an #N/A error with this formula: {=VLOOKUP(MAX(WEEKDAY<=TODAY()),I12:I63,1,FALSE)} Or why it returns the appropriate column, minus 1 row, when a value is entered into column H (Vacation Used for Week). {=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} Example: {=MAX(IF(WEEKDAY<=TODAY(),I12:I63))} returns 20.17 hours remaining if I didn't use any vacation time that week (column H). However, I used '10 hours' of vacation that week (column H), the formula returns the value in the cell above 20.17. Any thoughts? Either formula would work, I believe, if I could figure out Thanks, Ryan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX returns correct column, but row -1?
All it takes sometimes is a new set of eyes.
Thanks for your help, this works awesome! :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX returns correct column, but row -1?
You're most welcome!
__________________________________________________ _______________________ "Ryan" wrote in message ups.com... All it takes sometimes is a new set of eyes. Thanks for your help, this works awesome! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Address block carriage returns not correct | Excel Worksheet Functions | |||
SUM Calculations Returns #VALUE but displays correct total with "Insert Function"=fx | Excel Worksheet Functions | |||
what is the correct formula to add a column with =sum | New Users to Excel | |||
Help Please- How to offset Date to correct column for Ageing | Excel Discussion (Misc queries) | |||
How can I turn auto correct on or off for one column only? | Excel Worksheet Functions |