Newbie peroblem with calculating values in cells

[Ike]

The spreadsheet is supposed to list frequncies of tones in the chromatic
scale. I named all the cells in the A column starting with F2 and up to A8,
(i.e., cell A1 was F2, A2 was F#2, A3 was G#2, etc. by typing in the names
in column A. When I got to A4, in the B column I input the number 440, for
standard concert pitch, in cell B 29 adjacent to A 29 the A4 named cell. In
the formula bar for B 28 (adjacent to G#4 named cell in the A column), I
typed
=B29/EXP(LN2/12)
because I wanted it to display the value of 440 divided by the 12th root of
2. of course this doesn't work.
After I input the first formula into the B28 cell, and press ENTER I get
#NAME? appearing in the cell.

I had a spreadsheet that worked in the PFS First Choice, and which also
calculated the value in cents of a deviation in Hz from the standard pitch
for all the values in the range of the chromatic scale that I needed. But
PFS First Choice is now abandonware and no longer easily available and my
copy of the disk no longer works, maybe because my computer uses the NTFS
format.

What I want to do is input 440 or 441 or whatever standard for A4 into the
adjacent cell for A4 and have the whole series above and below give me a
frequency readout quantity for (in this case) Equal Temperament or whatever
tuning scheme I use. In Equal temperament the value of each higher note in
the Chromatic scale is (the 12th root of 2) times the next lower note.

Then the formula for the cents equivalent of a frequency difference between
two pitches (X-Y) would be 1200*(LNX-LNY)/(LN2), if I remember correctly
what I did in the other program formula. (A cent is equal to the 1200th root
of 2).

What my original spreadsheet did, was to let me set a tremolo between two
pitches, of close to the same frequency, for instance A 440 beats 4 hz
tremolo vs. A 444, and to use the spreadsheet to input a tremolo speed and
set the cents vernier dial on my measurement equipment to check the accuracy
of the tuning.

 

[Ike]

The spreadsheet is supposed to list frequncies of tones in the chromatic
scale. I named all the cells in the A column starting with F2 and up to
A8, (i.e., cell A1 was F2, A2 was F#2, A3 was G#2, etc. by typing in the
names in column A. When I got to A4, in the B column I input the number
440, for standard concert pitch, in cell B 29 adjacent to A 29 the A4
named cell. In the formula bar for B 28 (adjacent to G#4 named cell in the
A column), I typed
=B29/EXP(LN2/12)
because I wanted it to display the value of 440 divided by the 12th root
of 2. of course this doesn't work.
After I input the first formula into the B28 cell, and press ENTER I get
#NAME? appearing in the cell.

I had a spreadsheet that worked in the PFS First Choice, and which also
calculated the value in cents of a deviation in Hz from the standard pitch
for all the values in the range of the chromatic scale that I needed. But
PFS First Choice is now abandonware and no longer easily available and my
copy of the disk no longer works, maybe because my computer uses the NTFS
format.

What I want to do is input 440 or 441 or whatever standard for A4 into the
adjacent cell for A4 and have the whole series above and below give me a
frequency readout quantity for (in this case) Equal Temperament or
whatever tuning scheme I use. In Equal temperament the value of each
higher note in the Chromatic scale is (the 12th root of 2) times the next
lower note.

Then the formula for the cents equivalent of a frequency difference
between two pitches (X-Y) would be 1200*(LNX-LNY)/(LN2), if I remember
correctly what I did in the other program formula. (A cent is equal to the
1200th root of 2).

What my original spreadsheet did, was to let me set a tremolo between two
pitches, of close to the same frequency, for instance A 440 beats 4 hz
tremolo vs. A 444, and to use the spreadsheet to input a tremolo speed and
set the cents vernier dial on my measurement equipment to check the
accuracy of the tuning.

I found the right way to enter the first formula: =B29/EXP(LN(2)/12) Also I
checked a box to enable cell references. now I get the calculation result in
the cell. What I need to do now is create a formula that calculates the
entire column at once.

 

[Ike]

I found the right way to enter the first formula: =B29/EXP(LN(2)/12) Also
I checked a box to enable cell references. now I get the calculation
result in the cell. What I need to do now is create a formula that
calculates the entire column at once.

I would like to move a formula down a column and have the cell number
change. Like for instance in cell G2
=(LN(F2)-LN(B2))*1200/LN(2)

Would change to =(LN(F3)-LN(B3)*1200/LN(2) when I paste it to G3 cell. Or in
other cases, just have one of the cell references in the formula increment
or decrement by one unit as I move rthe formula up or down the column one
unit.

Another issue, is that when I copy a formula from the formula bar onto the
clipboard, and then click on another cell where I want to paste it, the new
cell gets a blinking dotted line around it and the formula disappears form
the cell I just copied it from, and instead in that cell appears the name of
another cell.

 

[Harlan Grove]

Ike wrote...
....

I would like to move a formula down a column and have the cell number
change. Like for instance in cell G2
=(LN(F2)-LN(B2))*1200/LN(2)

Would change to =(LN(F3)-LN(B3)*1200/LN(2) when I paste it to G3 cell. Or in
other cases, just have one of the cell references in the formula increment
or decrement by one unit as I move rthe formula up or down the column one
unit.

Did you try this. If G2 contained the formula

=(LN(F2)-LN(B2))*1200/LN(2)

then filling G2 down into G3 *does* produce the following formula in
G3.

=(LN(F3)-LN(B3))*1200/LN(2)

Unless by 'move' as formula you mean *cut* and paste.

Is there any reason you don't want to simplify this to

G3:
=LOG(F3/B3,2)*1200

?

Another issue, is that when I copy a formula from the formula bar onto the
clipboard, and then click on another cell where I want to paste it, the new
cell gets a blinking dotted line around it and the formula disappears form
the cell I just copied it from, and instead in that cell appears the name of
another cell.

Are you *copying* formulas to the clipboard or *cutting* them? There's
a BIG difference in spreadsheets. If you issue the menu command Tools >
Options, and click on the Transition tab, is the Transition formula
entry box checked?

 

[Ron Rosenfeld]

The spreadsheet is supposed to list frequncies of tones in the chromatic
scale. I named all the cells in the A column starting with F2 and up to A8,
(i.e., cell A1 was F2, A2 was F#2, A3 was G#2, etc. by typing in the names
in column A. When I got to A4, in the B column I input the number 440, for
standard concert pitch, in cell B 29 adjacent to A 29 the A4 named cell. In
the formula bar for B 28 (adjacent to G#4 named cell in the A column), I
typed
=B29/EXP(LN2/12)
because I wanted it to display the value of 440 divided by the 12th root of
2. of course this doesn't work.
After I input the first formula into the B28 cell, and press ENTER I get
#NAME? appearing in the cell.

I had a spreadsheet that worked in the PFS First Choice, and which also
calculated the value in cents of a deviation in Hz from the standard pitch
for all the values in the range of the chromatic scale that I needed. But
PFS First Choice is now abandonware and no longer easily available and my
copy of the disk no longer works, maybe because my computer uses the NTFS
format.

What I want to do is input 440 or 441 or whatever standard for A4 into the
adjacent cell for A4 and have the whole series above and below give me a
frequency readout quantity for (in this case) Equal Temperament or whatever
tuning scheme I use. In Equal temperament the value of each higher note in
the Chromatic scale is (the 12th root of 2) times the next lower note.

Then the formula for the cents equivalent of a frequency difference between
two pitches (X-Y) would be 1200*(LNX-LNY)/(LN2), if I remember correctly
what I did in the other program formula. (A cent is equal to the 1200th root
of 2).

What my original spreadsheet did, was to let me set a tremolo between two
pitches, of close to the same frequency, for instance A 440 beats 4 hz
tremolo vs. A 444, and to use the spreadsheet to input a tremolo speed and
set the cents vernier dial on my measurement equipment to check the accuracy
of the tuning.

Not sure I follow all you are, however

1. F2 is not a valid cell name.
2. F#2 is not a valid cell name.

From EXCEL Help:

Guidelines for names
What characters are allowed? The first character of a name must be a letter
or an underscore character. Remaining characters in the name can be letters,
numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell reference,
such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed. Underscore
characters and periods may be used as word separators — for example, Sales_Tax
or First.Quarter.

How many characters can be used? A name can contain up to 255 characters.

=================================

So far as your formula:

=B29/EXP(LN2/12)

LN2/12 is not a valid function name, nor is it a valid argument to some other
function, nor is it a valid cell reference name (see above). So Excel doesn't
have a clue as to what you mean.

If you are trying to calculate the natural logarithm of 2/12, then the proper
syntax is LN(number) or LN(2/12).

This, too, is clearly outlined in Excel HELP.

Finally, simple algebra reveals that the 12th root of a number is the same as
raising it to the 1/12 power; and the 1200th root would be the same as raising
it to the 1/1200th power.

So the 12th root of 2 would be:

=2^(1/12)

and the 1200th root of 2 would be

=2^(1/1200)

440 divided by the 12th root of 2 would be:

=440/(2^(1/12))


--ron

 

[Ike]

Ike wrote...
...

Did you try this. If G2 contained the formula

=(LN(F2)-LN(B2))*1200/LN(2)

then filling G2 down into G3 *does* produce the following formula in
G3.

=(LN(F3)-LN(B3))*1200/LN(2)

Unless by 'move' as formula you mean *cut* and paste.

Is there any reason you don't want to simplify this to

G3:
=LOG(F3/B3,2)*1200

?


Are you *copying* formulas to the clipboard or *cutting* them? There's
a BIG difference in spreadsheets. If you issue the menu command Tools >
Options, and click on the Transition tab, is the Transition formula
entry box checked?

I was able to get the formulas to behave finally, by learning how to copy
and paste down the whole column. Your answer was very informative, however.
Thank you.

I now have a new issue: I wanted to get all the numbers in the table to
round off to rwo decimal places, but I tried the Help file which suggested
that I use the Decrease Decimal function on the formatting toolbar. When I
selected all the cells and clicked on the icon, nothing happened.

 

[Ike]

Not sure I follow all you are, however

1. F2 is not a valid cell name.
2. F#2 is not a valid cell name.

From EXCEL Help:

Guidelines for names
What characters are allowed? The first character of a name must be a
letter
or an underscore character. Remaining characters in the name can be
letters,
numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell
reference,
such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed.
Underscore
characters and periods may be used as word separators - for example,
Sales_Tax
or First.Quarter.

How many characters can be used? A name can contain up to 255
characters.

=================================

So far as your formula:

=B29/EXP(LN2/12)

LN2/12 is not a valid function name, nor is it a valid argument to some
other
function, nor is it a valid cell reference name (see above). So Excel
doesn't
have a clue as to what you mean.

If you are trying to calculate the natural logarithm of 2/12, then the
proper
syntax is LN(number) or LN(2/12).

This, too, is clearly outlined in Excel HELP.

Finally, simple algebra reveals that the 12th root of a number is the same
as
raising it to the 1/12 power; and the 1200th root would be the same as
raising
it to the 1/1200th power.

So the 12th root of 2 would be:

=2^(1/12)

and the 1200th root of 2 would be

=2^(1/1200)

440 divided by the 12th root of 2 would be:

=440/(2^(1/12))


--ron

I finally got the formulas and the sheet to behave by rewriting the
formulas. BTW I was not using the cell names in the formulas. This was a
text column to refer to the adjacent rows.

The new issue is that I am now trying to get all the numbers in the table to
truncate to two decimal places, with no luck yet.

 

[Ron Rosenfeld]

I finally got the formulas and the sheet to behave by rewriting the
formulas. BTW I was not using the cell names in the formulas. This was a
text column to refer to the adjacent rows.

The new issue is that I am now trying to get all the numbers in the table to
truncate to two decimal places, with no luck yet.

Have you looked at HELP for Truncate?


--ron

 

[Ike]

Have you looked at HELP for Truncate?


--ron

I looked at it. Now what?

 

[Ron Rosenfeld]

I looked at it. Now what?

I assume that if you looked at it, either you don't understand it, or you have
determined that the TRUNC function is not appropriate for your requirements.
If the latter, then I don't understand what you mean when your write "truncate
to two decimal places". Perhaps you could explain exactly what you mean by
that.

--ron

 

[Ike]

I assume that if you looked at it, either you don't understand it, or you
have
determined that the TRUNC function is not appropriate for your
requirements.
If the latter, then I don't understand what you mean when your write
"truncate
to two decimal places". Perhaps you could explain exactly what you mean
by
that.

--ron

After reading the file as you suggested, I was of the impression that the
TRUNC command would not be appropriate since it would seem from the
description that it truncates to whole numbers. What I would like to do is
select a column of figures --generated by the same formula in this
instance-- and round every figure in it to two decimal places.

 

[Ron Rosenfeld]

After reading the file as you suggested, I was of the impression that the
TRUNC command would not be appropriate since it would seem from the
description that it truncates to whole numbers. What I would like to do is
select a column of figures --generated by the same formula in this
instance-- and round every figure in it to two decimal places.

Well, now you are writing you want to ROUND the figures to two decimal places.
Before you wrote you wanted to TRUNCate the figures to two decimal places.
You've gotten me very confused.

TRUNC should satisfy your first request.
ROUND should satisfy your second request.


==========================
From HELP for TRUNC worksheet function:

TRUNC(number,num_digits)

Number is the number you want to truncate.

Num_digits is a number specifying the precision of the truncation. The
default value for num_digits is 0 (zero).
============================


So:

A1: 4.589

=TRUNC(A1) --> 4
=TRUNC(A1,2) --> 4.58
=ROUND(A1,2) --> 4.59

Hopefully one of these will do what you wish.
--ron

 

[Bruce Sinclair]

Well, now you are writing you want to ROUND the figures to two decimal places.
Before you wrote you wanted to TRUNCate the figures to two decimal places.
You've gotten me very confused.

TRUNC should satisfy your first request.
ROUND should satisfy your second request.

... and if all you want to do is to show some kind of currency format (the
most common reason for using 2 decimal places ) then you could simply
display the numbers as currency instead.

Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Ron Rosenfeld]

Well, now you are writing you want to ROUND the figures to two decimal places.
Before you wrote you wanted to TRUNCate the figures to two decimal places.
You've gotten me very confused.

TRUNC should satisfy your first request.
ROUND should satisfy your second request.

.. and if all you want to do is to show some kind of currency format (the
most common reason for using 2 decimal places ) then you could simply
display the numbers as currency instead.

Bruce
[/QUOTE]

If you are recommending using the currency format in the cell, the OP should be
aware that would result in ROUNDing the display only, and have no effect on the
actual value stored in the cell.

At least initially, the OP indicated he wanted to TRUNCate the result (or
possibly ROUND the result).


--ron