software

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
try this which does the formula and removes it
Sub addformulasandtakeaway()
x=cells(rows.count,quot;aquot;).end(xlup).row
Set frng = Range(quot;e2:equot; amp; x)
With frng
.Formula = quot;=c2/d2quot;
.Formula = .Value
End With
End Sub

--
Don Guillett
SalesAid Software

quot;aaaaquot; gt; wrote in message
...
gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt;
gt; its a basic set up, Total, Passed, Rejected ect ect across the top with
gt; the info below.
gt;
gt; example...
gt;
gt; Date Total Passed Rejected
gt; 1/2/05 123 100 23
gt; 1/4/05 324 320 4
gt; 1/6/05 97 96 1
gt;
gt; I need a fifth column showing a percentage of passed.
gt; but I don't know how to do this with out entering the formula 25 thousand
gt; times :-(
gt;
gt; Please help
gt;
gt; Also would really love to be pointed towards an easy to understand
gt; instruction site for access.
gt;
gt; Thank you all,
gt;
gt; Chad
gt;
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?
quot;Don Guillettquot; gt; wrote in message
...
gt; try this which does the formula and removes it
gt; Sub addformulasandtakeaway()
gt; x=cells(rows.count,quot;aquot;).end(xlup).row
gt; Set frng = Range(quot;e2:equot; amp; x)
gt; With frng
gt; .Formula = quot;=c2/d2quot;
gt; .Formula = .Value
gt; End With
gt; End Sub
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;aaaaquot; gt; wrote in message
gt; ...
gt;gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt;gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt;gt;
gt;gt; its a basic set up, Total, Passed, Rejected ect ect across the top with
gt;gt; the info below.
gt;gt;
gt;gt; example...
gt;gt;
gt;gt; Date Total Passed Rejected
gt;gt; 1/2/05 123 100 23
gt;gt; 1/4/05 324 320 4
gt;gt; 1/6/05 97 96 1
gt;gt;
gt;gt; I need a fifth column showing a percentage of passed.
gt;gt; but I don't know how to do this with out entering the formula 25 thousand
gt;gt; times :-(
gt;gt;
gt;gt; Please help
gt;gt;
gt;gt; Also would really love to be pointed towards an easy to understand
gt;gt; instruction site for access.
gt;gt;
gt;gt; Thank you all,
gt;gt;
gt;gt; Chad
gt;gt;
gt;
gt;
The modification of the macro should be fairly obvious.
Modifygt;put it in a module and execute from alt f8 or assign to a button.

--
Don Guillett
SalesAid Software

quot;aaaaquot; gt; wrote in message
...
gt; Thanks for the fast reply Don
gt;
gt; LOL where should enter this?
gt; does it matter what cell is selected?
gt; I thought I would be able to make out what I need to change to make this
gt; work for my sheet, but I was WAY wrong LOL
gt; I should have been more specific sorry
gt; total is in column C and Passed is column G and would like the % in column
gt; J
gt; what would I need to change to make this work?
gt;
gt;
gt;
gt; quot;Don Guillettquot; gt; wrote in message
gt; ...
gt;gt; try this which does the formula and removes it
gt;gt; Sub addformulasandtakeaway()
gt;gt; x=cells(rows.count,quot;aquot;).end(xlup).row
gt;gt; Set frng = Range(quot;e2:equot; amp; x)
gt;gt; With frng
gt;gt; .Formula = quot;=c2/d2quot;
gt;gt; .Formula = .Value
gt;gt; End With
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;aaaaquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt;gt;gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt;gt;gt;
gt;gt;gt; its a basic set up, Total, Passed, Rejected ect ect across the top with
gt;gt;gt; the info below.
gt;gt;gt;
gt;gt;gt; example...
gt;gt;gt;
gt;gt;gt; Date Total Passed Rejected
gt;gt;gt; 1/2/05 123 100 23
gt;gt;gt; 1/4/05 324 320 4
gt;gt;gt; 1/6/05 97 96 1
gt;gt;gt;
gt;gt;gt; I need a fifth column showing a percentage of passed.
gt;gt;gt; but I don't know how to do this with out entering the formula 25
gt;gt;gt; thousand times :-(
gt;gt;gt;
gt;gt;gt; Please help
gt;gt;gt;
gt;gt;gt; Also would really love to be pointed towards an easy to understand
gt;gt;gt; instruction site for access.
gt;gt;gt;
gt;gt;gt; Thank you all,
gt;gt;gt;
gt;gt;gt; Chad
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
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:
gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt;
gt; its a basic set up, Total, Passed, Rejected ect ect across the top with the
gt; info below.
gt;
gt; example...
gt;
gt; Date Total Passed Rejected
gt; 1/2/05 123 100 23
gt; 1/4/05 324 320 4
gt; 1/6/05 97 96 1
gt;
gt; I need a fifth column showing a percentage of passed.
gt; but I don't know how to do this with out entering the formula 25 thousand
gt; times :-(
gt;
gt; Please help
gt;
gt; Also would really love to be pointed towards an easy to understand
gt; instruction site for access.
gt;
gt; Thank you all,
gt;
gt; Chad
gt;
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlYou 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
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
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 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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Debra Dalgleishquot; gt; wrote in message ...
gt; Select cell E2, and enter the formula: =C2/B2
gt; Format the cell as Percent
gt; With cell E2 selected, point to the Fill Handle -- the small black
gt; square at the bottom right of the selection.
gt; When the pointer changes to a small black plus sign, double-click,
gt; to fill the formula down to the first blank row.
gt;
gt; aaaa wrote:
gt; gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt; gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt; gt;
gt; gt; its a basic set up, Total, Passed, Rejected ect ect across the top with the
gt; gt; info below.
gt; gt;
gt; gt; example...
gt; gt;
gt; gt; Date Total Passed Rejected
gt; gt; 1/2/05 123 100 23
gt; gt; 1/4/05 324 320 4
gt; gt; 1/6/05 97 96 1
gt; gt;
gt; gt; I need a fifth column showing a percentage of passed.
gt; gt; but I don't know how to do this with out entering the formula 25 thousand
gt; gt; times :-(
gt; gt;
gt; gt; Please help
gt; gt;
gt; gt; Also would really love to be pointed towards an easy to understand
gt; gt; instruction site for access.
gt; gt;
gt; gt; Thank you all,
gt; gt;
gt; gt; Chad
gt; gt;
gt; gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
gt; wrote:

gt;Select cell E2, and enter the formula: =C2/B2
gt;Format the cell as Percent
gt;With cell E2 selected, point to the Fill Handle -- the small black
gt; square at the bottom right of the selection.
gt;When the pointer changes to a small black plus sign, double-click,
gt; 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 !

gt;gt;aaaa wrote:
gt;gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt;gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt;gt;
gt;gt; its a basic set up, Total, Passed, Rejected ect ect across the top with the
gt;gt; info below.
gt;gt;
gt;gt; example...
gt;gt;
gt;gt; Date Total Passed Rejected
gt;gt; 1/2/05 123 100 23
gt;gt; 1/4/05 324 320 4
gt;gt; 1/6/05 97 96 1
gt;gt;
gt;gt; I need a fifth column showing a percentage of passed.
gt;gt; but I don't know how to do this with out entering the formula 25 thousand
gt;gt; times :-(
gt;gt;
gt;gt; Please help
gt;gt;
gt;gt; Also would really love to be pointed towards an easy to understand
gt;gt; instruction site for access.
gt;gt;
gt;gt; Thank you all,
gt;gt;
gt;gt; Chad
gt;gt;
gt;gt;

--

Steve

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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;David McRitchiequot; gt; wrote in message ...
gt; You would probably find it faster to locate the last row, ctrl End
gt; should help with that. then after entering the formula into E2
gt; as described or something that ignores division by zero you
gt; could copy it down by typing E2:E25000 (if that is then last cell wanted)
gt; then use the shortcut Ctrl D
gt;
gt; More information on use of the fill handle and use of Ctrl D
gt; www.mvps.org/dmcritchie/excel/fillhand.htm
gt;
gt; If Ctrl End took you way beyond your actual data you might want to
gt; fix that see the macro (don't do it manually) at
gt; Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
gt; www.contextures.com/xlfaqApp.html#Unused
gt;
gt; As far as the macro supplied earlier, if you still don't know how to install
gt; a macro see www.mvps.org/dmcritchie/excel/getstarted.htm
gt; you would need to install a macro if your last cell is way beyond your
gt; data and you want to use a macro to fix all sheets at once.
gt; ---
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Debra Dalgleishquot; gt; wrote in message ...
gt; gt; Select cell E2, and enter the formula: =C2/B2
gt; gt; Format the cell as Percent
gt; gt; With cell E2 selected, point to the Fill Handle -- the small black
gt; gt; square at the bottom right of the selection.
gt; gt; When the pointer changes to a small black plus sign, double-click,
gt; gt; to fill the formula down to the first blank row.
gt; gt;
gt; gt; aaaa wrote:
gt; gt; gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt; gt; gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt; gt; gt;
gt; gt; gt; its a basic set up, Total, Passed, Rejected ect ect across the top with the
gt; gt; gt; info below.
gt; gt; gt;
gt; gt; gt; example...
gt; gt; gt;
gt; gt; gt; Date Total Passed Rejected
gt; gt; gt; 1/2/05 123 100 23
gt; gt; gt; 1/4/05 324 320 4
gt; gt; gt; 1/6/05 97 96 1
gt; gt; gt;
gt; gt; gt; I need a fifth column showing a percentage of passed.
gt; gt; gt; but I don't know how to do this with out entering the formula 25 thousand
gt; gt; gt; times :-(
gt; gt; gt;
gt; gt; gt; Please help
gt; gt; gt;
gt; gt; gt; Also would really love to be pointed towards an easy to understand
gt; gt; gt; instruction site for access.
gt; gt; gt;
gt; gt; gt; Thank you all,
gt; gt; gt;
gt; gt; gt; Chad
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Debra Dalgleish
gt; gt; Excel FAQ, Tips amp; Book List
gt; gt; www.contextures.com/tiptech.html
gt; gt;
gt;
gt;
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 gt;,
says...
gt; On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
gt; gt; wrote:
gt;
gt; gt;Select cell E2, and enter the formula: =C2/B2
gt; gt;Format the cell as Percent
gt; gt;With cell E2 selected, point to the Fill Handle -- the small black
gt; gt; square at the bottom right of the selection.
gt; gt;When the pointer changes to a small black plus sign, double-click,
gt; gt; to fill the formula down to the first blank row.
gt;
gt; dragging 25,000 is a real pain
gt; click on e2
gt; Ctrl C to copy this cell
gt;
gt; Ctrl End will take you down to the bottom corner
gt; Click on the last cell in E that you want the formula in
gt; Hold Shift
gt; Ctrl Home will take you back to to the top, use the cursor
gt; to move *top* of this block to e3
gt; Paste in the formula - Ctrl V
gt;
gt; Messy, but after a while that sort of Block selection gets easier,
gt; whereas I never get used to dragging 25,000 lines !
gt;
gt; gt;
gt;
gt;
gt; gt;aaaa wrote:
gt; gt;gt; Firstly I'm a total newb to excel (and access for that matter LOL)
gt; gt;gt; I have almost 25,000 rows of data and I'm a little scared LOL
gt; gt;gt;
gt; gt;gt; its a basic set up, Total, Passed, Rejected ect ect across the top with the
gt; gt;gt; info below.
gt; gt;gt;
gt; gt;gt; example...
gt; gt;gt;
gt; gt;gt; Date Total Passed Rejected
gt; gt;gt; 1/2/05 123 100 23
gt; gt;gt; 1/4/05 324 320 4
gt; gt;gt; 1/6/05 97 96 1
gt; gt;gt;
gt; gt;gt; I need a fifth column showing a percentage of passed.
gt; gt;gt; but I don't know how to do this with out entering the formula 25 thousand
gt; gt;gt; times :-(
gt; gt;gt;
gt; gt;gt; Please help
gt; gt;gt;
gt; gt;gt; Also would really love to be pointed towards an easy to understand
gt; gt;gt; instruction site for access.
gt; gt;gt;
gt; gt;gt; Thank you all,
gt; gt;gt;
gt; gt;gt; Chad
gt; gt;gt;
gt; gt;gt;
gt;
gt;


quot;SteveWquot; gt; wrote in message
news
gt; On Sat, 18 Mar 2006 15:47:42 -0500, Debra Dalgleish
gt; gt; wrote:
gt;
gt;gt;Select cell E2, and enter the formula: =C2/B2
gt;gt;Format the cell as Percent
gt;gt;With cell E2 selected, point to the Fill Handle -- the small black
gt;gt; square at the bottom right of the selection.
gt;gt;When the pointer changes to a small black plus sign, double-click,
gt;gt; to fill the formula down to the first blank row.
gt;
gt; dragging 25,000 is a real pain
gt; click on e2
gt; Ctrl C to copy this cell
gt;
gt; Ctrl End will take you down to the bottom corner
gt; Click on the last cell in E that you want the formula in
gt; Hold Shift
gt; Ctrl Home will take you back to to the top, use the cursor
gt; to move *top* of this block to e3
gt; Paste in the formula - Ctrl V
gt;
gt; Messy, but after a while that sort of Block selection gets easier,
gt; 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