Formulas : help i'm stuck!!

    i'm working on a spreadsheet and am desperate to sort out the following problem.


    two columns of numbers as follows:


    Rate Amount


    0.12 49250
    0.00 49250
    0.00 49550
    0.00 0
    0.00 0


    I've got the rate in order from highest to lowest ie


    Rate Amount


    0.00 ?
    0.00 ?
    0.00 ?
    0.00 ?
    0.12 ?


    Now need to get the amount next to each, so total amount in this case will be 148,050, split as above.


    Numbers won't always be like this, eg sometimes will have each rate & amounts different, but needs to be able to cope with the above.


    I've tried vlookup, but doesn't seem to work with the 0.00's having different amount values?


    ANY help would be much appreciated,
    Thanks

    Welcome to the OzGrid Forum!


    I don't understand what you are trying to do. Can you give some more detail or examples? Are you starting with a total amount and need to split it somehow? What role do the rates play? Only one of the rates was not 0.


    Perhaps you could give the starting situation, and tehn what you would like the finished result to be. Use the free HTML Add-in to show a portion of a spreadsheet if that would help explain the problem. You can download it from http://www.ozgrid.com/forum/viewthread.php?tid=3167

    Thanks for your reply.


    At present I have the two columns like this


    Rate Amount


    0.12 49250
    0.00 49250
    0.00 49550
    0.00 0
    0.00 0


    My aim is to get the rate in order from the lowest - highest with the corresponding amount next to it.


    I've got the rate from low to high using the small formula
    so it looks like:

    Rate


    0.00
    0.00
    0.00
    0.00
    0.12


    Now i've got to get the relevant amount next to each of these rates, so that it looks like this


    Rate Amount


    0.00 0
    0.00 0
    0.00 49250
    0.00 49550
    0.12 49250


    is there any way of doing this?
    many thanks

    You can sort the data. Select the table and go to the data menu and select sort. Then sort ascending on the first column. The second column will automatical stay aligned in the result. Is there some reason why you were trying to do it with formulas?

    back again!!


    I've tried to use data sort, but it doesn't work - I can only guess that it's something to do with the formulas in the cells which may be mucking up the sort?
    Any ideas?
    Thanks

    Formulas shouldn't bother the sort unless they are using relative references to values outside their row. The relative references will still remain relative, so if the cell with the formula moves the cell its pointing to with a relative reference will be a different cell. To put it another way, sorting treats relative references like Copy, not like Cut.

    yes, that's exactly what is happening, the values are based on other formulas elsewhere in the s/sheet. Is they any way around this or alternative methods i could use?
    thanks

    On what basis are you deciding which 0.00 has ended up where in the sorted list? For example is:


    Rate Amount
    0.00 0
    0.00 49250
    0.00 0
    0.00 49550
    0.12 49250


    any less correct than the end game in your example? Or, are there more decimal places just hidden?


    If the 'Amount' is unique to a 'Rate' value you could try using:


    =INDEX([origional 'Amount' range],MATCH([new corresponding 'Rate' value],[origional rate range],0),1)

    thanks for your answers, i'll try these
    don;t mean to sound daft, but how do i change to an absolute reference from a relative reference?
    Thanks

    Hi resssc10


    A1 is a Relative reference


    $A1 is a Relative Row, Absolute Column reference


    A$1 is a Relative Column , Absolute Row reference


    $A$1 is a Absolute reference


    You can cycle through these easily by selecting anywhere within the reference via the Formula Bar and pushing F4

    Hello,
    back again!!


    I've used the sort feature which works great, however, I need it to automatically put the list into order. Using the sort feature, at present i need to manually sort each time new figures are used.


    Is there a way to organise this so that excel automatically puts the figures into asecending order, even when the figures change?
    Thanks for your help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login