5,930 reputation
1410
bio website pending
location England
age 49
visits member for 2 years, 7 months
seen 23 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.


May
3
comment Sum of N-highest number in a series excluding duplicates
I'm not clear if the range can contain more than 5 values? - If so this may not work if there are duplicates at the cutoff point, e.g. if you have a range containing these values - 7,7,7,7,7,7,7 - then the above formula returns 5
Apr
8
comment Countifs array with number of days
In the first formula the first IF filters out date ranges that don't overlap the block - if you don't use that then negative numbers are returned...but with format 0;\0 positive numbers are returned as integers, negative numbers as zero, so that has the same effect as the IF function. .....and yes, the +0 is then need to convert those text values to real numbers
Apr
8
comment Countifs array with number of days
No problem - I also found a slightly shorter way - should get the same results - I updated my answer
Apr
8
revised Countifs array with number of days
added 158 characters in body
Apr
8
revised Countifs array with number of days
deleted 21 characters in body
Apr
8
answered Countifs array with number of days
Mar
30
answered Excel, how to subtract time to span before midnight
Mar
26
awarded  microsoft-excel
Mar
25
answered Counting Text Cells
Mar
20
answered How do I calculate the number of months between two dates in Excel?
Mar
17
comment Excel TEXT function does not work properly
Why use TEXT function at all? unless you specifically need the date formatted as text it's usually better to just use cell formatting - and unlike the "dd.mm.yyyy" part of TEXT function the cell format will adjust according to the region
Mar
17
comment ,“>”&0),“”) at the end of a countif formula
">"&0 can also be written as ">0" which may be more transparent
Mar
12
comment How to extract max values from multiple rows and count how many fall to certain criteria, in one-cell function
Good question, Madball - I edited my answer
Mar
12
revised How to extract max values from multiple rows and count how many fall to certain criteria, in one-cell function
added 163 characters in body
Mar
12
answered How to extract max values from multiple rows and count how many fall to certain criteria, in one-cell function
Mar
7
answered Hiding an Excel formula untill the fields before are completed
Mar
7
comment Sum over every second cell if neighboring cell is not empty
@gehho, I suppose I'm stuck in the past! - I always avoided ISODD because in Excel 2003 and earlier versions it's an add-in function (Analysis ToolPak). In 2010, though, there's no problem
Mar
6
answered Sum over every second cell if neighboring cell is not empty
Mar
1
answered Excel dates - how much of a period falls in a financial year
Feb
27
answered vlookup with blank look up value