Excel Function?

freeze4real · 11-10-2014 6:12pm #1

Hi Guys,

I am analyzing data on excelabout 5000 US companies using various variables gotten Bloomberg. Vraibles such as earnings per share, dividends etc. I'm using 10years as my timescale.

I did a test run of getting the data o Bloomberg and the format table like manner. Ideally I would like to have the companies on Column A, Column B might be the year, Column C might be dividends per share, Column D might be earnings before tax etc.

I did a test run and it seems that all the info are all in rows for each company. E.g all the info for apple will be on Row b1 - G8, then amazon from G8 - Z8 etc.

Now my question, is there a function in excel that I could use to arrange the data in a table like format?

Buford T Justice · 12-10-2014 1:42am

An excel sheet is one big table.

What sort of formatting are you looking to perform?

Dub_Steve · 12-10-2014 8:56am

You mean transpose?

You select the row, copy and then click paste special then transpose

freeze4real · 12-10-2014 4:12pm

Dub_Steve wrote: »

You mean transpose?

You select the row, copy and then click paste special then transpose

Yea I looked at transpose and wasnt sure if it'll be able to handle the data. I'm looking at 5,000 - 7,000 companies with 20 - 30 variables for a perid of tens years. That's alot.

freeze4real · 12-10-2014 4:13pm

Buford T Justice wrote: »

An excel sheet is one big table.

What sort of formatting are you looking to perform?

I would like to have the companies on Column A stacked vertically, then all the variables stacked horizontally e.g Column B might be the year, Column C might be dividends per share, Column D might be earnings before tax etc

Senna · 12-10-2014 4:24pm

Very hard to understand what you're asking,
are you wanting data already entered to be change from vertically for each company to horizontal?

freeze4real · 12-10-2014 4:28pm

Senna wrote: »

Very hard to understand what you're asking,
are you wanting data already entered to be change from vertically for each company to horizontal?

Its in an horizontal format. I'd like it to be vertical.

MagicMarker · 12-10-2014 9:00pm

Can you not just use a pivot table?

freeze4real · 12-10-2014 9:16pm

MagicMarker wrote: »

Can you not just use a pivot table?

Thanks, I didn't think of pivot table as I'm not sure it would allow a regression analysis. I'll try this tomorrow. Thanks again.

FionnK86 · 15-10-2014 12:54pm

Can anyone give me a hand with selecting a function in excel?

I've to update an inspection list starting every week. For every week that goes by, items have to become an extra 7 days overdue.

So example: Item #1 is overdue by 4 days on 13th October
Item #1 is overdue by 11 days on 20th October

Beano · 15-10-2014 1:30pm

FionnK86 wrote: »

Can anyone give me a hand with selecting a function in excel?

I've to update an inspection list starting every week. For every week that goes by, items have to become an extra 7 days overdue.

So example: Item #1 is overdue by 4 days on 13th October
Item #1 is overdue by 11 days on 20th October

why not just have one column with the due date and another column with the datediff between todays date and the due date? That way it always shows the correct number of days overdue. Or does it specifically have to be updated every seven days?

FionnK86 · 15-10-2014 1:55pm

Beano wrote: »

why not just have one column with the due date and another column with the datediff between todays date and the due date? That way it always shows the correct number of days overdue. Or does it specifically have to be updated every seven days?

It has to be updated

MagicMarker · 15-10-2014 2:17pm

Try something like this...

=(ROUNDDOWN(((TODAY()-A4))/7,0)*7)

A4 would be whatever cell contains your date. So it will only update in 7 day periods from the date you're looking at, so if the item is 13 days out of date it will still say 7, until the next day when it will change to 14.

FionnK86 · 15-10-2014 2:22pm

MagicMarker wrote: »

Try something like this...

=(ROUNDDOWN(((TODAY()-A4))/7,0)*7)

A4 would be whatever cell contains your date. So it will only update in 7 day periods from the date you're looking at, so if the item is 13 days out of date it will still say 7, until the next day when it will change to 14.

No luck, error message

MagicMarker · 15-10-2014 2:59pm

Hmm, works in Excel 2003 anyway.

Maybe the ROUNDOWN or TODAY function is different in newer versions?

Could your date be stored as text maybe?

Beano · 16-10-2014 12:03pm

I've just tried Magicmarkers formula in excel 2010 and it works fine. I imagine it would also work fine in excel 2007.

any chance you might tell us what the error message is?

drdidlittle · 16-10-2014 12:07pm

Can you post your formula here?

Beano · 16-10-2014 12:08pm

Another option would to do what i suggested but set the sheet to not autocalculate. You then tell it recalculate manually every 7 days. A bit of a faff but presumably this is an old paper based process that you are trying to computerise but without updating it to exploit a computers capabilities.

Excel Function?

Comments