Posts by daddylonglegs

    Re: Search first X characters for four characters in a row that match value in a Tabl


    The 29 is just a little trick......


    I used this part


    TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")


    to generate this array


    {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}


    You could replace the first with the latter if you want


    it works because


    29*{1,2,3,4,5,6,7,8,9,10,11,12} generates this array


    {29,58,87,116,145,174,203,232,261,290,319,348}


    and as Excel starts counting dates from 1/1/1900 each of those numbers represents a date in successive months in 1900.


    I started by just searching for the month but in some of your data you had "aug" or similar somewheree in the other text so I looked for "Aug1" etc. to make sure it was a date......let me have a look and see if I can improve that.......

    Re: Search first X characters for four characters in a row that match value in a Tabl


    The file you attached was an Excel 2007 file so I assumed you could use IFERROR, or do you not want to?


    You have some parentheses out of place with ISERROR, try


    =IF(ISERROR(TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5))),"",TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5)))


    although, I dislike repeating the whole formula. If there is an error it will be with the SEARCH part so you could shorten that with this version


    =IF(COUNT(SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1)),TRIM(MID(B1,LOOKUP(2^15,SEARCH(TEXT(29*{1,2,3,4,5,6,7,8,9,10,11,12},"mmm")&1,B1))-7,5)),"")

    Re: Excel help on DownTime Formula


    Assuming your working days are Monday to Friday and that start and end times will always be within the working hours then you could use this formula in C2


    =(NETWORKDAYS(A2,B2)-1)*9/24+MOD(B2,1)-MOD(A2,1)


    format C2 as [h]:mm


    Please advise if working days are different or if start and end can be at evenings weekends etc......

    Re: difference between 2 times in yyyy.mm.dd hh:mm


    Assuming your date/times are text-formatted then a straight subtraction won't work. Try using SUBSTITUTE function to replace . with - to give a valid date format, i.e. in C2


    =SUBSTITUTE(B2,".","-")-SUBSTITUTE(A2,".","-")


    custom format C2 as [h]:mm

    Re: Lookup Nth Occurence


    Hello HSU,


    I replied to your thread at Excelforum, this is my reply.....


    The specific reason that your formula returns #N/A is that you have an #N/A error in the range 'Risk Register Testing'!AA11:AA85 (in AA34). You either need to prevent that at source (change formulas in 'Risk Register Testing'!AA11:AA85) or alter your other formula to ignore #N/A


    In any case your formula won't extract the correct value. The first 6.2 is in row 16 so the SMALL part of the formula returns 16, so this.....


    =INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)),1))


    ....becomes


    =INDEX('Risk Register Testing'!AB11:AB85,16)


    the 16th value in the range 'Risk Register Testing'!AB11:AB85 is in 'Risk Register Testing'!AB26, I assume you want the value from 'Risk Register Testing'!AB16. The best way to do that is to change the formula to this


    =INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1),1)


    ....or to ignore error values


    =INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF(ISNUMBER('Risk Register Testing'!AA11:AA85),IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1)),1))


    Note: I think there is a value in referring to the other worksheet within the ROW function. In the formula above if some rows are added or deleted below or within the range the formula will adjust to the new range, hence it's more robust

    Re: Count only the unique numbers in a row


    If some of the "numbers" are text formatted, and you want to count those too then try revising Mike's original formula like this


    =SUMPRODUCT(ISNUMBER(D4:CK4+0)*(D4:CK4<>"")/COUNTIF(D4:CK4,D4:CK4&""))


    One possible side effect is that would count as a number anything that coerces to a number, e.g. 1/1 which becomes a date (number) when you co-erce with +0

    Re: SUMPRODUCT function where condition is cell range?


    You can use MATCH to deal with a range as criteria, i.e.


    =SUMPRODUCT(('Data Page'!$B$8:$B$8630=”Something”)*ISNUMBER(MATCH('Data Page'!$J$8:$J$8630,A2:A3,0)))


    That approach means you can easily accommodate a criteria range of 10 or 100 cells (and the range can be either vertical or horizontal, it doesn't matter which as long as it's just a single column or row)

    Re: Find dates within date range


    Small change to PCI's formula will check whether any of the three dates fall in the range


    =(B7<=EndDate)*(B7>=StartDate)+(C7<=EndDate)*(C7>=StartDate)+(G7<=EndDate)*(G7>=StartDate)=1

    Re: Calculation of dates and funding between two fiscal years


    Hello Tessy,


    I'm a little confused by this - rather than quoting formulas that don't work I think it would be better for you to explain what you want to do exactly.


    What results do you expect to get in K5:L6 for instance - are the figures shown there the results you want or should they be different? Don't forget there aren't exactly 52 weeks in any year....so 253.85 per week isn't the same as 13,200 a year...and some years are longer than other years....