bio  website  pending 

location  England  
age  50  
visits  member for  3 years, 1 month 
seen  2 hours ago  
stats  profile views  409 
I'm keen on excel.....no, really.....I contribute to some other forums on the web, mostly excel formula related  short, long, complex......or very, very complex! I'm your man. I'm particularly interested in date related formulas.
21h

comment 
Duration calculation in seconds
You say "Duration calculation in seconds" but the formula you quote gives you days, hours, minutes and seconds...and the data shown doesn't include dates, so implies that you can't get days in the result  Can you give the exact results you want? 
2d

answered  Excel 2010 SUMPRODUCT FUNCTION  unable to do date comparison 
Jan 22 
answered  How do i apply conditional formating that is specific for each row? 
Jan 21 
answered  How can I use INDEX MATCH formula with two horizontal parameters? 
Jan 18 
comment 
Excel VLOOKUP with named range not working
Are you actually using "x", "y" and "z" or have you just simplified that for the question? If you use VLOOKUP with no 4th argument then the lookup range needs to sorted ascending.......and you may get a "closest match", e.g. if D5 = "yyy" you'll get the same result as if D5 = "y"  if you only want exact matches then use VLOOKUP with 4th argument of FALSE

Jan 15 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
No problem! Glad to be of assistance 
Jan 15 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
"put on hold as unclear what your asking"  I find it odd that the question is deemed to be as such when I'm using comments to clarify the question and getting good enough answers to answer it successfully to the asker's satisfaction  isn't that what comments are for?? 
Jan 14 
comment 
Trying to use SEARCH function in Excel
Yes, but the result is an array the same size as F1:F5 so you need another function to sum the array and give your total I use SUMPRODUCT to avoid "array entry" so you get this formula =SUMPRODUCT(COUNTIFS(A:A,$F$1:$F$5,B:B,"bug_report"))

Jan 14 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
In fact, probably easier to use COUNTIF for the first test, i.e. =IF(COUNTIF(Data!$B$2:$B$600,Students!$M$7&"q3"),INDEX(Data!F2:F500,MATCH(1,IF(Data!B2:B500=Students!$M$7&"q3",IF(Data!J2:J500=MAX(IF(Data!B2:B500=Students!$M$7&"q3",Data!J2:J500)),1)),0)),"")

Jan 14 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
Try this version: =IF(ISERROR(VLOOKUP(Students!$M$7&"q3",Data!$B$2:$G$600,5,FALSE)),"",INDEX(Data!F2:F500,MATCH(1,IF(Data!B2:B500=Students!$M$7&"q3",IF(Data!J2:J500=MAX(IF(Data!B2:B500=Students!$M$7&"q3",Data!J2:J500)),1)),0)))

Jan 13 
comment 
Excel  Exclude Cells from Computation
No, I don't think there's a simple way to do that........ 
Jan 13 
answered  Excel  Exclude Cells from Computation 
Jan 13 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
OK, I posted a "generic" answer which should fit the bill  let me know if you have any problems with it....or if I have misunderstood 
Jan 13 
answered  Return MAX value of CONCATENATED text using VLOOKUP 
Jan 13 
comment 
How do I add multiple formulas into one cell?
As E29:E34 is a contiguous range you could also use INDEX like this: =IF(J30<=INDEX(E29:E34,H4),"PreQualified","Unqualified") ...and if H4 were text you could use a MATCH function in place of H4

Jan 12 
comment 
Array manipulation in Excel formula arguments
Then I would revert to an "array formula" that will filter out any rows with text, e.g. =SUM(IF(ISNUMBER(MATCH(A1:A3&" "&B1:B3,A5:A6,0)*C1:C3/D1:D3),C1:C3/D1:D3))  confirmed with CTRL +SHIFT +ENTER

Jan 12 
revised 
Array manipulation in Excel formula arguments
added 6 characters in body 
Jan 12 
answered  Array manipulation in Excel formula arguments 
Jan 12 
comment 
Average Sum of Pairs of Numbers in a Column
Now the phrase that pops in to my head is this one  "Can't see the wood for the trees"  this is my answer of the week. In my defence my approach can perhaps be usefully used for other functions (median, standard deviation?) but for average this is much better

Jan 11 
comment 
Return MAX value of CONCATENATED text using VLOOKUP
I don't understand what you mean by "....now search Column 8 (calculated value) for MAX value and return what is in Column 5 for "smithjoeQ3"....." Do you mean you want the absolute max value in column 8.....or the max value for rows where column B contains "smithjoeQ3"? Note that in your VLOOKUP "column5" is actually F because that's the 5th column of the lookup array (B4:G951), so when you say "Column 8" do you mean column H or the 8th column starting at B (column I)?
