|
I have almost 25,000 rows of data and I'm a little scared LOL
Firstly I'm a total newb to excel (and access for that matter LOL)
I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad |
I have almost 25,000 rows of data and I'm a little scared LOL
try this which does the formula and removes it
Sub addformulasandtakeaway() x=cells(rows.count,"a").end(xlup).row Set frng = Range("e2:e" & x) With frng .Formula = "=c2/d2" .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "aaaa" wrote in message ... Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad |
I have almost 25,000 rows of data and I'm a little scared LOL
Thanks for the fast reply Don :)
LOL where should enter this? does it matter what cell is selected? I thought I would be able to make out what I need to change to make this work for my sheet, but I was WAY wrong LOL I should have been more specific sorry :( total is in column C and Passed is column G and would like the % in column J what would I need to change to make this work? "Don Guillett" wrote in message ... try this which does the formula and removes it Sub addformulasandtakeaway() x=cells(rows.count,"a").end(xlup).row Set frng = Range("e2:e" & x) With frng .Formula = "=c2/d2" .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "aaaa" wrote in message ... Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad |
I have almost 25,000 rows of data and I'm a little scared LOL
Select cell E2, and enter the formula: =C2/B2
Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
I have almost 25,000 rows of data and I'm a little scared LOL
You would probably find it faster to locate the last row, ctrl+End
should help with that. then after entering the formula into E2 as described or something that ignores division by zero you could copy it down by typing E2:E25000 (if that is then last cell wanted) then use the shortcut Ctrl+D More information on use of the fill handle and use of Ctrl+D http://www.mvps.org/dmcritchie/excel/fillhand.htm If Ctrl+End took you way beyond your actual data you might want to fix that see the macro (don't do it manually) at Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish http://www.contextures.com/xlfaqApp.html#Unused As far as the macro supplied earlier, if you still don't know how to install a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm you would need to install a macro if your last cell is way beyond your data and you want to use a macro to fix all sheets at once. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Debra Dalgleish" wrote in message ... Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
I have almost 25,000 rows of data and I'm a little scared LOL
On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
wrote: Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. dragging 25,000 is a real pain click on e2 Ctrl+C to copy this cell Ctrl+End will take you down to the bottom corner Click on the last cell in E that you want the formula in Hold Shift Ctrl+Home will take you back to to the top, use the cursor to move *top* of this block to e3 Paste in the formula - Ctrl+V Messy, but after a while that sort of Block selection gets easier, whereas I never get used to dragging 25,000 lines ! aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad -- Steve |
I have almost 25,000 rows of data and I'm a little scared LOL
should read
then after entering the formula into E2: =C2/B2 and then typing E2:E25000 into the name box to the left of the address bar then use the shortcut Ctrl+D -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David McRitchie" wrote in message ... You would probably find it faster to locate the last row, ctrl+End should help with that. then after entering the formula into E2 as described or something that ignores division by zero you could copy it down by typing E2:E25000 (if that is then last cell wanted) then use the shortcut Ctrl+D More information on use of the fill handle and use of Ctrl+D http://www.mvps.org/dmcritchie/excel/fillhand.htm If Ctrl+End took you way beyond your actual data you might want to fix that see the macro (don't do it manually) at Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish http://www.contextures.com/xlfaqApp.html#Unused As far as the macro supplied earlier, if you still don't know how to install a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm you would need to install a macro if your last cell is way beyond your data and you want to use a macro to fix all sheets at once. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Debra Dalgleish" wrote in message ... Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
I have almost 25,000 rows of data and I'm a little scared LOL
Re-read Debra's post. There's no dragging involved, just one double-click!
-- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish wrote: Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. dragging 25,000 is a real pain click on e2 Ctrl+C to copy this cell Ctrl+End will take you down to the bottom corner Click on the last cell in E that you want the formula in Hold Shift Ctrl+Home will take you back to to the top, use the cursor to move *top* of this block to e3 Paste in the formula - Ctrl+V Messy, but after a while that sort of Block selection gets easier, whereas I never get used to dragging 25,000 lines ! aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad |
I have almost 25,000 rows of data and I'm a little scared LOL
"SteveW" wrote in message ... On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish wrote: Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. dragging 25,000 is a real pain click on e2 Ctrl+C to copy this cell Ctrl+End will take you down to the bottom corner Click on the last cell in E that you want the formula in Hold Shift Ctrl+Home will take you back to to the top, use the cursor to move *top* of this block to e3 Paste in the formula - Ctrl+V Messy, but after a while that sort of Block selection gets easier, whereas I never get used to dragging 25,000 lines ! But it is hardly faster than double clicking lower right corner of E2 as Debra suggested? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon |
I have almost 25,000 rows of data and I'm a little scared LOL
I'm intrigued how anything could be faster than one double-click in the top
cell... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... You would probably find it faster to locate the last row, ctrl+End should help with that. then after entering the formula into E2 as described or something that ignores division by zero you could copy it down by typing E2:E25000 (if that is then last cell wanted) then use the shortcut Ctrl+D More information on use of the fill handle and use of Ctrl+D http://www.mvps.org/dmcritchie/excel/fillhand.htm If Ctrl+End took you way beyond your actual data you might want to fix that see the macro (don't do it manually) at Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish http://www.contextures.com/xlfaqApp.html#Unused As far as the macro supplied earlier, if you still don't know how to install a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm you would need to install a macro if your last cell is way beyond your data and you want to use a macro to fix all sheets at once. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Debra Dalgleish" wrote in message ... Select cell E2, and enter the formula: =C2/B2 Format the cell as Percent With cell E2 selected, point to the Fill Handle -- the small black square at the bottom right of the selection. When the pointer changes to a small black plus sign, double-click, to fill the formula down to the first blank row. aaaa wrote: Firstly I'm a total newb to excel (and access for that matter LOL) I have almost 25,000 rows of data and I'm a little scared LOL its a basic set up, Total, Passed, Rejected ect ect across the top with the info below. example... Date Total Passed Rejected 1/2/05 123 100 23 1/4/05 324 320 4 1/6/05 97 96 1 I need a fifth column showing a percentage of passed. but I don't know how to do this with out entering the formula 25 thousand times :-( Please help Also would really love to be pointed towards an easy to understand instruction site for access. Thank you all, Chad -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
I have almost 25,000 rows of data and I'm a little scared LOL
Okay Peo and Tushar, missed that it was a double click, but whenever
using that you do want to verify that you covered the range as it will stop if there is an empty cell to the left (in this case). |
| All times are GMT +1. The time now is 05:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com