Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Percentage calculation from cell to row
I want to be able to type a certain percent in cell I4 and have it re-calculate what is already there in column F. So right now it say's 90%. In the first item in column F, which is F10, the formula bar reads =+SUM(E10*I4). So, if I type 95% and press enter it raises the dollar amount to 95% of the retail price. Basically I need to type a percent and press enter in a cell that calculates an entire column that's based another column that is the retail price! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Percentage calculation from cell to row
If your formula in a cell is =Sum(E10*I4) then your spreadsheet will automatically update any time you change either (or both) E10 or I4. If you need all cells to update based on a single percentage, your formula in every cell that depends on the percentage needs to reference the percentage cell. OK, theory done, let's do something with it: In the cell that now contains the formula =+SUM(E10*I4), replace it with . =SUM(E10*$I$4). {NB the '+' symbol in your formula before is unnecessary.} You can now copy this straight in the cell below and it will change to =SUM(E11*$I$4). Copy the formula down as far as you need. The other way would be to name the percentage cell Click on your cell that contains the percentage. Click on the name box - see diagram - and type a meaningful name (example pctReduction) http://spreadsheets.about.com/od/glo...me_box_def.htm change your formula to reference your named cell: =SUM(E10*pctReduction). Back to theory: Some reading that may help you understand the difference between relative and absolute references: http://office.microsoft.com/en-us/ex...CH010036991033 see the reference "The difference between relative and absolute references" -- Steve "Jameslee77" wrote in message ... I want to be able to type a certain percent in cell I4 and have it re-calculate what is already there in column F. So right now it say's 90%. In the first item in column F, which is F10, the formula bar reads =+SUM(E10*I4). So, if I type 95% and press enter it raises the dollar amount to 95% of the retail price. Basically I need to type a percent and press enter in a cell that calculates an entire column that's based another column that is the retail price! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Percentage calculation from cell to row
Thanks, that was very helpful! Here's my next question. Rather than copy and paste into every cell descending. Is there a way I can assign this function to an array of cells in the column? If so, does it involve the VBA editor like an "if" statement or something? "AltaEgo" wrote: If your formula in a cell is =Sum(E10*I4) then your spreadsheet will automatically update any time you change either (or both) E10 or I4. If you need all cells to update based on a single percentage, your formula in every cell that depends on the percentage needs to reference the percentage cell. OK, theory done, let's do something with it: In the cell that now contains the formula =+SUM(E10*I4), replace it with . =SUM(E10*$I$4). {NB the '+' symbol in your formula before is unnecessary.} You can now copy this straight in the cell below and it will change to =SUM(E11*$I$4). Copy the formula down as far as you need. The other way would be to name the percentage cell Click on your cell that contains the percentage. Click on the name box - see diagram - and type a meaningful name (example pctReduction) http://spreadsheets.about.com/od/glo...me_box_def.htm change your formula to reference your named cell: =SUM(E10*pctReduction). Back to theory: Some reading that may help you understand the difference between relative and absolute references: http://office.microsoft.com/en-us/ex...CH010036991033 see the reference "The difference between relative and absolute references" -- Steve "Jameslee77" wrote in message ... I want to be able to type a certain percent in cell I4 and have it re-calculate what is already there in column F. So right now it say's 90%. In the first item in column F, which is F10, the formula bar reads =+SUM(E10*I4). So, if I type 95% and press enter it raises the dollar amount to 95% of the retail price. Basically I need to type a percent and press enter in a cell that calculates an entire column that's based another column that is the retail price! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Percentage calculation from cell to row
There are shortcuts to copy down to every cell adjacent filled cells. Click on the cell containing the formula. In the lower right-hand corner you will see a small square - the fill handle. You can grab the fill handle with your mouse and drag it down to copy formula OR (My favourite shortcut) you can double-click the fill handle and Excel will do the work for you. Fill handle basics: http://spreadsheets.about.com/od/f/g...handle_def.htm Flash Video http://communityclips.officelabs.com...9-fc9bb2b57366 http://www.vimeo.com/3948458 -- Steve "Jameslee77" wrote in message ... Thanks, that was very helpful! Here's my next question. Rather than copy and paste into every cell descending. Is there a way I can assign this function to an array of cells in the column? If so, does it involve the VBA editor like an "if" statement or something? "AltaEgo" wrote: If your formula in a cell is =Sum(E10*I4) then your spreadsheet will automatically update any time you change either (or both) E10 or I4. If you need all cells to update based on a single percentage, your formula in every cell that depends on the percentage needs to reference the percentage cell. OK, theory done, let's do something with it: In the cell that now contains the formula =+SUM(E10*I4), replace it with . =SUM(E10*$I$4). {NB the '+' symbol in your formula before is unnecessary.} You can now copy this straight in the cell below and it will change to =SUM(E11*$I$4). Copy the formula down as far as you need. The other way would be to name the percentage cell Click on your cell that contains the percentage. Click on the name box - see diagram - and type a meaningful name (example pctReduction) http://spreadsheets.about.com/od/glo...me_box_def.htm change your formula to reference your named cell: =SUM(E10*pctReduction). Back to theory: Some reading that may help you understand the difference between relative and absolute references: http://office.microsoft.com/en-us/ex...CH010036991033 see the reference "The difference between relative and absolute references" -- Steve "Jameslee77" wrote in message ... I want to be able to type a certain percent in cell I4 and have it re-calculate what is already there in column F. So right now it say's 90%. In the first item in column F, which is F10, the formula bar reads =+SUM(E10*I4). So, if I type 95% and press enter it raises the dollar amount to 95% of the retail price. Basically I need to type a percent and press enter in a cell that calculates an entire column that's based another column that is the retail price! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage Calculation | Excel Worksheet Functions | |||
Percentage calculation | Excel Discussion (Misc queries) | |||
percentage calculation | Excel Discussion (Misc queries) | |||
'of' percentage with calculation | Excel Discussion (Misc queries) | |||
Percentage Calculation | Excel Worksheet Functions |