 # Formulas : help i&#039;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

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?

• none, except for the fact that i'm an idiot!!
thanks for your help - much appreicated

• 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)

• To keep your formulas working correctly after the sort change the relative references in them (that point outside the list) to absolute references.

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

• FANTASTIC, IT WORKS LIKE A DREAM NOW.
THANKS A LOT

• 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?