Microsoft Excel Q&A

[Uncle Festivus]

General thread for MS Excel questions & answers.

My question - how can I convert a number eg 20060410 into a date Excel can understand eg 2006/04/10 ie a number to a date? Formatting for date does nothing. (This date number is exported from Bullcharts, but Excel doesn't seem to understand it as a date?) Formula or macro???? Thanks

 

[rryall]

General thread for MS Excel questions & answers.

My question - how can I convert a number eg 20060410 into a date Excel can understand eg 2006/04/10 ie a number to a date? Formatting for date does nothing. (This date number is exported from Bullcharts, but Excel doesn't seem to understand it as a date?) Formula or macro???? Thanks

Hi Uncle,

If 20060410 is in cell A1 use:

=CONCATENATE(RIGHT(LEFT(A1,6),2),"/",RIGHT(A1,2),"/",LEFT(A1,4))

You can then copy and past special values for Excel to read it as a date.

Hope that helps

 

[Uncle Festivus]

Hi Uncle,

If 20060410 is in cell A1 use:

=CONCATENATE(RIGHT(LEFT(A1,6),2),"/",RIGHT(A1,2),"/",LEFT(A1,4))

You can then copy and past special values for Excel to read it as a date.

Hope that helps

Thanks for that, but the date comes out as 4th of Oct 2006 instead of 10th of April 2006? The original format is yearmonthday...? I need day/month/year... thanks again

 

[gfresh]

Just have to move the two middle expressions around, e.g.

=CONCATENATE(RIGHT(A1,2),"/",RIGHT(LEFT(A1,6),2),"/",LEFT(A1,4))

 

[derty]

this will do it too, with the yyyymmdd date at A1.

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

 

[Uncle Festivus]

Thank you comrades

 

[Aurum]

Whilst concatenating strings will work it will cause problems with international settings. The best way to do it is with the date function,

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

This will work no matter what international date settings you have.

Aurum.

 

[nizar]

Hi,

I need some help with correlation.

What I have now in Excel (exported from MS) is the correlation of the top 100 stocks to the XTO (ASX100) index.

What I need is the correlation of each of these stocks to each other.

Any ideas?

(I can obviously do it manually by dividing them all up, but its very tedious....)

Thanks.

 

[wabbit]

Even if you're looking at comparing each ASX100 constituent against each other constituent, that's nearly 5000 operations to complete the 100 x 100 matrix of comparisons (as you only test X vs Y, there is no need to test Y vs X)

How much time have you got to perform the computations?
How are you going to view the results?
What sort of filtering or limiting criteria is there?

Just like as has been discussed elsewhere, an option is to test all the constituents against the baseline, leaving the hard(?) part to figure out what is the best method to do the cross analysis??



wabbit

 

[BentRod]

To all the gun Excel users, what is the best way to learn?

Did you guys do courses or were a couple of books enough?

 

[nizar]

leaving the hard(?) part to figure out what is the best method to do the cross analysis??

Yeh this is what I mean, right now Im doing the cross-analysis manually.

 

[Nick Radge]

I was wondering if anyone knows how to change the default settings on a chart plot? I'm doing a scatter graph and want to change the font size from 10 to 8 as well as the format the marker.

Thanks in advance...

 

[wabbit]

To all the gun Excel users, what is the best way to learn?

Did you guys do courses or were a couple of books enough?

Have a project in mind and keep working to attain that project goal. Whenever you don't know how to complete the next step, research and ask questions. Eventually you will have gained enough knowledge to challenge yourself to another project, maybe with some more complex requirements.

I was wondering if anyone knows how to change the default settings on a chart plot? I'm doing a scatter graph and want to change the font size from 10 to 8 as well as the format the marker.

Thanks in advance...

Being very careful which object you select by right-clicking in the chart, select "properties" and do whatever formatting etc you like. The hardest part is selecting the right object! The chart itself contains objects such as, the plot area, axis, data series (this is the one you want to change for the scatter plot), legends, titles, axis titles, axis scales and many more.


Hope this helps.

wabbit

 

[Nick Radge]

Thanks wabbit, but I want to keep the new formatting. How do I set the default to the new formatting? Example, the marker on the scatter graph?

 

[jackson8]

Thanks wabbit, but I want to keep the new formatting. How do I set the default to the new formatting? Example, the marker on the scatter graph?

hi nick

this may help
when you right click on chart and open chart type dialogue box there is a option on the bottom left hand side for default formatting re set as default chart or check the default formatting box either one of these
should acheive what you are after

 

[wabbit]

Depending on which version of Excel you have:

2003 (going from memory here): right click on the chart and select chart type, custom types, add. This will save the chart as it appears now. Next time you want a chart like this, start the chart wizard, select custom type.

2007: Custom charts are called "templates".From the chart tools, design menu, select save as template. Then it's the same deal as for 2003 for creating a new chart (remembering that custom chart types is now called templates).



wabbit

 

[Nick Radge]

Thanks. That seems to work, but it means all work to date needs to be reinput into the new format...arrrggg...back to it!

 

[nizar]

Can anybody help with a division cross-analysis?

For example.

On the top columns Iv got A, B, C, etc...
On the left hand side rows 1, 2, 3, etc...

I want to divide every everything up by everything else one by one.

For example.

Down column A, I want to have the values for A/1, then A/2, then A/3, A/4, etc.

BUT when i put in the cell =(b6/b7) and then in the next column =(b6/b8) and then =(b6/b9) and then =(b6/b10) then I want to drag and drop and apply the same formula Excel doesnt seem to understand.

For example I have entered manually =(a2/a4), =(a2/a5) all the way until =(a2/a14) but then when i drag and drop the very next cell has autofilled a formula of =(a15/a10) and the next one =(a16/a10), etc...

It seems it cant understand the pattern even after I gave it >10 examples to work off !!

Can anybody help please?

Thanks.

 

[Aurum]

On the top columns Iv got A, B, C, etc...
On the left hand side rows 1, 2, 3, etc...

I want to divide every everything up by everything else one by one.

For example.

Down column A, I want to have the values for A/1, then A/2, then A/3, A/4, etc.

From your description the first formula should go in B2 and should be =B$1/$A2. Or, I have misunderstood what you mean.

Mike.

 

[wabbit]

Have a look in the help files for cell referencing; absolute vs. relative

Or

Post the worksheet.


wabbit