Posts by daddylonglegs

  • Counting Streaks In Data

    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

  • Count Number Of Rows With Same City

    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

  • Calculating Turn Around Time In Multiple Scenario

    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....

  • If Array Formula

    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

  • Tax Brackets

    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

  • Lookup Function Error

    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