Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Bond prices convert to decimals

I need to convert a bond price, which is expressed as (for example) : 99-07
and which really means 99 7/32. How can I do that in excel to be part of a
macro (like, convert everything with looks like 00-00 to 00 00/32, or even as
decimal ) . I'd appreciate any ideas anyone can offer me!!
Cheers
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 623
Default Bond prices convert to decimals

The formula would be quite simple:

=left(a1,2)+right(a1,2)/32

However, I suspect you also have prices over 100, so sometimes you would have 3
characters to the left of the '-'. If so:

=left(a1,find("-",a1)-1)+right(a1,2)/32

If you really need a macro, post back and I'm sure others will help you. Give
them an idea of how your data is store (ie, all over the sheet, in a column,
etc.)

--
Regards,
Fred


"J-EL" wrote in message
...
I need to convert a bond price, which is expressed as (for example) : 99-07
and which really means 99 7/32. How can I do that in excel to be part of a
macro (like, convert everything with looks like 00-00 to 00 00/32, or even as
decimal ) . I'd appreciate any ideas anyone can offer me!!
Cheers



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Bond prices convert to decimals

Fred - you are awesome. That was extremely helpful. Although - I have to say
that I am curious about a macro which could do that "seek and convert" for a
column of prices. Let's say I had :
97.5
101-20
99-8
100.1

So - is it even possible to do that? Convert those two "dashed prices
without screwing up the already decimaled prices?

Also - does anyone know about macros for the old mainframe AS4000 ?

"Fred Smith" wrote:

The formula would be quite simple:

=left(a1,2)+right(a1,2)/32

However, I suspect you also have prices over 100, so sometimes you would have 3
characters to the left of the '-'. If so:

=left(a1,find("-",a1)-1)+right(a1,2)/32

If you really need a macro, post back and I'm sure others will help you. Give
them an idea of how your data is store (ie, all over the sheet, in a column,
etc.)

--
Regards,
Fred


"J-EL" wrote in message
...
I need to convert a bond price, which is expressed as (for example) : 99-07
and which really means 99 7/32. How can I do that in excel to be part of a
macro (like, convert everything with looks like 00-00 to 00 00/32, or even as
decimal ) . I'd appreciate any ideas anyone can offer me!!
Cheers




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 623
Default Bond prices convert to decimals

This macro should do what you want:

Sub ConvertPrices()
For Each cell In Selection
If InStr(1, cell, "-") 0 Then
cell.Value = Left(cell, InStr(1, cell, "-") - 1) + Right(cell, 2) / 32
End If
Next cell
End Sub

Highlight the range you want it to work on, then execute the macro. It converts
only those cells which have a dash in them. It skips any others.

Can't help you with the AS4000 -- I never worked on that machine. You're
probably better off to post this as a stand alone question.

--
Regards,
Fred


"J-EL" wrote in message
...
Fred - you are awesome. That was extremely helpful. Although - I have to say
that I am curious about a macro which could do that "seek and convert" for a
column of prices. Let's say I had :
97.5
101-20
99-8
100.1

So - is it even possible to do that? Convert those two "dashed prices
without screwing up the already decimaled prices?

Also - does anyone know about macros for the old mainframe AS4000 ?

"Fred Smith" wrote:

The formula would be quite simple:

=left(a1,2)+right(a1,2)/32

However, I suspect you also have prices over 100, so sometimes you would have
3
characters to the left of the '-'. If so:

=left(a1,find("-",a1)-1)+right(a1,2)/32

If you really need a macro, post back and I'm sure others will help you. Give
them an idea of how your data is store (ie, all over the sheet, in a column,
etc.)

--
Regards,
Fred


"J-EL" wrote in message
...
I need to convert a bond price, which is expressed as (for example) : 99-07
and which really means 99 7/32. How can I do that in excel to be part of a
macro (like, convert everything with looks like 00-00 to 00 00/32, or even
as
decimal ) . I'd appreciate any ideas anyone can offer me!!
Cheers






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Lats and longs to decimals dharmik Excel Worksheet Functions 2 March 30th 06 07:48 PM
convert lats and longs to decimals dharmik Excel Worksheet Functions 1 March 28th 06 07:12 PM
Convert $ amount from excel to access? No decimals but 00 at end Nicole Excel Worksheet Functions 1 January 4th 06 05:37 PM
Convert Seconds and decimals to time bagoxc Excel Discussion (Misc queries) 5 January 4th 06 12:41 AM
How to convert numbers with apostrophe's (ex. 219'2) to decimals? Kaci Excel Worksheet Functions 2 June 15th 05 03:48 PM


All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"