# Posts by censura

• ## Best profit hour

I want to calculate which is the most profitable hour in the day across our business.

I have a table see attached That breaks down the profit per hour from 8 till 9pm.

One thing we need to ignore the first hour in the day as it is not a full hour and not reflective of the day

I want a new column which shows the daily highest hour profit and the hour in the next column ie £6750 11.00am

I have attached a image also hope this helps to explain

Thank you

PS i have this placed here as well http://www.excelforum.com/exce…fit-hour.html#post3479276 and

[/INDENT]

• ## high low data extraction

Re: high low data extraction

Hi, thank you but how would this work for 365 days of data ie over 36,000 rows to ensure I get 365 high and lows 1 for each day

• ## high low data extraction

Hi and thank you for looking, my problem is this:

I have a table of data there are 4 columns

Day/time, open price, close price, high, low

The data rows are prices at 5min intervals from 8.00am to 4.30pm each day 102 rows of data per day.

The reason for the problem is the first row of each day ie the 8am row is not a true representation of the price movement in that 5min it also reflects any price action from the close of the previous day ie from 4.35pm to 7.55am ie out of hours market moves. The data is for the UK footsie 100

So to avoid this I want to extract the high and low in 2 seperate columns for each day but take the range from 8.05am to 4.30pm ie what is the high and low for that range (1 high, 1 low per day shown)

Thank you

• ## importing data

Simply:- I have a spreadsheet which i wish to import data into.The imported data is contained in seperate spreadsheets which all have thier own title ie british pound u.s.d. cash.xls. The data sheets have the data ordered identicaly ie same column headings and same number of rows so A2 in one spred sheet "date" is the same in all sheets and all sheets are held in a folder DATA.

I have a summary sheet which i use to analyis the data the same querys being applied each time. Currently by linking the summary spread sheet to one of the data sheets eg. British Pound u.s.d.xls i can analyis the data for that sheet. If i want to look at another data sheet i have copied the summary sheet and linked it to say British Pound JPY.xsl datasheet. This is very time consuming having 80 data sheets also if i make a chnage to how the data is analised i have to go back and change all the sheets.

What i would like to do is be able to import any of the datasheets by selecting from a dropdown list in the summary sheet.

Ie if i select from the dropdown list bristish pound u.s.d cash it pulls in the data for this if i select say british pound jpy then this data is pulled in and overwrite the previous data.

Here are 3 files to illustrate my problem 2 are data sheets and one is the summary sheet for analysis of the data

can this be done, hope you can help