If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Universal formula to all cells
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. |
#2
|
|||
|
|||
Universal formula to all cells
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 On Mar 26, 3:09*pm, Frodo Baggins. wrote: 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. |
#3
|
|||
|
|||
Universal formula to all cells
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" wrote: 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 On Mar 26, 3:09 pm, Frodo Baggins. wrote: 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. |
#4
|
|||
|
|||
Universal formula to all cells
Well, you could try looking for that in XL Help, but I don't think you
will find it. bg Pete On Mar 26, 4:23*pm, Frodo Baggins. wrote: 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" wrote: 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 On Mar 26, 3:09 pm, Frodo Baggins. wrote: 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.- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Universal formula to all cells
Copy the totals cell at end of each column.
Paste specialValue. Copy that cell. Select the cells above and Paste SpecialDivideOKEsc. Format to percentage. Gord Dibben MS Excel MVP On Wed, 26 Mar 2008 08:09:07 -0700, Frodo Baggins. wrote: 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. |
#6
|
|||
|
|||
Universal formula to all cells
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. "Frodo Baggins." wrote: 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. |
#7
|
|||
|
|||
Universal formula to all cells
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. |
Thread Tools | |
Display Modes | |
|
|