|
Identify last item in a list
I have a list of timestamps in column A (one per minute for a day,
total 1440 entries), and an associated list of TRUE/FALSE values in column B: 12:00 AM FALSE 12:01 AM FALSE 12:02 AM TRUE |
Try...
=INDEX(A1:A7,MATCH(2,1/(B1:B7=TRUE))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Greg wrote: I have a list of timestamps in column A (one per minute for a day, total 1440 entries), and an associated list of TRUE/FALSE values in column B: 12:00 AM FALSE 12:01 AM FALSE 12:02 AM TRUE . . . 11:56 PM TRUE 11:57 PM TRUE 11:58 PM FALSE 11:59 PM FALSE I need a formula that will return the timestamp associated with the *last* TRUE value in the list. In the example above, the formula would return 11:57 PM. If there are no TRUE values anywhere in the list, then the formula should return some error text, i.e., "No TRUE values found." The spreadsheet has no macros on it, and I would prefer to keep it that way (so users do not have to contend with macro virus warnings), therefore a formula-based solution is highly preferable. Thanks, Greg Hurwitt |
I think that's a little overkill. Try:
=IF(COUNTIF(A1:A1440,TRUE)=0,"No TRUE values found",...) HTH Jason Atlanta, GA -----Original Message----- Works great! Thank you. I added the necessary pieces to provide the desired error checking: =IF(ISNA(INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))), "No TRUE values found.",INDEX(A1:A1440,MATCH(2,1/ (B1:B1440=TRUE)))) Greg Domenic wrote: Try... =INDEX(A1:A7,MATCH(2,1/(B1:B7=TRUE))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Greg wrote: I have a list of timestamps in column A (one per minute for a day, total 1440 entries), and an associated list of TRUE/FALSE values in column B: 12:00 AM FALSE 12:01 AM FALSE 12:02 AM TRUE . . . 11:56 PM TRUE 11:57 PM TRUE 11:58 PM FALSE 11:59 PM FALSE I need a formula that will return the timestamp associated with the *last* TRUE value in the list. In the example above, the formula would return 11:57 PM. If there are no TRUE values anywhere in the list, then the formula should return some error text, i.e., "No TRUE values found." The spreadsheet has no macros on it, and I would prefer to keep it that way (so users do not have to contend with macro virus warnings), therefore a formula-based solution is highly preferable. Thanks, Greg Hurwitt . |
Re-read my original post. The "No TRUE values found" part is just the
error check. The main purpose of the function is to identify the timestamp associated with the last TRUE entry. Greg "Jason Morin" wrote: I think that's a little overkill. Try: =IF(COUNTIF(A1:A1440,TRUE)=0,"No TRUE values found",...) HTH Jason Atlanta, GA -----Original Message----- Works great! Thank you. I added the necessary pieces to provide the desired error checking: =IF(ISNA(INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))), "No TRUE values found.",INDEX(A1:A1440,MATCH(2,1/ (B1:B1440=TRUE)))) Greg Domenic wrote: Try... =INDEX(A1:A7,MATCH(2,1/(B1:B7=TRUE))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Greg wrote: I have a list of timestamps in column A (one per minute for a day, total 1440 entries), and an associated list of TRUE/FALSE values in column B: 12:00 AM FALSE 12:01 AM FALSE 12:02 AM TRUE . . . 11:56 PM TRUE 11:57 PM TRUE 11:58 PM FALSE 11:59 PM FALSE I need a formula that will return the timestamp associated with the *last* TRUE value in the list. In the example above, the formula would return 11:57 PM. If there are no TRUE values anywhere in the list, then the formula should return some error text, i.e., "No TRUE values found." The spreadsheet has no macros on it, and I would prefer to keep it that way (so users do not have to contend with macro virus warnings), therefore a formula-based solution is highly preferable. Thanks, Greg Hurwitt . -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. |
Hi!
Jason's suggestion is more efficient and also results in a shorter formula! Biff -----Original Message----- Re-read my original post. The "No TRUE values found" part is just the error check. The main purpose of the function is to identify the timestamp associated with the last TRUE entry. Greg "Jason Morin" wrote: I think that's a little overkill. Try: =IF(COUNTIF(A1:A1440,TRUE)=0,"No TRUE values found",...) HTH Jason Atlanta, GA -----Original Message----- Works great! Thank you. I added the necessary pieces to provide the desired error checking: =IF(ISNA(INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))), "No TRUE values found.",INDEX(A1:A1440,MATCH(2,1/ (B1:B1440=TRUE)))) Greg Domenic wrote: Try... =INDEX(A1:A7,MATCH(2,1/(B1:B7=TRUE))) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Greg wrote: I have a list of timestamps in column A (one per minute for a day, total 1440 entries), and an associated list of TRUE/FALSE values in column B: 12:00 AM FALSE 12:01 AM FALSE 12:02 AM TRUE . . . 11:56 PM TRUE 11:57 PM TRUE 11:58 PM FALSE 11:59 PM FALSE I need a formula that will return the timestamp associated with the *last* TRUE value in the list. In the example above, the formula would return 11:57 PM. If there are no TRUE values anywhere in the list, then the formula should return some error text, i.e., "No TRUE values found." The spreadsheet has no macros on it, and I would prefer to keep it that way (so users do not have to contend with macro virus warnings), therefore a formula-based solution is highly preferable. Thanks, Greg Hurwitt . -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. . |
Greg,
If I'm not mistaken, I believe Jason's suggestion is to use COUNTIF() rather than ISNA(INDEX(...)) for error checking... =IF(COUNTIF(B1:B1440,TRUE)=0,"No TRUE values found",INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))) Probably a little more efficient, and a good suggestion at that. Hope this helps! In article , Greg wrote: Re-read my original post. The "No TRUE values found" part is just the error check. The main purpose of the function is to identify the timestamp associated with the last TRUE entry. Greg |
I see it now. Thanks to everyone for all the help.
Greg Domenic wrote: Greg, If I'm not mistaken, I believe Jason's suggestion is to use COUNTIF() rather than ISNA(INDEX(...)) for error checking... =IF(COUNTIF(B1:B1440,TRUE)=0,"No TRUE values found",INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))) Probably a little more efficient, and a good suggestion at that. Hope this helps! In article , Greg wrote: Re-read my original post. The "No TRUE values found" part is just the error check. The main purpose of the function is to identify the timestamp associated with the last TRUE entry. Greg -- Greg phobos78-marslink-net Replace dashes and move in by 1 planet to reply. |
| All times are GMT +1. The time now is 04:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com