Re: Calculating Times That Have different String Lengths
This formula would also convert your "date" and "time" columns to a recognisable format
=TEXT(F2,"0000-00-00")+TEXT(G2,"00\:00\:00")
format as desired e.g. dd/mm/yy hh:mm:ss
Re: Calculating Times That Have different String Lengths
This formula would also convert your "date" and "time" columns to a recognisable format
=TEXT(F2,"0000-00-00")+TEXT(G2,"00\:00\:00")
format as desired e.g. dd/mm/yy hh:mm:ss
Re: Counting Streaks In Data
Assuming your data is in A2:Z2 this formula will count "streaks" of 3 or more consecutive "G"s
=SUM(IF(FREQUENCY(IF(A2:Z2="G",COLUMN(A2:Z2)),IF(A2:Z2="M",COLUMN(A2:Z2)))>2,1))
This formula needs to be confirmed with CTRL+SHIFT+ENTER
Re: Count Number Of Rows With Same City
OK,
Sounds like you have the cities grouped together, e.g. something like this
header in C1 = "City"
C2 = Atlantic City
C3 = Atlantic City
C4 = Atlantic City
C5 = Baltimore
C6 = Baltimore
C7 = Chicago
etc.
You want F2 to show 3 (count of Atlantic City)
You want F5 to show 2 (count of Baltimore) etc.
If so then use this formula in F2 copied down
=IF(C1<>C2,COUNTIF(C:C,C2),"")
Note: this only works correctly if you have a header (or blank) in C1
Re: Concatenate Columns
Here's another way without using a helper column
=INDEX(C$1:C$7,MATCH(1,(A$1:A$7=A8)*(B$1:B$7=-B8),0))
confirmed with CTRL+SHIFT+ENTER
Re: Ceiling, Round And Floor Functions
Another way is to use the MROUND function from Analysis ToolPak
=MROUND(A1,0.5)
Re: Calculating Turn Around Time In Multiple Scenario
Hello ByTheCringe2
I don't think that formula will work on all possible dates. If B2 is 6-May-2007, C2 13:00, D2 7-May-2007 and E2 11:00 the result should be 3:00.
I posted a working solution here
http://www.excelforum.com/showthread.php?t=598808
revised to use the same setup as you, i.e. with day start in T3 and day end in T4
=(NETWORKDAYS(B2,D2)-1)*(T$4-T$3)+E2-MEDIAN(NETWORKDAYS(B2,B2)*C2,T$4,T$3)
This caters for the received time/date to be anything, sent time/date must be within the business hours
Note: to avoid using Analysis ToolPak functions you could change that to
=(SUM(INT((WEEKDAY(B2-{2,3,4,5,6})+D2-B2)/7))-1)*(T$4-T$3)+E2-MEDIAN((WEEKDAY(B2,2)<6)*C2,T$4,T$3)
although further amendment would be needed to exclude holidays....
Re: If Array Formula
If I understand correctly the problem is that while you want to refer to B4:B13 in row 15 when you drop one row the column ref needs to change, i.e. to C4:C13 so you can't do that just by changing from absolute references to relative. Try this (non -array) formula in B15 copied across and down to K24
=(SUMPRODUCT(--(INDEX($B$4:$K$13,0,MATCH($A15,$B$3:$K$3,0))>0),--(B$4:B$13>0))>0)+0
Of course you'll want to remove it from the diagonal
Re: Formula To Count Weekday Occurrence Based On Month And Year
My apologies, shg, I posted a formula I was using for testing. Yes, it was based on your example and should have referenced A1 containing the year, rather than A17, i.e.
=5-(DAY( DATE($A$1,$A2,36))>WEEKDAY(DATE($A$1,$A2,1-B$1)))
btw, congratulations on your 1000 posts
Re: Formula To Count Weekday Occurrence Based On Month And Year
Another way to achieve this would be to use this formula in B2 copied across and down
=5-(DAY(DATE($A$17,$A2,36))>WEEKDAY(DATE($A$17,$A2,1-B$1)))
Re: Tax Brackets
If you have your outstanding balance in B1, i.e. 9950 then each subsequent invoice amount in A2 down, i.e. A2 contains 100, A3 should contain your next invoice amount.
In B2 copied down use this formula to give a running total
=B1+A2
then in C1 use this formula copied down to give the total tax payable on the running total
=B1*0.01+MAX(0,B1-10000)*0.002
Finally in D2 copied down use this formula to give the amount of tax payable on each individual invoice
=C2-C1
Note: if you have additional tax bands which kick in at some higher amount these can also be incorporated in the column C formula
Re: Lookup Function Error
Note that if you do sort the table and use that LOOKUP formula then it will return a "closest match", e.g. if A1 contained "WEST GARDENS", which doesn't exist in your lookup table, you'll get a result of "SYD". You probably don't want that so revise formula to:
=IF(LOOKUP(A1,lookup_table!$B$1:$B$38)=A1,LOOKUP(A1,lookup_table!$B$1:$C$38),"No Match")
This is a more efficient formula than using VLOOKUP on an unsorted lookup table, especially if you have 65000 rows
Re: Number Of Days Per Year
If you have a date in A1 this formula will give the number of days in that year
=366-(DAY(DATE(YEAR(A1),2,29))=1)