Posts by jindon

    Hi,


    Insert one column anywhere you like, say col.C, and put


    in cell C6
    =IF(MOD(ROW(A1),4)=1,B6-B5,"")


    then drag down as you want



    Second problem


    In Cell B5
    =IF(AND(B5<>"",B6<>""),B6,"")
    In cell B6
    =IF(AND(B5<>"",B6<>""),B6-B5,"")


    then drag down both cells


    hope this works

    Hi,
    Here's how it works.


    Formula in col.C on Totals Sheet is to find the rank among "EVAL TOTALS", col.B
    and I thought this would be the original ranking.
    =IF(B2<>"",RANK(B2,B:B),"")
    One problem!
    Function RANK will rank the same if the test value is the same.
    When it happens, function SMALL will always return the first comer only.
    To avoid this problem, I always use another working column which adds or subtracts ROW(A1)/100 to or from the test value.
    It really avoids this problem. (The attached has been modified already, ref col.G and formula in co.C is now =IF(B2<>"",RANK(G2,G:G),"") )


    Formula in col.L on sheet Shift Input
    =IF(ISERROR(MATCH(C10,TOTALS!A:A,0)),"",INDEX(TOTALS!A:C,MATCH(C10,TOTALS!A:A,0),3))
    It finds the original ranking of the value in col.C from Totals sheet


    Formula in Col.F on sheet Shit Input
    =IF(C10="","",INDEX(TOTALS!A:C,MATCH(SMALL(L:L,ROW(A1)),TOTALS!C:C,0),1))
    It finds the first smallest value in col.L for the first row and find ‚ƒ‚‚’‚’‚…‚“‚‚‚Ž‚„‚‰‚Žg value in col.A on Totals sheet and second smallest for the second row and so forth.


    Try the attached and compare with the other one

    In cell M1


    =IF(ROW(A1)<=COUNT($A$1:$A$1000),INDIRECT("A"&SMALL(IF($A$1:$A$1000<>0,ROW($A$1:$A$1000)),ROW(A1))),"")


    holding down Ctrl+Shift and Enter to get out from the formula bar


    then drag down to row 1000th


    I'm not sure if this is what you want.


    Or,


    VBA, try attached.
    with Sheet1
    When value of last row of col.A changes, the value appear in the last row of col.M.


    Cell N1 has bbeen used as working cell.


    hope it works

    Using Index+Match


    Table must in descending order
    i.e. B1=30, C1=20, D1=10


    =INDEX(B1:D1,1,MATCH(A1,B1:D1,-1))


    returns larger value than the test value in the list

    Hi, Tanis


    When A1 changes, same value should be posted to the first available cell in col.M.
    Then A2 changes, same value to the next cell in col. M
    Is it possible A1 changes again?
    What would you like to do when it happens?

    Other solution


    Select the range
    Choose Edit - Copy
    Holding down Shift key and choose Edit - Paste Picture Link


    Then you can move the linked picture on the top row and freeze pane.


    Jindon

    Hi,


    First of all, at this end, it doesn't sort like you desire even I change the format from number to text. The only way I can get sorted that way is what I mentioned above and it doesn't matter if the values in column A are formatted as number or text.


    Anyway, if you want convert numbers in column A to text in column B is as follow


    1) Copy columnA and select columnB then pastespecial and check Values then OK
    2) Right click and select cell format then change from General to Text


    Alternatively, the given formula in cell B1
    =TEXT(A1,"@")
    changes value to Text from numbers
    If you put =ISTEXT(B1) returns TRUE means Text already.


    I hope you can get what you wish,


    Jindon

    Using working column:
    If the data is in column A
    B1:
    =--LEFT(A1,1)
    fildown to the last row of col. A


    then sort by column b as first criteria and a for second.


    Hope this does good for you.