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

May
14
awarded  Nice Answer
May
10
answered Excel SUMIFS not working for subtotals
May
5
comment Excel text to convert date format
I would use DATE function with similar inputs, but in a different order, year - month day is required =DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,5,2)) - this will work in any region
May
4
comment Excel text to convert date format
Is the month always 3 letters or the whole month - it's hard to tell with May as the example! - what would 1st June look like?
May
1
comment How to round off an hour value?
Thanks - you can do similar with FLOOR or MROUND, depending on whether you want to round up, down or to the nearest 15
May
1
answered How to round off an hour value?
Apr
28
revised List values based on criteria
added 185 characters in body
Apr
26
comment Index of N'th largest value in a range
@Zachary Turner. When the IF is FALSE the formula returns FALSE, so you get an array of numbers and FALSE values passed to the SMALL function. SMALL ignores logical values like TRUE and FALSE
Apr
26
revised Index of N'th largest value in a range
edited body
Apr
26
comment Index of N'th largest value in a range
@Scott, Yes, thanks that was a typo - correcting it now. I will also add some explanation....
Apr
26
answered SUMPRODUCT where the product is computed from the row
Apr
26
answered Index of N'th largest value in a range
Apr
21
comment Excel to count number of concurrent sessions based on start/end times
Are you referring to all 20,000 rows in all the formulas? Depending on your data you may be able to speed it up by using smaller ranges - if the data is sorted by time then presumably there will only be at most a few hundred rows which can overlap with the current row?
Apr
21
comment Excel to count number of concurrent sessions based on start/end times
@fixer1234 - no problem, I added an explanation
Apr
21
revised Excel to count number of concurrent sessions based on start/end times
added 1939 characters in body
Apr
21
answered Excel to count number of concurrent sessions based on start/end times
Apr
16
answered How to add higest ten values from odd/even columns in Excel 2010
Apr
15
comment Convert a group of letters into a date in excel
....or you can even get rid of MOD function if you do it like this: =TEXT(SUMPRODUCT(FIND(MID(A1,{1,2,3,4,5,6},1),"RSWITCHGEA")-1,10^{5,4,3,2,1,0})‌​,"0-00-00")+0
Apr
15
comment Convert a group of letters into a date in excel
Are you always using 6 letters? How is 1st Jan 2015 represented?
Apr
15
comment Convert a group of letters into a date in excel
Nice! If there will always be 6 letters in A1 you could simplify to this non-array version =TEXT(SUMPRODUCT(MOD(FIND(MID(A1,{1,2,3,4,5,6},1),"SWITCHGEAR"),10),10^{5,4,3,2‌​,1,0}),"00-00-00")+0 to return an actual date