Help with interest rates formatting

[golf4]

Hi everyone -

Just a quick question: I'm continuing to develop an income calculation spreadsheet for a public housing agency. What I'm looking for is help in asset interest rate cell formatting. I've got the cell(s) formatted for "percentage", but in cases where staff wants to enter ".91" for .91%, the cell appears as "9.1" instead. Is there a way to format the cell(s) that would enable us to enter ".91" and have to formula calculate as, for example, "$5000 x .91%"? THANKS FOR THE HELP (AGAIN)!!!

 

[Legacy 98055]

Hi
Your staff will need to understand that .91 = 91/100ths or 91%. .091 is what should be entered for 91/1000ths or 9.1%. I understand your problem. Excel has just chosen the mathematically correct way to display the value. Not the plain English way.

Tom

 

[golf4]

Hey Tom -

Thanks for the help. I really appreciate it.

 

[Alriemer]

Could use custom number format 0.00"%" and divide the cell by 100 in calculations.

 

[stevebausch]

Yep, you need smarter staff, or a clever programmer


If the Wizards haven't suggested a Validation scheme, perhaps there is need for some code in Worksheet_Change.

If this sounds like the path you want to take, then post further information.

The rough outline:

Worksheet_Change is triggered.

It checks which cell is being modified.

If the cell is your "problem" cell, then the code would need to step in and make judgement on how to modify/validate the entry.

You would need to imagine yourself watching over the user's shoulder. What are you watching for? A range of values? A specific format? Is there a "dead giveaway" when the entry is bad? Is there a "dead giveaway" when the entry is good?

Please get back with us, this sounds like a challenge.