Increments less than one in format control box for spinner

[srpettew]

I've added a spinner to my NPV analysis with regards to the discount rate.
What I'd like to do is use increments of .10 or .25 as opposed to just whole
numbers. Don't understand why this is not a built in feature in the format
control box. I'm using 2003. Any help is appreciated.

 

[T. Valko]

Use an intermediate cell to calculate the increment then refer to that cell.

For example, A1 is the spinner linked cell. Use B1 to calculate the
increment you want to use:

=A1/10
=A1/4

Then refer to B1 in the formulas that you want to calculate when you click
the spinner.

 

[Gord Dibben]

Use a helper cell to do the math.

Linked cell is A1

In B1 enter =A1*.25

B1 becomes your working cell for the analysis.


Gord Dibben MS Excel MVP

 

[srpettew]

Thanks very much for the reply but I'm not sure I understand. Currently I am
using a helper cell (E34 in my case) to serve as the "linked" cell in the
format control box. I then have the discount rate (expressed as a %) in cell
C34 with the formula, =E34/100. So the spinner is located in cell D34 (in
between) but the discount rate can only be changed using increments of 1 (or
some other whole number).

My goal is to use discount rates such as 9%, 9.25%, 9.50%, 9.75% to provide
more accurate NPV's. Perhaps I just didn't understand. Does my
clarification above change your answers?

Again, thank you both for replying.
SP

 

[Gord Dibben]

Make D34 the linked cell for the spinner.

It gets incremented by whole integers of 1

In E34 =D34*.09

In C34 enter =E34/100

Alternative to the =D34*.09 you could use =D34*F34 where F34 is .09 or
..0975 etc.


Gord

 

[srpettew]

Thanks Gord but it doesn't work. D34 is the spinner (the cell with the
up/down arrows that were drawn in. It gets too complicated adding more
linked cells. I guess the biggest question is why doesn't MS allow for
increments of less than one? When working with interest rates, discount
rates, etc, rare is the case when you have a whole number as the rate.

Again, thanks for the attempt to help.
SP

 

[Gord Dibben]

Pardon me, but it does work.

The Spinner is not "in the cell", just lays on top.

You can still have D34 as the linked cell.

I don't know the answer to the "why doesn't MS".

Happy NPV'ing


Gord