Solved: Number format

[Grantx]

I work with some data to 3 decimal points at 0.005 intervals, eg 108.770, 108.775, 108.780. Where the last number is a 5, I would prefer this to be a half, eg 108.77 1/2, 108.78 1/2.

This can't be done via Custom number format (fraction converts all figures after a decimal). Any ideas?

Grant.

[Simon Lloyd]

it can be done by worksheet formula using the substitute method like this:
Assuming figure is in A1 paste this in B1

=IF(A1<>"",SUBSTITUTE(A1,"5"," 1/2",1))

it is written in this format

=SUBSTITUTE(CELL TO CHANGE,"TEXT TO CHANGE","NEW TEXT",1)

[Bob Phillips]

=IF(RIGHT(D2,1)="5",LEFT(D2,LEN(D2)-1)&" 1/2",D2)

[Bob Phillips]

it can be done by worksheet formula using the substitute method like this:
Assuming figure is in A1 paste this in B1
it is written in this format

Simon,

Have you been sprinkling stuff on those cornflakes? If the number was 58.775, this would return 1/2 8.775, that is it changes the first 5 not the last..

It could be adapted like so

=IF(A1<>"",SUBSTITUTE(A1,"5"," 1/2",LEN(A1)-LEN(SUBSTITUTE(A1,"5",""))),"")

[Bob Phillips]

Just noticed, this errors if there is no 5 in the string

=IF(A2<>"",SUBSTITUTE(A2,"5"," 1/2",MAX(1,LEN(A2)-LEN(SUBSTITUTE(A2,"5","")))),"")

[unmarkedhelicopter]

This is one of the strangest threads I've ever seen.
Why would anyone want to write 8.775 as : "8.77 and half a hundredth" ???

[Simon Lloyd]

Hi Bob, knowing you you saw the flaw straight away.

Simon,
Have you been sprinkling stuff on those cornflakes? If the number was 58.775, this would return 1/2 8.775, that is it changes the first 5 not the last..

i must say i didn't test for this, and i have to be honest i wouldn't have known immediately how to fix it!

The lesson and info humbly accepted as always!

[Grantx]

Gentlemen,

Thank you for your suggestions. I'll try them out later.

Unmarkedhelicopter,

You're obviously not familiar with quoting conventions in the financial markets.

Grant.

[unmarkedhelicopter]

So what you are saying is that the whole of the financial market industry is run by numerically illiterate people with ponytails and porches ?

[Bob Phillips]

No he is saying that they have conventions in the financial business just as you do in whatever business you are in. Just because they are not the same as yours doesn't mean they are right, they are wrong, or they are illiterate. Good to see that prejudice is alive and well.

If you think they are so illiterate, try getting a job as a dealer (of any kind).

[Grantx]

Unmarkedhelicopter,

"numerically illiterate"? Do you mean "innumerate" ?

Not many ponytails these days. Porches are popular but the current fashion is to extend these into verandahs.

Thank you for the solution, xld.

Grant.

[unmarkedhelicopter]

Blackjack ?

I well understand conventions, and I well understand that 'some' of them are less than fully logical, I was merely pointing this out and included a smiley and a reference to pony tails and porches as an obvious stereotypical cliche reference. In case you are still wondering this was meant to be "funny".

The above is akin to a cabinet maker building an occasional table 1 foot and 41 mm in diameter.

Standard number conventions do NOT work like this example above and there are a few more like 5.23 being spoken as five point twenty three, and I'm as likely getting that changed as I am the above "half a hundred" convention or the fact that when you look up in the sky at night you see "The Moon" not a moon but "The Moon" as "Moon" is the name of our primary satellite, but you even get NASA talking about "the moons of Jupiter". The arguments go - "that people don't understand the distinction" but they never will unless the issue is pointed out.

Bob you obviously got out of the wrong side of the bed this morning, as you seem to on occassion, (I too am guilty of some wild responses, which is why I laud Ross's Budhist calm) the last time you blew up at me was when I was talking about security and said that "we ALL say we are concerned about security ... but live in houses with windows".
Which I assume you took to mean "people in glass houses ..." or some such, cos you blew ...
but it actually referred to an earlier post about how "any five year old with half a brick can circumvent a locked window"

At the time I did not respond but am fed up with this, if I want to insult you, you will know about it. If Grantx felt insulted then he can take it up with me, (Grantx, if you ARE insulted, that was not my intent and I appologise, just in case !). Just to be clear :- Bob, I think you are one of THE finest Excel experts out there, I have learned much from you ... but about this you are an ASS.

[unmarkedhelicopter]

Unmarkedhelicopter, "numerically illiterate"? Do you mean "innumerate" ?

No, I understand the distinction and am assuming that you can add up, subtract possibly multiply and divide. What I meat is that "traders" 'may' be unaware of the language that surrounds the use of numbers. And as I said in my other response this was meant to be an overblown stereotypical response and not an actual opinion on your profession. The text would have been much longer if I had to explain all this and that is why people use smilies to convey a subtext or attitude that goes along with the actual written statement. Having said that it would have been much shorter than the responses I've had to write since.

[Bob Phillips]

Bob you obviously got out of the wrong side of the bed this morning, as you seem to on occassion, (I too am guilty of some wild responses, which is why I laud Ross's Budhist calm) the last time you blew up at me was when I was talking about security and said that "we ALL say we are concerned about security ... but live in houses with windows".
Which I assume you took to mean "people in glass houses ..." or some such, cos you blew ...
but it actually referred to an earlier post about how "any five year old with half a brick can circumvent a locked window"

At the time I did not respond but am fed up with this, if I want to insult you, you will know about it. If Grantx felt insulted then he can take it up with me, (Grantx, if you ARE insulted, that was not my intent and I appologise, just in case !). Just to be clear :- Bob, I think you are one of THE finest Excel experts out there, I have learned much from you ... but about this you are an ASS.

YOu do not know me any more than I know you and you have no idea as to the context as to which I wrote this and as you say it is impossible to relay feelkings in textual biased medium such as the internet.

I enjoy winding up people who I believe are being pompous, or who act like an ASS as in your terminology. In this case, you clearly did that when you made that comment. In financial markets the margins are very small so it is necessary to emphasise the least significant digit, which is why they use such conventions. Either you knew(were attuned) to that and was just being totally flippant, in which case you post was unnecessary and not funny, or you knew nothing about it, in which case a modicum of restraint would have been in order.

I have also seen you around a lot, here and the other place, and I know that you are capable of considered thought and helpful suggestions, but you are also prone to shoot off a post that adds nothing to the thread, nothing to the quality of our life and is just plain distracting. If I can get a rise out of you on these occasions with a simple response ... well it brightens my day when things may be a bit tedious.

BTW, don't recall the other post so I cannot comment on it.

[Bob Phillips]

Thank you for the solution, xld.

Actually, it was Simon's solution. I just added to it to make it resilient in more situations.

[unmarkedhelicopter]

I will fight for my right to be an ASS ! (but as I said, I envy Ross's restraint).

I shoot off, and have seen others do the same, yourself, simon, rory, mdmacillop, yellolabpro, Sir babydum (to name but a few) have also indulged, I see no problem with this as I do not get distracted by it, whereas a new colour lid on a soft drink is worthy of an hours investigation I have enjoyed some of these distracted offshoots as I hope others have and also feel that this engenders a sense of community (and can be quite educational) and takes the edge of what is be be honest a bunch of people obsessed with (in the scheme of things) something quite minor. I will continue to do so but perhaps less so, in future, with anyone new (and thus of questionable humour) or who could be accused of taking themselves a little too seriously.

Further, I shall only correspond with you in a professional manner as without any clues as to intonation I will assume you are winding up. I think this is sad as it will make you more (to me) two dimentional, but if that is what you want ...

I shall however continue to hold you in a position of respect regarding Excel et al and your views upon it. (I hesitate to add a smilie as you may consider that to be flippant, ironic or sarcastic).

[Bob Phillips]

Nobody said you couldn't (God forbid that the moral police extend their influence), I am just saying that it is really not rational to complain in such strident language when someone does it back at you.

[Aussiebear]

This is one of the strangest threads I've ever seen.
Why would anyone want to write 8.775 as : "8.77 and half a hundredth" ???

I would, but according to Bob I stand on my head all day anyway, so i guess its perfectly acceptable behaviour

[unmarkedhelicopter]

Nobody said you couldn't (God forbid that the moral police extend their influence), I am just saying that it is really not rational to complain in such strident language when someone does it back at you.

Strident language ? Where ?

[Grantx]

Correction: Simon, thank you for the solution.

Unmarkedhelicopter,

apology accepted. Xld explains my reaction well.

Grant.