Gravitational Constant in Excel Formula 1

[junfanbl]

Hey Everyone,

I am trying to figure out how to use a gravitational constant in some of my worksheet calculations. From what I understand this is the gravitational constant: 6.67408 × 10-11 m3 kg-1 s-2.
However, how exactly do you use that in Excel? I need to determine the gravitational force on an object when it isn't in motion compared only to earth's mass. However, I am not the best at math. (and I'm not an engineer for that matter, but I do programming support for engineers). I am trying to do a side project though.

Generally speaking, how do you use physical constants in Excel? If anyone has a worksheet with these numbers already configured for use in formulas, than I would very much appreciate it if I could get a copy. I don't mind doing the work myself though, if someone could teach me. Either way would work. Thank you for any help!

 

[SkipVought]

Hi,

On any sheet, I often create a sheet for various constants, SELECT a cell. Let’s say A1. In the Name Box, just above A1, you will see A1. In the Name Box, replace A1 with the name you want to represent the gravitational constant, like GravConst. NO SPACES and there are other prohibited characters. UNDERSCORE is allowable. The hit ENTER.

Now you can use GravConst in any reference or formula. If my memory is correct, F3 brings up a list of Names Ranges, which is what you have created. A Named Range can define one or more cells. In ypur case, one.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's an example using 3 constants
[pre]
GravConst 6.73E-11
GravAcc 9.80665
GravAcc2 32.174
[/pre]

Here's the process:
[tt]
1) SELECT all these cells
2) Formulas > Defined Names > Create from Selection > Create names from values in the:...
...and select LEFT Column
[/tt]

Now if you select any of your constants, you will see the Range Name in the Name Box.

Now using a constant.
We would do this calculation on anther sheet with such a formula
Force = G * (m[sub]1[/sub]* m[sub]2[/sub]) / r[sup]2[/sup]

I would have a constant for ErthMass and a cell Named SubjMass to hold the mass of the subject and a cell Named Dist.

So lets say that in A2 we have this formula for Force...
[tt]
a2: =GravConst*(ErthMass*SubjMass)/Dist^2
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[junfanbl]

Hey SkipVought,

Thank you for the tips! There is one thing I don't quite understand though. I know how to use named ranges, but what I don't understand is how you get 6.73E-11 from 6.67408 × 10-11 m3 kg-1 s-2?
I think the number format you presented is scientific notation. I don't understand how you got here though? I mean, I looked up what the value was for gravitational constant and simply pasted it into excel to see how it would be interpreted. It seems Excel treats it as a string though, because I can't use it any formulas. Which I think makes sense because the value itself seems to be an unfinished equation. I'm sure it's not though. It's only my ignorance.

 

[IDS]

I'm not sure where Skip got his 6.73E-11 from, but I always suspected he comes from a parallel universe.

To enter scientific notation, just replace the "x 10" with "E". If you are copy and pasting also make sure not to include the units.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[junfanbl]

So the "m3 kg-1 s-2" part of the value is just units?

 

[SkipVought]

[pre]
GravConst 6.67408E-11
[/pre]

Sorry, I'm not sure where I got that value from.

Although the DISPLAY value is 6.67408E-11, the underlying value is 0.0000000000667408, as you can see in the Formula Bar.

Excel uses display values that are different than the underlying value for Scientific Notation, Date and Time, Fractions, Currency and Percentages.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Yes, they are units
[tt]
m3 / (kg * s[sup]2[/sup])
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Another method, that I read about in some Excel book years ago and which I use on occasion, is that the defined name (call it a Constant) can be defined in the Name Manager itself without any external reference to a cell or range.

For example, open the Name Manager and define the new name "GravAcc", give it either workbook or worksheet scope. Then enter the numerical value (or a text string in quotation marks) and click OK. Access the new "constant" by entering in cell formulas, etc.

My use of this versus defining something in a worksheet cell is pretty rare and unusual, but it does have its uses.

ETA: I just ran across an instance this morning. A particular worksheet uses a constant defined this way in order to keep track of the "state" of the spreadsheet (modified, audited, etc), rather than an actual numerical constant to be used for calculations.

 

[IRstuff]

CODATA's last published recommendation for the Newtonian constant of gravitation is 6.67408(31) × 10−11 m^3*kg^−1*s^−2

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[junfanbl]

What about mass? Is the mass of an object simply it's weight in kilograms? I have an object that is 97,000 lbs. How do you calculate the mass of the object for use in the gravitational force formula?

 

[electricpete]

Is the mass of an object simply it's weight in kilograms?

I dunno. Are you calculating on earth? If so, you don’t need gravitational constant G and the formula including mass of the earth, all you need gravitational acceleration g

And even on earth, your statement is not really true unless you happen to use KGf as a unit of weight, in which case the numerical value of the weight in kgf is the same as the numerical value of the mass in kg.

BUT kgf is a dodgy unit. Newtons is the SI unit for force (and weight).

F=m*a
Weight = M * g ... works out just fine if you use SI units for Weight (N), Mass (kg), and g (kg*m/sec^2)… the gravitational acceleration g that Skip mentioned.

I have an object that is 97,000 lbs. How do you calculate the mass of the object for use in the gravitational force formula?

Still assuming you’re on earth…
Convert the weight from lbf to Newton and proceed as above.
Or…
If working in british/imperial units, an object with 97,000 lbf weight has a mass of 97,000 lbm.

At the risk of touching off a controversy, I’d say SI units are generally a lot easier to work with for people that have trouble working with units.


=====================================
(2B)+(2B)' ?

 

[TomBarsh]

assuming you're on Earth

This is an important point. I work a lot with the ASME pressure vessel code and there are a lot of assumptions in it based on being on Earth with atmospheric pressure and all. I hope designers take this into account when designing pressure vessels for use on extraterrestrial human habitations; I have wondered about this over time.

 

[drawoh]

electricpete,

Kilograms are a unit of mass. Pounds and Newtons are units of force and weight. For years, I have been doing exactly what SkipVought describes above, with units, on the last sheet of my spreadsheet. I have conversion values for kgf and lbm for the sole purpose of converting these to real units, typically MKS.

=22.5*furlongs/fortnight^2

If you confuse mass and force, you will stick g in the wrong place in your acceleration equations and get the wrong answer.

--
JHG

 

[IRstuff]

Note the EP was extremely careful in using kg, kgf, lb, and lbm units to distinguish things. Mathcad does almost the same thing, so you can reference lbm as the mass of something whose weight is lb.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

Speaking of Newtons, forcefully...

...I’d like to reference Newton’s FOURTH Law.

1 Fig / Cookie

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Skip... constant

https://physics.nist.gov/cgi-bin/cuu/Value?bg

Dik

 

[jrs87]

Always cancel units.

https://www.youtube.com/watch?v=4GNJmPtZbZI