Excel again

[wb]

Pretty sure this was asked before but I can't find it.

I'm converting numbers to percentages, so column A has the numbers (They are totaled in Cell A20. )

Now, in column B, I want to express each number as a percentage.

So in B1, I have =sum(A1/A20*100). This is fine. But when I drag to copy the formula, it goes =sum(A2/A21*100).

How do I drag it so that it goes A1,A2,A3 etc while maintaining the A21 in each formula?

[mathare]

Put a $ in front of the 21 to use an absolute reference (it then won't change when you drag down), i.e. =sum(A2/A$21*100)

Similarly you can put a $ in front of the A, i.e. =sum($A2/$A21*100) such that were you to copy this formula across columns it would always use data from column A

[wb]

Ah yes - Thanks Mat, great help as usual.

[lowe1]

Wayne

You have email

I can't answer your question myself but have sent the ebook excel for dummies hope that helps with any excel queries

[scoobydoo]

Just beat me to it Mat :laugh In fact I did think I knew this one,but wasn't totally sure. :ermmm

[wb]

One more question!

If I have a set of numbers, how do I get it to highlight (or change the colour) of the highest number?

[mathare]

Check the Excel help on 'conditional formatting' - exactly how it worlks varies from version to version so best using the in-built help here

[wb]

Figured it out - just in case anyone wants to know (excel 2007).

Highlight Cells
Conditional Formatting
Top/Bottom Rules
Top Ten Items
Change Value to 1

[scoobydoo]

One more question!

If I have a set of numbers, how do I get it to highlight (or change the colour) of the highest number?

That is about the only thing I can do I think. :rolleyes: