Microsoft Excel Q&A

Uncle Festivus · 22 February 2009

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 · 22 February 2009

Uncle Festivus said:
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 · 22 February 2009

rryall said:
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 · 22 February 2009

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

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

derty · 22 February 2009

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

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

Uncle Festivus · 23 February 2009

Thank you comrades

Aurum · 24 February 2009

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 · 27 February 2009

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 · 27 February 2009

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 · 1 March 2009

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 · 1 March 2009

wabbit said:
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 · 2 March 2009

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 · 2 March 2009

BentRod said:
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.

Nick Radge said:
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 · 2 March 2009

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 · 2 March 2009

Nick Radge said:
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 · 2 March 2009

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 · 2 March 2009

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 · 3 March 2009

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 · 3 March 2009

nizar said:
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 · 3 March 2009

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

Or

Post the worksheet.

wabbit

Microsoft Excel Q&A

Uncle Festivus

rryall

Uncle Festivus

gfresh

derty

Uncle Festivus

Aurum

nizar

wabbit

www.wabbit.com.au

BentRod

ZZZZzzzzZZZZzzzzzzzz

nizar

Nick Radge

wabbit

www.wabbit.com.au

Nick Radge

jackson8

wabbit

www.wabbit.com.au

Nick Radge

nizar

Aurum

wabbit

www.wabbit.com.au

Similar threads

Connect with us