Universal formula to all cells

[Frodo Baggins.]

Hello,
I have a grid of numbers with totals at the end of each column.
I'd like to convert the numbers in the grid to percentages of the total.
Is there a way to do this without changing the content/formula of each
individual cell?

Thanks!

FB.

 

[Pete_UK]

I think you would have to do it in a separate (adjacent) grid. Suppose
you currently have your grid occupying cells A1:F20, with your totals
in row in 21, then in cell H1 you could enter this formula:

=A1/SUM($A$21:$F$21)

and format this as percentage. Then copy the formula into cells I1:M1.
Then highlight H1:M1 and copy down to row 20.

Hope this helps.

Pete

 

[Frodo Baggins.]

Thanks Pete...thought there'd be a function like CELLCONTENT or something
that I could apply with an anchored ($A$1) cell for the math function.

Thanks again!

FB.

 

[Pete_UK]

Well, you could try looking for that in XL Help, but I don't think you
will find it. <bg>

Pete

 

[Gord Dibben]

Copy the totals cell at end of each column.

Paste special>Value.

Copy that cell.

Select the cells above and Paste Special>Divide>OK>Esc.

Format to percentage.


Gord Dibben MS Excel MVP

 

[Teal CPA]

Actually - years ago there was an add-on that had a feature (function) where
you could apply a formula to a range of cells - for example if you had a
column of numbers that you wanted to change from positive to negative numbers
you would only need to highlight the column, bring up the universal feature
and put in the formula you wanted applied to those numbers (*-1), and the
whole column would be negative... in your case the universal formula would be
(/100) and then apply the percent format.... I've not been able to find that
wonderful add on for quite awhile... maybe someone knows where it is.

 

[MyVeryOwnSelf]

I have a grid of numbers with totals at the end of each column.

I'd like to convert the numbers in the grid to percentages of the total.
Is there a way to do this without changing the content/formula of each
individual cell?

You can use a separate worksheet.

If the original grid is Sheet1!A1:G20 and the totals are in row 22 of
Sheet1, put this in Sheet2!A1
=Sheet1!A1/Sheet1!A$22
Then extend that formula to all of Sheet2!A1:G20.