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

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

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)