Reputation
7,560
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
1 7 13
Newest
 Nice Answer
Impact
~902k people reached

Jul
19
comment Elapsed time between two timestamps
...and if your data already includes the semi-colon between seconds and milliseconds you can use SUBSTITUTE function within the formula to convert, e.g. =SUBSTITUTE(A3,":",".",3)-SUBSTITUTE(A2,":",".",3) - format result cell as [s].000 to see just seconds and milliseconds
Jul
14
answered How to compare and sumproduct two cells value and use multiple criteria in IF in Excel 2013
Jul
14
comment How to compare and sumproduct two cells value and use multiple criteria in IF in Excel 2013
It's hard to tell from your description and formula how your data is set up. Which cell contains the month, is that Sheet1!H1? What does the range 'Master Tracker'!E7:E10000 contain? Are they dates or text values with just the month?
Jul
14
comment Diff bet time separated by colon (like 23:10:00 minus next day 01:15:00) in excel
@fixer1234 - not sure I agree - if we are talking about work shifts, for example, where the max length will never reach 24 hours, it's relatively simple to deal with shifts that cross midnight by using time values only - see my answer
Jul
14
answered Diff bet time separated by colon (like 23:10:00 minus next day 01:15:00) in excel
Jul
10
answered SUMPRODUCT with multiple conditions - EXCEL
Jul
7
answered Excel Formula To Get LAST Non-Zero Value in Row and Return Column Header
Jul
7
comment How to sum values over timeperiod in Excel?
Yes, I can't account for that - if your formula is consistent you'd get all zeroes - perhaps some of the column D values are text strings rather than real time/dates?
Jul
7
answered How to sum values over timeperiod in Excel?
Jun
26
comment Need a formula to check the first x amount of characters if numbers
To coin a phrase...."I agree with XOR LX" - using this formula I get TRUE with 2E1 and 1oct, they don't even have 7 characters.....
Jun
18
comment Excel # Different Then The Math
when I use those numbers I get -0.003 - are you sure B6, F6 and H1 are exactly .2500, .2503 and 10 respectively? What do you see if you increase the number of decimals in those cells (and increase the decimals for the result cell)?
Jun
18
answered Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?
Jun
11
answered Boolean “OR” Inside of Function
Jun
11
comment Boolean “OR” Inside of Function
Assuming Excel 2010 or later you could get the first match for 5, 6 or 7 with this formula: =AGGREGATE(15,6,MATCH({5,6,7},D:D,0),1)
Jun
11
comment Boolean “OR” Inside of Function
This solves a problem but it's not clear to me whether it solves this problem! That depends on how the OR should work - if 5 and 6 both exist in column D then should the formula return the position of the one that is positioned first? Of course this solution won't do that. If 5 is in D100 and 6 in D2 the MATCH part of the formula will return 100
Jun
11
comment Boolean “OR” Inside of Function
You need to define how you want an "OR" to work here. For your basic MATCH it will find the position of the first instance of 7. For an OR do you want it to find the position of the first instance of any of 5, 6 or 7?......or something else
Jun
11
comment Need a formula for Leap Year
Strictly speaking leap years won't always occur every 4 years, 2100 is not a leap year
Jun
11
comment Need a formula for Leap Year
You could also adapt the latter formula to just compare the day number of two dates 366 days apart, if they are the same there's a leap year in between, i.e. =IF(DAY(A1-1)=DAY(A1+365),"Leap Year","No Leap Year")
Jun
2
comment How to find value of last cell in an Excel row
LOOKUP can also be used for a column of values, e.g. =LOOKUP(2,1/(A1:A100<>""),A1:A100)....or mimicking teylyn's INDEX/MATCH, =LOOKUP("zzzz",A1:A100)
May
28
comment Excel multi table lookup, can you find a more elegant solution?
You can avoid volatile OFFSET by using another INDEX function, e.g. =INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0))