Posts by daddylonglegs

    Re: Counting Streaks In Data

    Assuming your data is in A2:Z2 this formula will count "streaks" of 3 or more consecutive "G"s


    This formula needs to be confirmed with CTRL+SHIFT+ENTER

    Re: Count Number Of Rows With Same City


    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


    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


    Note: this only works correctly if you have a header (or blank) in C1

    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


    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


    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


    Of course you'll want to remove it from the diagonal

    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


    then in C1 use this formula copied down to give the total tax payable on the running total


    Finally in D2 copied down use this formula to give the amount of tax payable on each individual invoice


    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