|Posted: January 2, 2010, 5:39 pm - IP Logged|
While updating my files, I tend to us short hand, ie. "200 K" or "2.45 M" or "29 M" to represent amounts. All of these entries are done manually. I'm attempting to automate this entry process and have the "new" amounts added automaticlly.
I've figured out the formula to identify the "K" or "M", but the multiply section isn't working correctly. It just gives me zeros. Below is what I'm trying to get to work. (I think the original entry is entered as text and the space & letter need to be seperated so that the numbers can become values and multipled properly.)
C1 contains 200 K
C4 contains the formula: @IF(@RIGHT(C1,1)="K",C1*1000,@IF(@RIGHT(C1,1)="M",C1*1000+C1*1000000," ")) [please note that Lotus uses the "@" sign in formulas instead of the "=" sign that Excel uses]
The results in C4 should be "$200,000.00", instead it displays "$0.00".
D1 contains 2.45 M - result in D4 should be $2,450,000.00
E1 contains 29 M - result in E4 should be $29,000,000.00
All results in row 4 are the same using the above formula.
Would someone please explain what I'm doing wrong so that the amounts come up correctly?
Thank you for any assistance ahead of time.
The only DUMB question is the one question you DID NOT ask...