Question 1. OK. I’ve got several columns with 2700 entries each. All of the values in each column are =0 except one, which is >0. The value that is >0 varies from column to column.
At the bottom of each column, I would like to program a cell to search the column for the value that is >0, and to report that value. *Can Excel do this? * If so, how do I program it to do so?
Question 2. I have two adjacent cells with two different times, in hh:mm:ss format–let’s say 12:00:00 and 13:30:30. I know how to subtract one from the other to arrive at 1:30:30, and I know that I can tell Excel to report this as minutes elapsed, ie, 90:30. But can I tell Excel to convert this number to a decimal, ie, 90.5? If so, how?
If it’s bad form to submit software questions to the SDMB, I apologize, but I’m going insane and I need your help. Thanks.
Question 1: If this is really the situation (exactly one non-zero value in each column), then SUM(A1:A2700) will give you that. Am I missing something?
Question 2: Excel stores all date/time values as floating-point numbers (in terms of days), behind the scenes. The time 12:00 PM is stored as 0.5, for example. If you multiply the difference between your two times by 1440 (the number of minutes in a day), the hidden value will be the difference between the two times in minutes. Change the format on the cell with the formula to “number”, and this will force Excel to display that value as a number instead of as a date/time. This should give you what you want.
For question one, simply use the MAX function. Specifically, at the bottom of each column you have a cell with the formula “=MAX(range)” where range is the cells in the column above.
For question two, you need to understand that Excel store dates as a number, and represents them (ie shows them) in a date format you specify. So, for your example the cell contains .062847222! The unit for this odd answer is in days. So, to answer your question, multiply the difference by 1440 to get minutes. Make sure the cell is formatted as a number, not a date. Your cell will have a formula like "=(B10-B11)2460
There are other ways to do it with date converstion functions, but basically you have already got your answer (in days) and just need to convert it to minutes.
Thanks all. SCSimmons–yeah, there were some loopholes I didn’t mention for Question 1, which is why I hadn’t used SUM. But I think the MAX function will suffice–thanks K364. Thanks to you both for the time info.