Posts by etaf

  • Compare actual grade to potential grade and highlight cells accordingly

    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
    =LOOKUP(A11,{"A","B","C","S"},{2,3,4,1})
    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


    EDIT
    if you putin alpha order based on first character it will work with the array
    so
    =LOOKUP(A11,{"A","A/B","B","C","C/D","D","S"},{1,10,2,3,11,4,100})
    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

  • assigning points to percentages

    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
    =INDEX(Point!$B$3:$B$20,MATCH(B3,Point!$A$3:$A$20,1))
    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

  • How to exclude zero values from the chart!

    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

  • Extracting Post Code and Locality

    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

  • Calculating hour average from 5min. timesteps

    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


    [/CODE]

  • Vlookup help

    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)


    so


    =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

  • Vlookup help

    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

  • Countif/Sum Product

    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