Hi. I'm doing a stock take at work and I want to store resistor values as numbers but have them displayed in resistor convention e.g -
120 is displayed as 120R
12,300 is displayed as 12K3
1,100,000 is displayed as 1M1
I can't seem to get the hang of all these #,## 0's and such, any help would be greatly appreciated. thanks.
I don't think the standard formatting tools will do what you want in the same cell as your data. Could you compromise and enter the data in one column, and then have the next column that shows the resistor format? You could create a custom function that converts the number to the resistor format.
Matt
i would rather store it as a number, i will look into the possibility of using some kind of VB code - thanks anyway
Hello benstreek,
I know nothing about "store resistor values", but from the example numbers you have provided, this formula seems to work. Your number in A1, then in B1
=IF(A1<1000,"R",IF(A1<1000000,"K",IF(A1<1000000000,"M","")))
then in C1
=IF(A1<1000,A1&B1,IF(A1<1000000,(A1-MOD(A1,1000))/1000&B1&MOD(A1,1000)/100,(A1-MOD(A1,1000000))/1000000&B1&MOD(A1,1000000)/1000/100))
There may be an easier way to do it, also these two formula's could be combined
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
I don't know that there is a way to get what you want strictly using number formats. The closest I can come is a number format code of [<1000]0"R";[<1e6]0.00,"K";0.00,,"M". This will display your numbers as
120 -> 120R
12300 -> 12.30K
1100000 -> 1.10M
I am not aware of a way to put the letter in in place of the decimal point.
Any other solution (even a VBA solution) is going to need to store the numbers as part of a text string. This means that 12,300 actually needs to become the text string "12K3" and if you need access to the value 12,300, you'll need to either figure out how to get that value back from the text string or store the 12,300 elsewhere. Since it sounds like you're just taking inventory, it probably doesn't matter how you do it.
The closest i can get is this.. (I'm using those basic functions only)
Put the input in your A1
then
=IF(LEN(A1)<=3,CONCATENATE(A1,"R"),IF(AND(LEN(A1)>=4,LEN(A1)<=6),CONCATENATE(LEFT(A1,LEN(A1)-3),"K",MID(A1,LEN(A1)-2,1)),IF(LEN(A1)>6,CONCATENATE(LEFT(A1,LEN(A1)-6),"M",MID(A1,LEN(A1)-5,1)),"??")))
But you still need to fill those cell with "??", so yeah, not a perfect solution I guess.
For the number-to-string conversion, maybe it's a good idea to make two columns for both the number (120) and the string (120R), and you can just hide/unhide the whole column if you want to print the page or something
Hope it helps
Last edited by igesta; 11-17-2006 at 02:22 AM.
=IF(LEN(A1)<=3,CONCATENATE(A1,"R"),IF(AND(LEN(A1)>=4,LEN(A1)<=6),CONCATENATE(LEFT(A1,LEN(A1)-3),"K",MID(A1,LEN(A1)-2,1)),IF(LEN(A1)>6,CONCATENATE(LEFT(A1,LEN(A1)-6),"M",MID(A1,LEN(A1)-5,1)),"??")))Originally Posted by igesta
is the same as
=IF(LEN(A1)<=3,A1&"R",IF(LEN(A1)<=6,LEFT(A1,LEN(A1)-3)&"K"&MID(A1,LEN(A1)-2,1),LEFT(A1,LEN(A1)-6)&"M"&MID(A1,LEN(A1)-5,1)))
but neither suppress the odd 0 (zero) after the K or M
--
=IF(LEN(A1)<=3,A1&"R",IF(LEN(A1)<=6,LEFT(A1,LEN(A1)-3)&"K"&IF(MID(A1,LEN(A1)-2,1)="0","",MID(A1,LEN(A1)-2,1)),LEFT(A1,LEN(A1)-6)&"M"&IF(MID(A1,LEN(A1)-5,1)="0","",MID(A1,LEN(A1)-5,1))))
does that.
hth
---
Si fractum non sit, noli id reficere.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks