excel question
 

Could someone please help with a formula please? I want to find the highest value in a column and convert that figure to 100 and then have all the other numbers in that column converted in proportion to the first figure if you get my drift. For example if column a has numbers 20 50 30 10 then i would like the 50 to change to 100 and then have the others convert by the same percentage so that the new figures would read 40 100 60 20 hope I've made it clear enough. thanks in advance.

In cell B1...
=A1*2

Thanks for the reply CP but wouldn't that just double each number? the numbers I used were only examples they could be something along the lines of 45 59 78 53 27 I would then need a formula to first of all find the highest number in column a (78) then convert to 100 then multiply all the other numbers by whatever 78 was to change it to 100. so the final numbers would be close to 57 75 100 68 35

o.k. yes I see your conundrum.

There are other ways to do it, but just quickly in column B enter =MAX(A:A) and copy it down as far as you want.

In cell C1 type =IF(A1=B1,100,(A1/B1*100)) and copy it down the page.

Thanks Chrome exactly what I was after.

Actually, you can do without the formula in column B altogether and just type this in cell B1 and copy it down.

=IF(A1=MAX(A:A),100,(A1/MAX(A:A)*100))

Better ;)

Or even better...

=IF(A1=MAX(A:A),(MAX(A:A)/MAX(A:A))*100,(A1/MAX(A:A)*100))

:D

=100*A1/MAX(A:A)

replicated in Column B

where Column A has input numbers

is shorter and probably correct