Posts by etaf

    Re: Compare actual grade to potential grade and highlight cells accordingly

    you could probably use a lookup to change to a value in some helper columns
    and then compare the helper columns and conditional format the grades
    (note the letters need to be ascending )

    how would you compare the A/B S/A
    not sure how that would work in the array

    if you putin alpha order based on first character it will work with the array
    assigns the values
    and then simple enough to compare
    you need the helper columns because conditional format wont do the array lookup

    see the attached example - with conditional formating
    green for higher or equal to
    red for lower

    Re: assigning points to percentages

    you could use a lookup table to assign the extra points

    set up a table of % and points
    and then use
    to pull the points across - the 1 at the end means its looking for the nearest lowest value if it cannot find an exact match

    the ranking will show duplicates if the points are the same - you maybe able to use something else to differentiate between those rankings -

    see attached = points lookup in sheet called point - and so you can make any points you wish for any percent

    Re: How to exclude zero values from the chart!

    you may need VBA to sum the column and if 0 move onto the next month and then re-populate an area of the spreadsheet just for the graph

    as you do not want to include a column on the chart range of all the data in that column is zero

    Re: Extracting Post Code and Locality

    not a great solution , but may help while waiting for others to provide a better one

    i found a formula which will extract the 5 digit number from a text string
    and then modified to extract the number and all the text after the number
    (LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)

    so a couple of issues
    1) are all post code only 5 digitis ?
    2) would any of the numbers at the start of the text house number ever be 5 numbers- if so this will not work

    i'm sure others will offer a better solution

    I then use find with the above formula to find the position of that text - add to a MID function so that the MID function will start with that character
    and then use length - the position of that formula +1 to get the length of characters to extract from the start position of the 5 digit number

    =MID(A1,FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1),LEN(A1)-FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)+1)
    use control+shift+enter to use as an array and get {} around the formula

    Re: Calculating hour average from 5min. timesteps

    just change the group by on the pivot table to include Days and Hours - click on days and hours - both should be selcted and you should get

    something simular to your example - only the date will be entered once in the column and then all the times will follow in next column until a day change

    11/23/2008 08:00 78,58
    ............... 09:00 94,92
    ............... 10:00 11,11
    11/24/2008 15:00 12,11
    ............... 16:00 11,34
    11/25/2008 08:00 78,58
    ............... 09:00 94,92
    ............... 10:00 11,11


    Re: Vlookup help

    no match with the lookup or no value when it finds the lookup but no value to return

    if no match - it will return a #n/a

    and you can use
    =IFERROR( the formula , error message)


    =IFERROR(VLOOKUP(J17,$L$1:$M$44,2), 1)
    would return a 1 if no lookup found

    1 is not a null value by the way - a NULL is no data in a field which is also different to a blank cell just FYI

    Re: Vlookup help

    should work
    try a index match

    =index( M:M, (match( J17, L:L,0))

    can you attach a spreadsheet - remember this is a public forum so no sensitive data

    Re: Countif/Sum Product

    seems to be working OK
    ROW 14
    G14 =1 header = 7 so 7
    H14 =1 header =10 so 10 = 10+7 = 17
    N14 = X an so i R14 that would be 1x2 = 2

    S14 = sumproduct - 10x1 + 7x1 = 17 then + R14 =2 =19