formatting decimals in excel

[Gordon Gekko]

when i put ES prices into excel, they're either like 1009.25 or 100925.

i don't really care if i use a decimal or not, but i would like all the prices to look the same. how can i do this in excel without dividing or multiplying?

for example, how can i have excel change the format of 1009.25 to 100925?

or how could i have it change 100925 to 1009.25?

i've been trying to do custom formatting, but i can't get either of the two ways to work.

anyone know how?

thx

 

[EricP]

If I understand correctly, the data is coming into Excel from your data source that way, right?

If so, I don't think it's a formatting issue, but it's an issue with the data itself. That being said, I think there is an easy fix. Simply create a second Excel column beside the first to 'fix' the data as follows. Any data value that is over 2000 (something arbitrarily higher than the correct index reading of ~1000) needs to divided by 100. You would do this as follows (assume that you are 'fixing' cell A6 and putting the correct value in cell B6). The formulae for B6 would be:

=IF(A6>2000, A6/100,A6)

Then copy this formulae from B6 down the entire B column to 'fix' the data. You will want to keep the bad data in the spreadsheet, but make all other spreadsheet references to the 'good' data in column B.

Good luck,
-Eric

Quote from Gordon Gekko:

when i put ES prices into excel, they're either like 1009.25 or 100925.

i don't really care if i use a decimal or not, but i would like all the prices to look the same. how can i do this in excel without dividing or multiplying?

for example, how can i have excel change the format of 1009.25 to 100925?

or how could i have it change 100925 to 1009.25?

i've been trying to do custom formatting, but i can't get either of the two ways to work.

anyone know how?

thx

 

[Gordon Gekko]

Quote from EricP:

If I understand correctly, the data is coming into Excel from your data source that way, right?

If so, I don't think it's a formatting issue, but it's an issue with the data itself. That being said, I think there is an easy fix. Simply create a second Excel column beside the first to 'fix' the data as follows. Any data value that is over 2000 (something arbitrarily higher than the correct index reading of ~1000) needs to divided by 100. You would do this as follows (assume that you are 'fixing' cell A6 and putting the correct value in cell B6). The formulae for B6 would be:

=IF(A6>2000, A6/100,A6)

Then copy this formulae from B6 down the entire B column to 'fix' the data. You will want to keep the bad data in the spreadsheet, but make all other spreadsheet references to the 'good' data in column B.

Good luck,
-Eric

EricP,

thx.. yeah, that is what i'm going to do for now. the incoming data for this cell (DDE from eSignal) was 1009.25, but the rest of my numbers are without decimals; so i just multiplied the DDE link by 100.

i think the best solution would be to make a custom format. excel lets you do number format codes. the problem is, i couldn't figure out the correct code to do this (if it's even possible).