Home |
Search |
Today's Posts |
#1
|
|||
|
|||
What Function to use?
Apologies - I think my oil filter is blocked and my brain is lacking all
lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
#2
|
|||
|
|||
Peter,
=VLOOKUP(Cell with code,Table with codes and sunrises,2,False) where Table with codes and sunrises has the airport as the first column, with sunrise as the second column. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Apologies - I think my oil filter is blocked and my brain is lacking all lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
#3
|
|||
|
|||
Bernie
Thanks for the advice. I have tried to experiment with your suggestion and am unable to achieve the result that I want - as yet! If i re-arrange the data so that the data table hos just two columns [Airport Code, Sunrise Time] (as I think your formula requires - let me know if I have misinterpretted this), how do I code the vlookup formula so that it picks up the sunirise time for the required airport for the required date. The table will contain many many sunrises for a particular airport - one for each day of the year. Thanks in advance for any further light that you can shed on this. Peter "Bernie Deitrick" wrote: Peter, =VLOOKUP(Cell with code,Table with codes and sunrises,2,False) where Table with codes and sunrises has the airport as the first column, with sunrise as the second column. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Apologies - I think my oil filter is blocked and my brain is lacking all lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
#4
|
|||
|
|||
Peter,
I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on which to base the lookup, but there are better ways. So, let's say that your dates are in column A, and your airport codes are in column B, and your sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest, and in E1, enter the airport code of interest. Assuming you have only one entry per date per airport, then you could use this formula =SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000) ) If you have more than 1000 rows, increase the 1000 in each of the three places. One complication may be how your dates are entered: make sure that the formula =D1=A??? where A??? is a cell that you THINK matches the date in D1, actually returns TRUE. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Bernie Thanks for the advice. I have tried to experiment with your suggestion and am unable to achieve the result that I want - as yet! If i re-arrange the data so that the data table hos just two columns [Airport Code, Sunrise Time] (as I think your formula requires - let me know if I have misinterpretted this), how do I code the vlookup formula so that it picks up the sunirise time for the required airport for the required date. The table will contain many many sunrises for a particular airport - one for each day of the year. Thanks in advance for any further light that you can shed on this. Peter "Bernie Deitrick" wrote: Peter, =VLOOKUP(Cell with code,Table with codes and sunrises,2,False) where Table with codes and sunrises has the airport as the first column, with sunrise as the second column. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Apologies - I think my oil filter is blocked and my brain is lacking all lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
#5
|
|||
|
|||
Peter,
I should have added that the cell with the formula should be formatted as time, and will only work if the times are true times and not strings. If the times are strings, you can change the formula to account for that as well. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Peter, I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on which to base the lookup, but there are better ways. So, let's say that your dates are in column A, and your airport codes are in column B, and your sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest, and in E1, enter the airport code of interest. Assuming you have only one entry per date per airport, then you could use this formula =SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000) ) If you have more than 1000 rows, increase the 1000 in each of the three places. One complication may be how your dates are entered: make sure that the formula =D1=A??? where A??? is a cell that you THINK matches the date in D1, actually returns TRUE. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Bernie Thanks for the advice. I have tried to experiment with your suggestion and am unable to achieve the result that I want - as yet! If i re-arrange the data so that the data table hos just two columns [Airport Code, Sunrise Time] (as I think your formula requires - let me know if I have misinterpretted this), how do I code the vlookup formula so that it picks up the sunirise time for the required airport for the required date. The table will contain many many sunrises for a particular airport - one for each day of the year. Thanks in advance for any further light that you can shed on this. Peter "Bernie Deitrick" wrote: Peter, =VLOOKUP(Cell with code,Table with codes and sunrises,2,False) where Table with codes and sunrises has the airport as the first column, with sunrise as the second column. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Apologies - I think my oil filter is blocked and my brain is lacking all lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
#6
|
|||
|
|||
Bernie
Thanks for this - I have got the spreadsheet working now. All the best. Peter "Bernie Deitrick" wrote: Peter, I should have added that the cell with the formula should be formatted as time, and will only work if the times are true times and not strings. If the times are strings, you can change the formula to account for that as well. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Peter, I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on which to base the lookup, but there are better ways. So, let's say that your dates are in column A, and your airport codes are in column B, and your sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest, and in E1, enter the airport code of interest. Assuming you have only one entry per date per airport, then you could use this formula =SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000) ) If you have more than 1000 rows, increase the 1000 in each of the three places. One complication may be how your dates are entered: make sure that the formula =D1=A??? where A??? is a cell that you THINK matches the date in D1, actually returns TRUE. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Bernie Thanks for the advice. I have tried to experiment with your suggestion and am unable to achieve the result that I want - as yet! If i re-arrange the data so that the data table hos just two columns [Airport Code, Sunrise Time] (as I think your formula requires - let me know if I have misinterpretted this), how do I code the vlookup formula so that it picks up the sunirise time for the required airport for the required date. The table will contain many many sunrises for a particular airport - one for each day of the year. Thanks in advance for any further light that you can shed on this. Peter "Bernie Deitrick" wrote: Peter, =VLOOKUP(Cell with code,Table with codes and sunrises,2,False) where Table with codes and sunrises has the airport as the first column, with sunrise as the second column. HTH, Bernie MS Excel MVP "Peter Barrett" wrote in message ... Apologies - I think my oil filter is blocked and my brain is lacking all lubrication! I cannot owrk out what function to use to select values from a table - can anyone please advise? In simple terms, I am trying to manipultae data as shown below: Input Output TABLE OF DATA Date Airport Sunrise FRA MAN 1 August 2005 MAN 04:25 03:54 04:25 2 August 2005 FRA 03:56 03:56 04:26 For each row, I want to input an airport (e.g. MAN, FRA) and obtain the correct sunrise time for that airport. There is a table of daily sunrise times for the airports - which is from where I want to be able to extract the data. Can anyone tell me how to do this - I have tried LOOKUP without any success. Thanks in advance Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |