I am trying to set up a depreciation schedule and I want the denominator of
fraction to stay a constant - do not round or reduce. For instance, if the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I have
tried to format the cell to # ???/???, but Excel keeps reducing the fraction.

You can have a defined cell format that is dependent on a variable; however,
if I understood your request correctly, you can do what you are asking for
with some VBA code though. Give this a try...

Right click the tab at the bottom of your worksheet and then click View Code
from the popup menu that appears and then copy/paste the following code into
the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
Const FormulaCell As String = "J8"
If Target.Address = "$I$8" Then
Range(FormulaCell).NumberFormat = "# " & String(Len(Target.Value), _
"?") & "/" & Target.Value
End If
End Sub

Next, change the example J8 cell reference I used in my code above to the
actual address of the cell that contains the formula you posted. Okay, now
go back to the worksheet and enter a value in cell I8... you need to do this
one time in order for the first format to be assigned (everything will be
automatic thereafter), so you can just type the number that is already in
there over again if you want. Now, whenever you change the value in I8, the
format in the cell you changed my example J8 reference to will change to a
Fraction format with the denominator being the value in I8.

--
Rick (MVP - Excel)


"fractionwoes" wrote in message
...
I am trying to set up a depreciation schedule and I want the denominator of
fraction to stay a constant - do not round or reduce. For instance, if
the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I
have
tried to format the cell to # ???/???, but Excel keeps reducing the
fraction.

Do you mean

=IF(F13="","",(F$5-F12) & "/" & I$8)

If this post helps click Yes
---------------
Jacob Skaria


"fractionwoes" wrote:

I am trying to set up a depreciation schedule and I want the denominator of
fraction to stay a constant - do not round or reduce. For instance, if the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I have
tried to format the cell to # ???/???, but Excel keeps reducing the fraction.

Just a quick follow up to both my posting and Jacob's posting...

Using my method, the value in I8 will remain a real number that you can do
calculations with whereas using Jacob's method makes the value in I8 simple
text, so it would not be a number any more.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You can have a defined cell format that is dependent on a variable;
however, if I understood your request correctly, you can do what you are
asking for with some VBA code though. Give this a try...

Right click the tab at the bottom of your worksheet and then click View
Code from the popup menu that appears and then copy/paste the following
code into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
Const FormulaCell As String = "J8"
If Target.Address = "$I$8" Then
Range(FormulaCell).NumberFormat = "# " & String(Len(Target.Value), _
"?") & "/" & Target.Value
End If
End Sub

Next, change the example J8 cell reference I used in my code above to the
actual address of the cell that contains the formula you posted. Okay, now
go back to the worksheet and enter a value in cell I8... you need to do
this one time in order for the first format to be assigned (everything
will be automatic thereafter), so you can just type the number that is
already in there over again if you want. Now, whenever you change the
value in I8, the format in the cell you changed my example J8 reference to
will change to a Fraction format with the denominator being the value in
I8.

--
Rick (MVP - Excel)


"fractionwoes" wrote in message
...
I am trying to set up a depreciation schedule and I want the denominator
of
fraction to stay a constant - do not round or reduce. For instance, if
the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I
have
tried to format the cell to # ???/???, but Excel keeps reducing the
fraction.

Hello Rick,

Your approach sounds reasonable but the OP mentioned a serious
application (depreciation schedule) and IMHO you cannot really trust
Excel's fractional number format. Please see entry 4 of my Excel
Don'ts:
http://sulprobil.com/html/excel_don_ts.html

I suggest to use the idea of your approach together with my UDF NRN:
http://sulprobil.com/html/nearest_rational_number.html

Regards,
Bernd

That did fix the rounding problem but caused the rest of the formula to mess
up. The next column was =IF(G13="","",(G13*E$5)) but it is showing #VALUE
now. I hateit when it fixes one thing but screws up the rest!
--
Thanks!


"Jacob Skaria" wrote:

Do you mean

=IF(F13="","",(F$5-F12) & "/" & I$8)

If this post helps click Yes
---------------
Jacob Skaria


"fractionwoes" wrote:

I am trying to set up a depreciation schedule and I want the denominator of
fraction to stay a constant - do not round or reduce. For instance, if the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I have
tried to format the cell to # ???/???, but Excel keeps reducing the fraction.

Would the "trust" issue you bring up come into play for the OP's
*particular* question? I mean, we are specifying the denominator to be the
value the numerator is being divided by, so wouldn't a cell format of
Fraction just give us back that numerator again? I would not think enough
precision could be lost in the decimal to underlying binary representation
to make a difference in the numerator value it the cell format calculates
given we have *fixed* the denominator to be the value the numerator was
divided by.

--
Rick (MVP - Excel)


"Bernd P" wrote in message
...
Hello Rick,

Your approach sounds reasonable but the OP mentioned a serious
application (depreciation schedule) and IMHO you cannot really trust
Excel's fractional number format. Please see entry 4 of my Excel
Don'ts:
http://sulprobil.com/html/excel_don_ts.html

I suggest to use the idea of your approach together with my UDF NRN:
http://sulprobil.com/html/nearest_rational_number.html

Regards,
Bernd

The problem has to do with the format of the string Jacob's formula
produces. Excel is trying to be helpful to you and is getting it completely
wrong. Since the output of Jacob's formula is the text string N/D (where N
is the numerator's value and D is the denominator's value), Excel must
convert it to a number in order to use it in a calculation. When Excel tries
to convert a text string to a number, it will *not* evaluate an algebraic
expression (it doesn't see mathematical operator symbols as operators which
is why you can't to something like =--"5*3" in a cell and get 15); HOWEVER,
Excell will always go out of its way to try and construct date values from
text that look like dates when there is an attempt to use that text in a
calculation. The N/D string, when one of N or D is between 1 and 12, looks
like like a date to Excel where the other value is first assumed to be a day
value in the month if it would produce a real date or else it is assumed to
be a year value. So, if G13 contained the text string "2/72", Excel would
convert it to the date serial value for February 1, 1972, which is 26330 and
not the value of 0.027777778 (rounded) that you might have expected, if you
tried to use it in a calculation. This is the kind of thing I was trying to
alert you to in the follow up message I posted to my original response to
you.

--
Rick (MVP - Excel)


"cjennings" wrote in message
...
That did fix the rounding problem but caused the rest of the formula to
mess
up. The next column was =IF(G13="","",(G13*E$5)) but it is showing #VALUE
now. I hateit when it fixes one thing but screws up the rest!
--
Thanks!


"Jacob Skaria" wrote:

Do you mean

=IF(F13="","",(F$5-F12) & "/" & I$8)

If this post helps click Yes
---------------
Jacob Skaria


"fractionwoes" wrote:

I am trying to set up a depreciation schedule and I want the
denominator of
fraction to stay a constant - do not round or reduce. For instance, if
the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72,
4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I
have
tried to format the cell to # ???/???, but Excel keeps reducing the
fraction.

That did the trick! You certainly know your stuff!
--
Thanks!


"Rick Rothstein" wrote:

Just a quick follow up to both my posting and Jacob's posting...

Using my method, the value in I8 will remain a real number that you can do
calculations with whereas using Jacob's method makes the value in I8 simple
text, so it would not be a number any more.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You can have a defined cell format that is dependent on a variable;
however, if I understood your request correctly, you can do what you are
asking for with some VBA code though. Give this a try...

Right click the tab at the bottom of your worksheet and then click View
Code from the popup menu that appears and then copy/paste the following
code into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
Const FormulaCell As String = "J8"
If Target.Address = "$I$8" Then
Range(FormulaCell).NumberFormat = "# " & String(Len(Target.Value), _
"?") & "/" & Target.Value
End If
End Sub

Next, change the example J8 cell reference I used in my code above to the
actual address of the cell that contains the formula you posted. Okay, now
go back to the worksheet and enter a value in cell I8... you need to do
this one time in order for the first format to be assigned (everything
will be automatic thereafter), so you can just type the number that is
already in there over again if you want. Now, whenever you change the
value in I8, the format in the cell you changed my example J8 reference to
will change to a Fraction format with the denominator being the value in
I8.

--
Rick (MVP - Excel)


"fractionwoes" wrote in message
...
I am trying to set up a depreciation schedule and I want the denominator
of
fraction to stay a constant - do not round or reduce. For instance, if
the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I
have
tried to format the cell to # ???/???, but Excel keeps reducing the
fraction.

The best way I have found to do this is to click in the cell in which you want to keep the "x/72" format. Go to the "Format" menu and click "Cells". Choose "Fractions" fro the categories list and then click on any of the choices under the "Type" menu that have "As" in front of them, such as "As halves (1/2)" and click OK. You then need to go back to "Format" and "Cells" and now choose "Custom" from the Categories list. Change the type from "# ?/2" to "# ?/72" and this will force Excel to reduce the fraction to a denominator of 72.