Bit of help with this excel file, any ideas ? Very stuck

[Combat squirrel]

Hi I have some data in a txt file, its written like this:

16:23:20, 9.83, 0.00,31.79, 0.00,%103.5699798583984 , 0.13" *******"
16:33:21, 9.83, 0.00,31.79, 0.00,%103.6629474639893 , 0.21" *******"
16:43:23, 9.82, 0.00,31.78, 0.00,%102.2954700469971 , 0.36" *******"
16:53:24, 9.82, 0.00,31.76, 0.00,%102.3343479156494 , 0.03" *******"

(copied from the txt file)

Now when I copy and paste into excel obviously the whole lot goes into 1 column, also I cannot copy columns in one go from the txt file, select all or selecting lots of data only lets me go one row at a time and not columns (i.e. normal windows copy and paste behavior)

How can I get the data into excel splitting the columns into there own columns in excel, like:

16:53:24, ||9.82||, 0.00,31.76, ||0.00,||%102.3343479156494 ,|| 0.03"

The above for example, columns properly separated as shown by || above in excel, any ideas?

 

[Combat squirrel]

Paste it into Excel.
Highlight the column with it in.
Go to: Data > Text to Columns
Select Delimited > Next
Select comma > next
Set whatever formatting you require > Finish

You sir are some sort of god like non corporal awesome úber being, thank you very much

 

[Combat squirrel]

Hi ! One more question if anyone knows....

Iv got columns of this:

0
100
200
300
400
500

And I want them to appear as:

0000
0100
0200
0300
0400
0500

The 2nd set of numbers are whats input into excel, however excel defaults them to appear as '0' then '100' etc, how do i change this ? (looked for ages cant for the life of me find out how do it)

 

[Combat squirrel]

Doh, cheers anyways, now im stuck on adding a 2nd Y data series on my graph, this things impossible to use , I looked in the help file it says while on chart view go to format and 'add 2nd series' its not in the menu as it says it will be in excel ! dag nammit

 

[Combat squirrel]

Right-click on the cells, Format cells, Number tab, Custom, 0000

That will make all cells show as 4 digits.

Cheers sir, you are awesome Any ideas on how to add a 2nd 'Y' axis data to my graph, unfortunately there's like only 7/8 sets of data (numbers) in the graph, lined up to the corresponding x value which I want it to appear as, like this:
13:53:08 108.78%
14:03:08 108.77% 1.91
14:13:09 108.35%
14:23:11 108.46%
14:33:12 108.20%
14:43:12 107.11%
14:53:13 106.58%
15:03:14 107.03% 2.35
15:13:14 107.71%
15:23:15 106.86%
15:33:17 106.38%
15:43:17 106.31%
15:53:18 104.67%
16:03:19 104.61% 3.3

X Y1 Y2

Iv put underneath the sample number how Id like them to appear on my graph, Y1 is already on there obviously to make the graph and appears fine, adding Y2 correctly is making me pull my hair out

EDIT: the above numbers dont appear as written when posted so its columns 1,2,3 being x,y1,y2, you have to imagine it i guess ?

 

[Combat squirrel]

Not sure I understand, sorry.

Well the 1st 2 columns of data above make x and y in a graph I have in excel, thats fine. However the last 'data set' ill term it 'y2' (as I want it to appear on the Y column, against X) I cant put into the graph and make it work, it always goes wrong, for example:

14:03:08 Is the X value, its time, 108.77% is the current Y value 1.91 is a 2nd Y value I want to overlay on the graph, but from the data sample I took there are not many 'y 2 'values in the column but they are lined properly to the 'correct' X value, i.e. 1.91 has to appear next to/corresponding to time 14:03:08 on the graph, and so on and so forth, any ideas? greatly appreciated for help so far

 

[Combat squirrel]

You sir are a genious, however I want excel to roughly display this:

My ACTUAL graph so far being this, I want to overlay tide depths at corresponding times:

The problem being now is before I wanted tide depth 1.91 next to 14:03 for example but now I need whats below to be the 'blue bar' (which iv done) on the graph

time transmittance
13:53:08 108.78%
14:03:08 108.77%
14:13:09 108.35%
14:23:11 108.46%
14:33:12 108.20%
14:43:12 107.11%
14:53:13 106.58%
15:03:14 107.03%
15:13:14 107.71%
15:23:15 106.86%
15:33:17 106.38%
15:43:17 106.31%
15:53:18 104.67%
16:03:19 104.61%

And whats below to be a 'red bar' on the graph,

Tide H Time
2.15 0000
1.75 0100
1.87 0200
2.53 0300
3.57 0400
4.72 0500
5.65 0600
6.13 0700
6.06 0800
5.49 0900
4.59 1000
3.57 1100
2.64 1200
2.03 1300
1.91 1400
2.35 1500
3.3 1600
4.51 1700
5.66 1800
6.42 1900
6.57 2000
6.12 2100
5.2 2200
4.03 2300

What I DO NEED the graph to do is line up '1.91 to 14:00' hours and plot it correctly against the time chart on the bottom of the current 2nd graph I have pictured.

Many thanks for anyones help, im doing an ocean science degree not and I.T. one ! lol, 1st time iv ever done proper office/excel work and they expect us to just know how to do it

 

[Combat squirrel]

I got one!

Can i convert numbers in Excel like this:

1
2
3
4
5
6
etc

To 1, 2, 3, 4, 5, 6 etc (and easier into Word)

Cheers

That I can help you with, select the numbers right click ---> copy--->then you need to paste them, but select 'paste special'------->on box that comes up press 'transpose'---> ok------> happy days

 

[Combat squirrel]

Cheers for that Dutch Guy any clues on how to do that ? Is there nothing I can do to to line up the x axis value to the scale on the main graph ? an advanced excel function perhaps ?

 

[Combat squirrel]

What you can try is make the X-axis a lot longer with intervals of 10 minutes instead of one hour intervals, that way you can plot both the values on the timeline.

How would I achieve this in excel ? also how do I make the 2nd Y axis on the right - like i said i really dont know how to do it, dont even know what buttons to press

 

[Combat squirrel]

Ok made some progress, produced this:

However, 1 more problem remaining (as you can see from the graph), my data for the tide heights and transmittance runs over 2 days over a 24 hour clock, and excel has plotted all data points on '1 day' (what it thinks is one day), how do I make excel plot the graph 'sequentially' just following the data down the column and plotting the graph so basically my x axis runs 01:00 to 23:00 day 1 then again 01:00 to 23:00 day 2 if that makes sense,

EDIT:1 other problem, how can I label my 2nd Y axis 'Transmittance'

below is the data from the tide depths part, left the transmittance data out as its 100's of rows long, any help anyone can give will be greatly appreciated

time depth
00:00:00 2.15
01:00:00 1.75
02:00:00 1.87
03:00:00 2.53
04:00:00 3.57
05:00:00 4.72
06:00:00 5.65
07:00:00 6.13
08:00:00 6.06
09:00:00 5.49
10:00:00 4.59
11:00:00 3.57
12:00:00 2.64
13:00:00 2.03
14:00:00 1.91
15:00:00 2.35
16:00:00 3.3
17:00:00 4.51
18:00:00 5.66
19:00:00 6.42
20:00:00 6.57
21:00:00 6.12
22:00:00 5.2
23:00:00 4.03
00:00:00 2.86
01:00:00 1.92
02:00:00 1.43
03:00:00 1.54
04:00:00 2.27
05:00:00 3.49
06:00:00 4.85
07:00:00 5.96
08:00:00 6.53
09:00:00 6.42
10:00:00 5.72
11:00:00 4.65
12:00:00 3.47
13:00:00 2.41
14:00:00 1.7
15:00:00 1.54
16:00:00 2.04
17:00:00 3.14
18:00:00 4.58
19:00:00 5.92
20:00:00 6.78
21:00:00 6.92
22:00:00 6.34
23:00:00 5.26

 

[Combat squirrel]

anyone? dutch guy you about good sir ?

 

[Combat squirrel]

hey dude, no pic appeared on your post, have added u to msn when u log on

 

[Combat squirrel]

Is this what you mean?
What I did was make the scale in 10 minute intervals and plot the percentages next to the times on the scale, the depth measurements I put on every whole hour, that way the data matches the time it was taken at.

I hope you understand, I can also e-mail the file if you want, send me an e-mail to the mail address in my sig and I'll send you what I have.

Hey dutch guy is sort of what I mean yes, and if you could email the file that would be awesome, I did add you to msn, but it seems your never on it still very stuck now, many weeks later

 

[Combat squirrel]

Oh another problem if anyone could be so kind.........

In notepad I have these numbers:

58.37
60.44
62.58
64.5
66.06
65.64
68.24
70.4

When I copy and paste them into excel they appear as such, but I want them to appear as percentages, i.e. using the top number 58.37 %..........as soon as i click the % button excel it changes it to 5837 % , like this:

5837%
6044%
6258%
6450%
6606%
6564%
6824%
7040%

obviously wrong, how do I make it appear properly ? I tried to 'format cells' and add 2 decimal places but then it just turns out like this:

5837.00%
6044.00%
6258.00%
6450.00%
6606.00%
6564.00%
6824.00%
7040.00%

Whats weird though is if I delete 1 cells numbers and type in 58.37 and hit the % button it correctly changes it to 58.37%

RANDOM ! how do I sort this anyone ?

 

[Combat squirrel]

Works fine for me, you sure the cells arent formatted as percentage?

Says there formatted as general

EDIT: Just copied data to txt file and tried on another pc, same thing happens , didnt think txt files saved 'content data' information, I was hoping it would just be 'raw' data and excel shouldnt be playing silly buggers again?

 

[Combat squirrel]

Why not create a formula that divides the 5837% by 100?

GENIUS /laughs at own dumbness

RIGHT THEN, LAST PROBLEM TO SOLVE THEN ITS DONE!

Iv now got this:

Only problem is I need the X Axis to be at regular intervals,

Sample of data from Transmittance X axis:

14:03:08
14:13:09
14:23:11
14:33:12
14:43:12
14:53:13
15:03:14
15:13:14
15:23:15
15:33:17
15:43:17
15:53:18
16:03:19
16:13:19
16:23:20
16:33:21
16:43:23
16:53:24
17:03:24
17:13:25
17:23:26
17:33:26
17:43:27
17:53:29
18:03:30
18:13:30
18:23:31
18:33:32
18:43:32
18:53:33
19:03:34


Sample of data from Water Depth X Axis:

00:00:00
01:00:00
02:00:00
03:00:00
04:00:00
05:00:00
06:00:00
07:00:00
08:00:00
09:00:00
10:00:00
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00
18:00:00
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00


Obviously now they both line up, hence why I can compare transmittance Vs Water depth at a given time............All I want is the bottom X axis to have regular 30 min intervals or something like that for example, i.e. the x axis labels to go:

14:03:08
14:33:12
15:03:14
15:33:17
16:03:19
and so on


Was gonna say the same for the water depth axis, but thinking about it I dont even need the top x axis, just so long as the data plots properly which its obviously doing now............but with regular x axis labels.......any ideas folks?

 

[Combat squirrel]

Right click on the axis, choose format axis and select the scale tab. You can then choose the minimum (eg 14:03:08) and maximum values and then specify the major unit (interval - eg 30 mins).

Howver, you may well need to change the format of your numbers as it won't workon xx:xx.

David

Hmm done that, it accepted 00:00:00 format however the numbers dont quite line up to the scale now..... any ideas?