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

Dutch Guy said:
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.
Click to expand...

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 :(
 
Ok made some progress, produced this:

progress.jpg


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
 
Last edited:


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.
 
the way i used to do that in work was open it in notepad, then do a find and replace

replace ", " with " " (a tab that i've pasted in from notepad)

then copy + paste into excel
 
Dutch Guy said:


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.
Click to expand...

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 :p still very stuck now, many weeks later :( :eek:
 
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 % :confused: , 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 ? :confused: :)
 
A[L]C said:
Works fine for me, you sure the cells arent formatted as percentage?
Click to expand...

Says there formatted as general :(

EDIT: Just copied data to txt file and tried on another pc, same thing happens :mad: , 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?
 
Last edited:
DAS said:
Why not create a formula that divides the 5837% by 100?
Click to expand...

GENIUS /laughs at own dumbness :D

RIGHT THEN, LAST PROBLEM TO SOLVE THEN ITS DONE!

Iv now got this:

nearly.gif


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?
 
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
 
DAS said:
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
Click to expand...


Hmm done that, it accepted 00:00:00 format however the numbers dont quite line up to the scale now..... :confused: any ideas?
 
Combat squirrel said:
Quote:
Originally Posted by sWiZzLe
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
Click to expand...
Hello again, I tried that transpose thing and although it could be very handy... what i really need is to convert a column of numbers quickly into this format: 1, 2, 3, 4, 5, 6 so that i can paste it into Word, i have to do it manually and it takes ages.... well not that long but its easy to make mistakes - Is this possible?
 
Combat squirrel said:
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 :)
Click to expand...

Ooops completely forgot about this. Just tried it - Transposed it, saved as csv, opened in Word and wooooooohoooooo 1, 2, 3, 4, 5, 6. You have no idea how helpful this is going to be, thank you sooooooo much ;)
 
You must log in or register to reply here.
Back
Top Bottom