6,640 reputation
1612
bio website pending
location England
age 50
visits member for 3 years, 1 month
seen 2 hours ago

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(Da‌​ta!F2:F500,MATCH(1,I‌​F(Data!B2:B500=Students!$M$7&"q3",IF(‌​Data!J2:J500=MAX(IF(Da‌​ta!B2:B500=Student‌​s!$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(Da‌​ta!F2:F500,MATCH(1,IF(Data!B2:B500=Students!$M$7&"q3",IF(‌​Data!J2:J500=MAX(IF(Da‌​ta!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),"Pre-Qualified","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)?