Re: Calculate the next date of s specific weekday
For these formulas I avoid MOD because the results are dependant on the date system (if you change to 1904 date system the formula gives a different result). The following don't depend on date system.....
For the first try
=A1+8-WEEKDAY(A1-3)
and for the second
=A1+11-WEEKDAY(A1)
for the latter I assume that if A1 is any date from 17th Oct to 23rd Oct 2010 then you want the result to be 27th Oct 2010
Re: MATCH & INDEX with Multiple Criteria (Cannot use CSE)
Sorry, small glitch in D5 formula, should be
=IF(COUNT(FIND(","&B5&",",","&B$1&",")),LOOKUP(9^9,D$4:D4)+1,"")
see attached example
Re: MATCH & INDEX with Multiple Criteria (Cannot use CSE)
Do you mind using a helper column - you can hide it if necessary.......?
Put a zero in D4 then this formula in D5 copied down
=IF(COUNT(FIND(","&B5&",","'"&B$1&",")),LOOKUP(9^9,D$4:D4)+1,"")
That puts a sequential number against each entry for your matching countries
Now in E5 use this formula
=IF(ROWS(E$5:E5)>MAX($D:$D),"",INDEX(B:B,MATCH(ROWS(E$5:E5),$D:$D,0)))
copy across to F5 and then as far down as you need
hide column D
Re: Remove .xls file extention from Cell name
It's always best to use CELL function in this case with a cell reference (any cell), i.e. instead of =CELL("filename") use =CELL("filename",A1). If you don't then the formula returns the filename of the last workbook you changed - that might not be the one in which the formula resides. Given that you can shorten a little, i.e.
=MID(MID(CELL("filename",A1),1,FIND(".xls",CELL("filename",A1))-1),FIND("[",CELL("filename",A1))+1,99)
Re: Calculate average scores
You could use this "array formula" in G5
=AVERAGE(IF(B$3:B$22=F5,C$3:C$22))
confirmed with CTRL+SHIFT+ENTER and copied down to G7
....or a "non-array" option.....
=SUMIF(B$3:B$22,F5,C$3:C$22)/COUNTIF(B$3:B$22,F5)
Re: Converting date formula
You could custom format the dates as
mmm yy "word"
or if you want a formula in another cell
=TEXT(A2,"mmm yy")&" word"
where your date is in cell A2
Re: Date Formating problem
16-10-10 19:48:21:010 isn't a valid date/time format in Excel. If the last 010 represents milliseconds then that would normally be preceded by a . rather than a :
As you only want the date you can grab that by taking everything before the space .....so try this formula to convert
=LEFT(A1,FIND(" ",A1))+0
format result cell in required date format
Re: Count unique entries within a start date end date range
Explanation....
=SUM(IF(FREQUENCY(IF(B2:B10>Q4,"",IF(C2:C10<P4,"",IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)))),ROW(A2:A10)-ROW(A2)+1),1))
The IFs are used to filter out “non-qualifying” rows, if the start date of the absence is greater than P4 or the end date is earlier than Q4 then there’s no intersect with the date range, otherwise there is an overlap….in which case the MATCH function is applied (assuming the name range is also non-blank). This will return the row number (within the referenced range) of the first instance of that name. So any qualifying row where the name is Sinead Jones will result in 2 because that name occurs first on row 2 of the range. The results of the IFs (those row numbers) are then assigned to the FREQUENCY bins and each >0 value in the FREQUENCY result is counted as 1 (and those are summed) thus effectively giving you a unique count of the names in the qualifying rows.
That probably all sounds like gibberish so here’s an example based on the data in Rob’s sheet but with a date range of 1st April 2010 to 11th September 2010.
The first two rows fall outside the range (and the last 3 are blank) so this part of the formula
IF(B2:B10>Q4,"",IF(C2:C10<P4,"",IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))
Returns this array
{"";"";2;4;5;4;"";"";""}
So the 4 matching rows all return numbers. Note that 2 of the numbers are the same because both instances of Mad Mike return 4 because Mad Mike first appears on row 4 of the range
This part of the formula
ROW(A2:A10)-ROW(A2)+1
Returns the frequency “bins”, i.e. just
{1;2;3;4;5;6;7;8;9}
So FREQUENCY function returns
{0;1;0;2;1;0;0;0;0;0}
One each of 2 and 5 are assigned to the 2 and 5 bins and 2 instance of 4 to the 4 bin
Now we just assign 1 to each of the non-zero numbers and sum them – hence a result of 3 – 3 different names within the qualifying date range.
Note that I deliberately included some blank rows to show that the formula copes with those OK
This formula is a variation of a formula often suggested by MS MVP Aladin Akyurek (and probably originating with him)
Regards, daddylonglegs
Re: Count unique entries within a start date end date range
Actually, I tried to be too clever.....took out a vital part of the formula, thinking I didn't need it......so it will miscalculate in some circumstances. Corrected version:
=SUM(IF(FREQUENCY(IF(B2:B10>Q4,"",IF(C2:C10<P4,"",IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)))),ROW(A2:A10)-ROW(A2)+1),1))
Explanation to follow.......
Re: Count unique entries within a start date end date range
Here's a single formula solution. Based on Rob's sheet it uses just the base data in columns A,B and C and the start and end dates in P4 and Q4
=SUM(IF(FREQUENCY(IF(B2:B10>Q4,,IF(C2:C10<P4,,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)))),ROW(A2:A10)-ROW(A2)+1),1))
This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER
Re: OFFSET,MATCH,COUNTIF in Dependent Drop Down List
The problem occurs because your OFFSET starts from B3 but the MATCH is looking at the whole column B, so if the match is with B70 B3+70-1 gives you B72.
Either define NetworkStart as B1...or have Network Column start at B3...or use -3 after the MATCH function rather than -1
Re: Determining the number of cases started after 03:30 PM...
Hello Ang,
I wouldn't expect to get #NAME? error for that formula but I expect it would always return incorrect results (it's treating D6 as a text value not a cell reference). To use a cell reference you need to concatenate using & like this
=COUNTIF(D5:D47,"<="&D6)
Re: VLOOKUPs and date/time ranges
Assuming the data in LICENSE is grouped by customer with column B in chronological order for each customer (as in your sample) then try this formula in G3 copied down
=IF(SUMPRODUCT((B3=LICENSE!A$2:A$100)*(LICENSE!B$2:B$100<=C3)),LOOKUP(C3,OFFSET(LICENSE!B$2,MATCH(B3,LICENSE!A$2:A$100,0)-1,0,COUNTIF(LICENSE!A$2:A$100,B3),2)),VLOOKUP(B3,LICENSE!A$2:C$100,3,0))
Re: Find highest 3 value from column without duplicate
Assuming your numbers are in A1:A100 then try this. In C1 type "top 3" then in C2 enter this formula
=LARGE(A$1:A$100,COUNTIF(A$1:A$100,">="&C1)+1)
and copy down to C4
Re: Use wildcard in SUMPRODUCT formula
Which version of Excel are you using? If you have Excel 2007 or later then a SUMIFS formula would probably be more efficient....and that does allow wildcards, i.e.
=SUMIFS(G$4:G$431,A$4:$A$431,"*Permission*",$F$4:$F$431"ABC")
Re: Expert: Indirect Reference to Named Formula ; Multi-Formula Switch
Just a small suggestion to add to Rob's excellent answer.....
LOOKUP requires you to list the search terms in ascending alphabetical order....and it can give you a "closest match", i.e. source "ABCE" would match with "ABCD", which I imagine you don't want. I'd suggest using MATCH which produces output ideally suited for CHOOSE.....and you'll get #N/A for any match that isn't exact, i.e.
=CHOOSE(MATCH(source,{"ABCD","WXYZ",...},0),VLOOKUP(x,y,z,0),VLOOKUP(a,b,c,0),...)
Re: Find key words in text string
In a similar vein to Rob's suggestions......
If you have search terms listed in B1:B20 and corresponding text to return in C1:C20 you could use this formula
=LOOKUP(2^15,SEARCH(B$1:B$20,A1),C$1:C$20)
Note that this will return the last match.....
Re: Count Dates with Criteria
Hello John,
It's probably best to avoid merged cells - they make calculations more difficult. Usually, however you just refer to the merged column by the first letter, i.e. R.
Your specifc problem is caused by "10/5/2010" - that won't be recognised as a date unless you use DATEVALUE or add +0 (or similar) to co-erce to a date value. My preference, though, is to use DATE function because that's unambiguous and doesn't depend on regional settings. So I'd suggest using a "non-array" SUMPRODUCT, i.e.
=SUMPRODUCT((Cum!$G$3:$G$2000<DATE(2010,5,10))*(Cum!$R$3:$R$2000="BT Delivery"))
As truly blank cells will be interpreted as zero this will also count rows with "BT Delivery" and a blank in the date column - to avoid that you can add another criteria, i.e.
=SUMPRODUCT((Cum!$G$3:$G$2000<DATE(2010,5,10))*(Cum!$G$3:$G$2000<>"")*(Cum!$R$3:$R$2000="BT Delivery"))
Re: Multi-condition count
Quote from boingboing069;525700Excel 2007
So the COUNTIFS function I suggested should work, did you try that?
=COUNTIFS(A:A,"John",B:B,"Yes")
Re: Formula Help
I doubt if SUMIF would be appropriate here Armando, this looks like a multi-conditional count to me.
Which version of Excel are you using, boingboing? In Excel 2007 and later you can use COUNTIFS for multi-conditional counting, e.g.
=COUNTIFS(A:A,"John",B:B,"Yes")
or for Excel 2003 and earlier try SUMPRODUCT, e.g.
=SUMPRODUCT((A$2:A$100="John")*(B$2:B$100="Yes"))
