Need a formula to

[TheContractorsGroup]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm sorry if this is a double post, I can't tell if the first post actually posted... I didn't get a confirmation that it was posted so if I don't get one again this time around then I'll try using Firefox (right now I'm using Safari)...

Hello!

I have a row of fill-in fields: Text1, Text2 ,Text3, Text4, Text5, Text6, Text7, Text8, Text9, Text10, Text11, Text12 and Text13.

Each field except Text13 is set up as a 2 decimal number. Text13 is set up as a 2 decimal Percentage.

Text1 is "Total Hours Worked" on a project.

Text3, Text5, Text7, Text9 and Text11 are each a different male minority group. In each of these fields I have to enter the amount of hours worked - (for each male minority group) on the project.

Then Text13 has to automatically calculate the Total Percentage that all the minorities worked, based on the "Total Hours Worked" in Text1.

I enter "Total Hours Worked" (2 decimal) in Text 1; then I enter the total hours worked for each individual minority.

How do I get field Text13 to calculate the percentage of minority hours worked?

For example, Text1 is 50 hours, whereas Text3, Text5, Text7,
Text9, and Text11 are each 2 hours.

That's a total of 10 hours worked by minorities which is 20% of the 50 hours total worked on the job.

How do I get that 20% to show up in Text13?

I hope this all makes sense.

Thank you very much!
Diane Dennis

 

[John McGhie]

Hi Diane:

That makes sense, and we could tell you. But Word is not the right place to
be doing this.

Use Excel for this kind of thing, and you will find it is easy, intuitive,
and much more important: rugged and stable.

You need the same formula in either application: "(Text2 + Text3 ... +
Text12)/(Text1 / 100)"

Formulas computing results from text fields in Word are fiddly to set up and
far too easy to break. It's not a reliable solution.

Create this whole thing as a little Excel spreadsheet, and embed a copy into
your document. When you want to fill it in, double-click it. It will last
for years

Cheers

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm sorry if this is a double post, I can't tell if the first post actually
posted... I didn't get a confirmation that it was posted so if I don't get one
again this time around then I'll try using Firefox (right now I'm using
Safari)...

Hello!

I have a row of fill-in fields: Text1, Text2 ,Text3, Text4, Text5, Text6,
Text7, Text8, Text9, Text10, Text11, Text12 and Text13.

Each field except Text13 is set up as a 2 decimal number. Text13 is set up as
a 2 decimal Percentage.

Text1 is "Total Hours Worked" on a project.

Text3, Text5, Text7, Text9 and Text11 are each a different male minority
group. In each of these fields I have to enter the amount of hours worked -
(for each male minority group) on the project.

Then Text13 has to automatically calculate the Total Percentage that all the
minorities worked, based on the "Total Hours Worked" in Text1.

I enter "Total Hours Worked" (2 decimal) in Text 1; then I enter the total
hours worked for each individual minority.

How do I get field Text13 to calculate the percentage of minority hours
worked?

For example, Text1 is 50 hours, whereas Text3, Text5, Text7,
Text9, and Text11 are each 2 hours.

That's a total of 10 hours worked by minorities which is 20% of the 50 hours
total worked on the job.

How do I get that 20% to show up in Text13?

I hope this all makes sense.

Thank you very much!
Diane Dennis

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[TheContractorsGroup]

LOL, that shot right over my head (except the part that it can't be done in Word)...

Thank you John for your explanation!

I will try this although I'll have to read up on how to create it in Excel and embed it in the Word document and then double-click it (?).

Can you tell me please,

A) How do I embed an Excel spreadsheet into a Word document

B) When I want to distribute the form to others to use, do I need to include both the Excel spreadsheet and the Word document, or just the Word document?

C) Double-click on what?

Thank you again!

And I'm sorry for taking so long to respond, even though I checked the box for "Email me when replies are posted", I didn't get an email.

Thank you again though! I'll see what I can figure out and then I'll check back here for help on the embedding, distribution and double-clicking questions I asked.

Have a great day!
Diane Dennis

 

[TheContractorsGroup]

I'm sorry, I don't know if I clicked the Email me box this time so I'm just entering this so that I can click that box.

Thank you!
Diane
ps: I looked in both Word and Excel help and there isn't anything about embedding so I'll have to look farther.

Thank you again!!
Diane

 

[TheContractorsGroup]

Hi John!

I'm finding some information about embedding an Excel spreadsheet into a Word document...

Got a question...

What if a recipient of the Word document doesn't have Excel, does that mean they won't be able to use the Word document?

Thank you John!
Diane

 

[John McGhie]

Hi Diane:

1) Make the spreadsheet in Excel

2) Select the bit you want

3) Press Command + c

4) Click in the Word document

5) Press Command + v...

"Embedding" is what you get if you do not do "anything else".

As a good working practice, create a new fresh Excel spreadsheet, so you are
not including something you might not want the customer to see.

Cut the Excel workbook back to only ONE sheet, the one you want to use,
before you create your formula. That makes it a nice compact file.

You can also use Insert>Object>Microsoft Excel worksheet from within Word.
Microsoft Office will automatically start Excel for you. In which case,
place your data in the top left cells of the worksheet that appears.

Cheers

LOL, that shot right over my head (except the part that it can't be done in
Word)...

Thank you John for your explanation!

I will try this although I'll have to read up on how to create it in Excel and
embed it in the Word document and then double-click it (?).

Can you tell me please,

A) How do I embed an Excel spreadsheet into a Word document

B) When I want to distribute the form to others to use, do I need to include
both the Excel spreadsheet and the Word document, or just the Word document?

C) Double-click on what?

Thank you again!

And I'm sorry for taking so long to respond, even though I checked the box for
"Email me when replies are posted", I didn't get an email.

Thank you again though! I'll see what I can figure out and then I'll check
back here for help on the embedding, distribution and double-clicking
questions I asked.

Have a great day!
Diane Dennis

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Rob Schneider]

FYI, I find it more useful to simply put a "picture" of the Excel
rows/columns in Word, rather than an embedded link. Little productivity
is usually gained by automating the updating between Word and Excel; but
to each it's own. Keep it simple.

--rms

www.rmschneider.com

 

[John McGhie]

Hi Rob:

Yeah: For a one-shot application that's often true.

In this case, she's making a "Template" she can use to quote jobs with. She
wants to update it multiple times over the next few years. That's why for
this application embedding is probably best...

Cheers

FYI, I find it more useful to simply put a "picture" of the Excel
rows/columns in Word, rather than an embedded link. Little productivity
is usually gained by automating the updating between Word and Excel; but
to each it's own. Keep it simple.

--rms

www.rmschneider.com

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[John McGhie]

Hi Diane:

It means they won't be able to update it easily. The Excel inclusion will
appear as a "picture".

However, the number of people who have "Word" but not "Excel" is vanishingly
small.

The street price of one application is so close to the price of the whole
"Office" suite that 99.99 per cent of Windows customers will buy the whole
thing. On the Mac, there may be a few more, but it is still less than five
per cent.

A greater consideration is "What if they don't have Word at all?"

Under those circumstances, an embedded chunk of Excel may not appear at all.
But neither will most of Word's Fields, so Fillin fields would also be
likely to fail.

You are making an "application" to be used for a "Business purpose". I
really don't think there are ANY people in business or government anywhere
in the world who do not have the ability to handle a Word document with an
embedded Excel spreadsheet. I believe you can safely discount this issue.

On the other hand: Since you are emailing this thing around to "unknown"
users, give consideration to doing it entirely in Excel. The formatting
capabilities of Excel are actually quite good: you will get a "document"
that looks very close to what you had in Word (the easiest way to start is
to simply paste the Word document into Excel, then replace the fields with
Excel cells).

Doing it this way will result in a much smaller file (Excel files are
typically a tenth the size of a Word document for a one-page thing like
this). So it will be much better for emailing.

And because the thing that arrives is a "single file type, containing only
one kind of data", users who do not have any kind of Microsoft Office
application will be able to edit it with a wide variety of free
applications, including some of the "Web" versions of Excel.

The thing that creates the difficulty is sending users a "complex"
multi-component file. If it's all one thing: Excel, even the least capable
of the free applications will eat it up.

Cheers

Hi John!

I'm finding some information about embedding an Excel spreadsheet into a Word
document...

Got a question...

What if a recipient of the Word document doesn't have Excel, does that mean
they won't be able to use the Word document?

Thank you John!
Diane

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[TheContractorsGroup]

Thank you both Rob and John!

John, I had no idea one could paste into Excel a form created in Word! Every time I've made a form in Excel I've done it directly in Excel and I have hated it every time. It's so difficult to get all the cells set up to the right sizes and then when you go further down in the document if you move something to make it fit then it messes up the top part of the document. Oh it's always been a pain!

But I'm going to try the "pasting the Word document into Excel" and see where I get with all that, pasting and then replacing the fields with Excel cells. =)

Thank you so much!!

Have a great weekend!
Diane

 

[TheContractorsGroup]

Hi Diane:

It means they won't be able to update it easily. The Excel inclusion will
appear as a "picture".

However, the number of people who have "Word" but not "Excel" is vanishingly
small.

The street price of one application is so close to the price of the whole
"Office" suite that 99.99 per cent of Windows customers will buy the whole
thing. On the Mac, there may be a few more, but it is still less than five
per cent.

A greater consideration is "What if they don't have Word at all?"

Under those circumstances, an embedded chunk of Excel may not appear at all.
But neither will most of Word's Fields, so Fillin fields would also be
likely to fail.

You are making an "application" to be used for a "Business purpose". I
really don't think there are ANY people in business or government anywhere
in the world who do not have the ability to handle a Word document with an
embedded Excel spreadsheet. I believe you can safely discount this issue.

On the other hand: Since you are emailing this thing around to "unknown"
users, give consideration to doing it entirely in Excel. The formatting
capabilities of Excel are actually quite good: you will get a "document"
that looks very close to what you had in Word (the easiest way to start is
to simply paste the Word document into Excel, then replace the fields with
Excel cells).

Doing it this way will result in a much smaller file (Excel files are
typically a tenth the size of a Word document for a one-page thing like
this). So it will be much better for emailing.

And because the thing that arrives is a "single file type, containing only
one kind of data", users who do not have any kind of Microsoft Office
application will be able to edit it with a wide variety of free
applications, including some of the "Web" versions of Excel.

The thing that creates the difficulty is sending users a "complex"
multi-component file. If it's all one thing: Excel, even the least capable
of the free applications will eat it up.

Cheers

Hi John!

I'm trying to paste the Word document into Excel but I'm just ending up with a mess in Excel. Is there a tutorial somewhere about this or do you mind explaining how to do it?

Thank you so much!!
Diane

 

[TheContractorsGroup]

Hi John!

I still haven't been able to paste the Word document into an Excel worksheet without it getting all out of whack, but I did manage to get the formula that I originally asked for, for Word, to work in Word.

I know what you wrote about Excel being a much "smaller" file than Word but I'm stuck with getting the Word document pasted into the Excel worksheet...

If you can help me with getting the Word document pasted into the Excel worksheet that would be terrific but at the very least, at least I'm on my way with the Word document itself.

Thank you very much! I hope you're enjoying your weekend!!
Diane

 

[John McGhie]

Command + a, command + c, Command + v....

Works for me.

Excel documents are tables, so the results will need fixing if what you are
pasting is "not" a table.

But since you were asking about table formulas, I guess it is a table.

So I am sorry, I do not understand where the difficulty is... I "suspect"
you are "trying too hard".

Just paste the thing in, then alter the column widths to line it up. You
only want to get the text in there anyway, since you will need to tweak the
formatting and add the formulas.

Cheers

Hi John!

I still haven't been able to paste the Word document into an Excel worksheet
without it getting all out of whack, but I did manage to get the formula that
I originally asked for, for Word, to work in Word.

I know what you wrote about Excel being a much "smaller" file than Word but
I'm stuck with getting the Word document pasted into the Excel worksheet...

If you can help me with getting the Word document pasted into the Excel
worksheet that would be terrific but at the very least, at least I'm on my way
with the Word document itself.

Thank you very much! I hope you're enjoying your weekend!!
Diane

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[TheContractorsGroup]

Hi John!

Yes, it is a Word table.

When I select and copy the table in Word, and then try to paste it into Excel I'm given several different choices.

First, just plain Paste.

Then, Special Paste... And under Special Paste there are the following choices:

Microsoft Word Document Object;
Picture;
HTML;
Unicode Text;
Text;
HLNK

How do I want to paste the Word table into the Excel worksheet?

Thank you again John!
Diane

 

[John McGhie]

"Ordinary" Paste works for me here.

It brings what you selected in in HTML, which Excel converts to an Excel
Worksheet.

When you paste, click only in the top left cell in a blank Excel worksheet.
If you have anything selected, the results can be a bit "entertaining".

Of course the column widths will change, you need to adjust them by
double-clicking or dragging after you paste.

What you get is something that looks like what you had. All the pieces are
there, and that enables you to stick the formulas in easily.

Excel is a bit of a learning curve if you are not accustomed to business
applications, but please believe me, the effort is worth it. If you know
Excel well, you are unlikely to encounter a situation where you need
anything more complex to manage data. And people will need to get up very
early in the morning to mislead you if you track their proposals in Excel ‹
it's the world's greatest tool for playing "What if they're lying?" games


Cheers

Hi John!

Yes, it is a Word table.

When I select and copy the table in Word, and then try to paste it into Excel
I'm given several different choices.

First, just plain Paste.

Then, Special Paste... And under Special Paste there are the following
choices:

Microsoft Word Document Object;
Picture;
HTML;
Unicode Text;
Text;
HLNK

How do I want to paste the Word table into the Excel worksheet?

Thank you again John!
Diane

--
Don't wait for your answer, click here: http://www.word.mvps.org/

Please reply in the group. Please do NOT email me unless I ask you to.

John McGhie, Microsoft MVP, Word and Word:Mac
Sydney, Australia. mailto:[email protected]