How do I make a chart with several times during a day

Hi,

This seems like it may be a simple thing, yet I can not for the life of
me figure out how to do this (or at least semi-easily with VBing it for
a while).

I have a simple table filled with the following information (example
for simplicity):

            12pm       5pm       10pm
11/20      5             4             7
11/21      5             4             7
11/23      5             4             7
11/24      5             4             7
11/25      5             4             7

So basically I am keeping track of a numeric value three times a day. I
would like to make a chart of it with one series with one long line for
the whole month tracking those numeric values. The chart would have
x-axis as date/time and y-axis with values(1-9).

So there needs to be 3 entries for every day on the x-axis tracking all
of these numbers. I have juggled different designs all over the place
and can not get this to work. Any help would be appreciated.

Thanks,
Marc

0
mnations (2)
12/22/2005 6:32:18 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
308 Views

Similar Articles

[PageSpeed] 28

Hi Marc,
I think you might have to reorganise your data table if you want 1 line
on your graph.
The left column of Date/Time values could be achieved by formatting it
as Custom>d/m h:mm AM/PM, then, going down the column...

1/11 12:00 PM
1/11 5:00 PM
1/11 10:00 PM
= A2 + 1    'assuming 1/11 12: PM is in A2

Then select the cell with "= A2 + 1" and fill down to get the rest of
the Date/Time values.

Then by what ever method needed copy the Y values down the column next
to the Date/time values column.

Then insert an X-Y Scatter Chart.

Ken Johnson

0
KenCJohnson (314)
12/22/2005 8:12:18 AM
Thanks Ken,

That worked very well.

However then I hit a snag in phase two of transposing these cells. I
need a way to increment a cell by going from A2 to A3 to A4 etc by just
adding one to the cell before it. Here's the problem. Since I'm putting
all the rows into one column, I need it to go:

A1
B1
C1
A2
B2
C2

I have way too many values to do manually, so I need some type of
equation to do it. Normally a cut/paste would work here. However if you
just cut/paste the first 3 rows, it will skip down three, so you end up
with:

A1
B1
C1
A5
B5
C5

Obviously that wont work. So I need a way to use the cell position as a
variable and just add one to it. For example:

A1 + 1 = A2
B1 + 1 = B2
C1 + 1 = C1

Something simple like that which will repeat all the way down. But
obviously doing it this way it will add the value in the cell, not the
cell position itself. I've tried many different ways to do this, and no
go. Any ideas on how to increment a cell to it's next row position?

Marc

0
mnations (2)
12/23/2005 12:02:27 AM
Hi Marc,
I thought that might be a problem.
I was working on it last night.
I'll get back to you.
Meanwhile, it might help if you tell me the cell addresses of your data
as it stands. In particular, what is the address of the very first 12
pm reading?
How many rows of data?

Ken Johnson

0
KenCJohnson (314)
12/23/2005 2:12:33 AM
Hi Marc,
try this:

1. Copy your data (values only, not headings) Paste it to a free part
of your worksheet.

2. In the empty column immediately to the left of your pasted data fill
down this series 1,2,3,4,5,.... down to the last row of data. Quickest
way is type 1 in the cell that is in the same row as your first pasted
data value, then 2 in the next cell down, then select both of those
cells, then use the fill handle (little cross that appear when the
cursor is over the bottom-right corner of the selection) to drag down
to the bottom of that column. You could also double click when the
little cross appears and it will fill down to the bottom of the sheet.

3. Select this new column and all of your pasted data. Click in the
Name box on the left side of the formula bar, type DATA then press
enter. DATA is a named range that will be used by the following VLOOKUP
formula

4.Type the following formula into an empty cell that is in row 3 and
has only blank cells below it:

=VLOOKUP(INT(ROW()/3),DATA,MOD(ROW(),3) + 2, FALSE)

Then fill that formula down till it stops showing your data in its
transposed state.

5.Copy and Paste Special>Values the transposed data to where you
finally want it.

Let me know how it goes.

Ken Johnson

0
KenCJohnson (314)
12/23/2005 3:08:29 AM
Hi Marc,

Better still try this macro

Option Explicit


Public Sub Table_to_Column()
Dim rngTable As Range
Dim vaTableArray As Variant
Dim vaColumnArray() As Variant
Dim iRowCounter As Long, iColumnCounter1 As Long, iColumnCounter2 As
Long

Set rngTable = Application.InputBox(prompt:="Select the Table of Data",
Type:=8)

If rngTable.Rows.Count * rngTable.Columns.Count >
ActiveSheet.Rows.Count - 1 Then
    MsgBox "Column would not fit on sheet!"
    Exit Sub
End If

vaTableArray = rngTable

For iRowCounter = 1 To UBound(vaTableArray, 1)
    For iColumnCounter1 = 1 To UBound(vaTableArray, 2)
        iColumnCounter2 = iColumnCounter2 + 1
        ReDim Preserve vaColumnArray(iColumnCounter2)
        vaColumnArray(iColumnCounter2) = vaTableArray(iRowCounter,
iColumnCounter1)
    Next iColumnCounter1
Next iRowCounter

'Find first blank column
iColumnCounter1 = 0
Do
    iColumnCounter1 = iColumnCounter1 + 1
    If Application.CountA(Cells(1, iColumnCounter1).EntireColumn) = 0
Then
        With Cells(1, iColumnCounter1)
            .Value = "Transposed Data"
            .EntireColumn.AutoFit
        End With
        For iColumnCounter2 = 1 To UBound(vaColumnArray)
            Let Cells(iColumnCounter2 + 1, iColumnCounter1) =
vaColumnArray(iColumnCounter2)
        Next iColumnCounter2
        Exit Do
    End If
Loop

End Sub

Look in your email for a copy of a workbook called Transpose (If the
email works)

Ken Johnson

0
KenCJohnson (314)
12/23/2005 1:22:38 PM
Hi Marc,
I can't get the email to work with the attachment. Beats me!
Ken Johnson

0
KenCJohnson (314)
12/23/2005 2:13:24 PM
Reply:

Similar Artilces:

Charting
My chart shows temperatures in fresh water. The Y-axis is turned to show the depth of the lake by double- clicking the scale. In this dialog box I can put in the maximum and minimum values and a value to show how the depth should increase. The X-axis should visualize the temperatures being lower according to deeper water. (As a plan B I can refer to a range of cells representing the temperatures. This works.) It is possible to turn on an option so that it is possible to alter values on the X-axis the same way as on the Y-axis. How do I turn on that option? You don't mention ...

Entering Data to make a list?
Is there a way that when I enter something into A1 &B1 it will fill down on a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will copy Apples and $12 , then if I type Pears & $5 it will copy to the next blank cell and so on -- Thanks in advance for your help....Bob Vance .. .. .. .. Bob I'm assuming you have this list on another sheet. This little macro will do the following: When you enter something in B1 of the first sheet, it will copy A1:B1 to the bottom of the list in Columns A & B of the second sheet. It will then clear (erase) the conte...

email delievere dmultiple times
i have an Exchange 2003 FE-BE topology setup. i have one user who is receiving a single message repeatedly at various time intervals. i seem to remeber reading about this condition some time ago but can no longer fnid any reference to the issue in KB articles. anyone have any advice where to look? Lots of reasons for this, but if it's an external message, look in the SMTP protocol logs and see if the sending server is correctly ending the conversation. If the sending server doesn't believe the delivery was successful, it will try again. We often see this with bad firewall conf...

Why does a graphic or print multiple times in the same document?
When I print from publisher to my Canon color copier,graphics appear multiple times in the same document. This could be many things- Temp files need cleaning A simple restart of cpu & printer A bad printer driver Check those first. "Carla" wrote: > When I print from publisher to my Canon color copier,graphics appear multiple > times in the same document. Carla wrote: || When I print from publisher to my Canon color copier,graphics appear || multiple times in the same document. Carla, check out the following kb article. This is actually a known bug that affects the e...

how to send mails at a scheduled time
hi, does anyone know how to send mails at a scheduled time from outllok express or microsoft outlook thanks depending on your version of outlook, there might be two ways, but in Outlook 2000 and above, with the email you're going to send open, go to View, Options...there's a checkbox that reads "Do not deliver before" and you can select the date and time you want the message sent... "CARETTA" <mail@mail.com> wrote in message news:OXJ1OZsqDHA.2268@TK2MSFTNGP12.phx.gbl... > hi, > does anyone know how to send mails at a scheduled time from outllok exp...

How do I make my publication my background on the computer?
I made a collection of pictures in a collage form and wanted to use it as a background on computer, how do I save it in a picture form so the control panel will recognize it? Which version of Publisher are you using? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Punky" <Punky@discussions.microsoft.com> wrote in message news:99C939A3-824B-45E9-8F05-421BDD7DFA83@microsoft.com... >I made a collection of pictures in a collage form and wanted to use it as a > background on computer, how do I save it in a p...

Combining date and time into one cell
I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How can I merge these two in one cell with the format m/dd/yy hh:mm ? Thanks. Date in A1, time in B1, combined in C1: formula is =A1+B1 and format as you describe. On Sat, 22 Jan 2005 14:03:02 -0800, "Kelly C" <KellyC@discussions.microsoft.com> wrote: >I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How >can I merge these two in one cell with the format m/dd/yy hh:mm ? > >Thanks. =TEXT(A1,"m/dd/yy ")&TEXT(B1,"hh:mm") is one way -- HT...

Active Sync timeed out with DEV_10
I have problems with sync my mobile. normally the mobile syncs 100 Objects and then stops. Next time it syncs the next 100 objects and stops again. Does anybody know why just one hundred !? On the Device I got the following error (at the end of the log) =-=- [15/3/2006 9:26:8.0] -=-= =-=-=-= Server Response =-=-=- No Header Found, got error: 80070006 Thanks Marc ...

Calendar entries and time zones
Hi! I have the following problem with Outlook: I moved from Europe to US, and what I did was just to change the computer clock (but not the time zone). Now the time Outlook writes in email headers is always offset by 6 hours. To change that, I wanted to change the time zone to the correct US zone instead of the European zone. But then I have the problem that all the calendar entries are shifted automatically by 6 hours which is definitely NOT what I want. Does anybody know how to solve that problem? How can I change the time zone for my computer without having to change back all the calendar e...

entering current time in shared work book
Hi, i am maintaing a excel tracker in shared drive where i am manually entering the time when a person came into the office. Is it possible that when a person comes he himself open the shared file and click the cell corresponding to his name so that the time of that moment can be stored and that can not be further updated with new value (time)... below is the format Name of Person Date Name A Name B Name C 21-Apr-10 6:44 PM 6:48 PM 6:40 PM 22-Apr-10 6:30 PM 6:34 PM 6:31 PM 23-Apr-10 6:35 PM 6:39 PM 6:40 PM I would try first to setup a list on another she...

Stacked chart problems
I'm using 2007 Using Jon Peltier information found below I'm stuck. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html I have arranged the data as necessary for the stacking (rahter than three I have seven). I need to change the axis labels. However, I'm stuck because the directions state to Paste Special the data as a new series (and I can't paste special in 2007) and also confused how to to move series information to secondary axis. Any help would be appreciated. Thanks in advance. Paste Special works the same in 2007 if you can find it. Click on the down ...

want to grab Day of week in a LIST
I have a frequency field and it is a drop down the choices are any combination of days for example Mon, Wed, Fri or Mon, Tues or Wed or Wed, Fri- you get what I mean- ANY combination. Each cleaning job that needs to be done the frequency gets selected from the dropdown. That table is fine. When I go to Completing the jobs. I would like to run a query that pulls up all jobs for example due today(Wed). When I put this Format(Now(),"ddd") in the criteria for Frequency ONLY the jobs due on WED ONLY show up - would like ALL jobs where Wed is anywhere in the frequency...

Can a front-end server be a bridgehead at the same time?
Is such a configuration even recommended? (Personally, I don't see why not.) Thanks. On Thu, 16 Jun 2005 17:34:23 GMT, "jdee" <jdee@trunk.net> wrote: >Is such a configuration even recommended? > >(Personally, I don't see why not.) > >Thanks. > it's fine, it's supported, it's recommended. Yar. Do it all the time. Just remember you need to leave that first storage group & mailstore up and running. -- Thanks, Brian Desmond Windows Server MVP www.briandesmond.com "jdee" <jdee@trunk.net> wrote in message news:zK...

Pivot Chart Refresh cancels all chart formatting
Hello folks, I am using pivot chart with calculated fields, on which I did some quite sophisticated formatting (combination chart stacked bar / dot plot with secondary axis ; bar & plot area & grid colors ; labels ; axis & chart titles). When I click the "refresh" (!) button to update the data, the chart formatting is lost and the chart reverts to the default formatting (stacked bar chart with all series) Is there any way to "memorize" the chart formatting on a pivot chart ? TIA -- NZ Loss of formatting is a known problem with pivot charts. There's inf...

Linking objects in a chart
I have been trying to link content in an autoshape to a cell in my worksheet. In his book Excel Charts, John Walkenbach says that you simply select the object then click in the Formula bar, enter the equal sign, then the cell to be linked. Once you press Enter the object should be linked and show the content of the cell. My problem is that every time I do that, instead of linking the object, I get a new text box in the middle of the chart (with the linked content) - but I need the content in the object! Can anybody help? Thanks T. [I am using Excel XP on Windows XP] I'm not sure how J...

Last Synchronization Time
Is there a way of finding out, from the server, when a user last successfully synchronized the Outlook Client (went offline)? Maybe in a log or somewhere in a table? MS CRM 3.0 Thanks, Chris ...

calculate time and date
I want to find out how many houer iy is between one date/time and another date/time. date and time one cell A1= 04.03.2005 08:30 date/time two cell A2= 05.03.2005 23:59 =A2-A1 custom format [hh:mm] or for Norway [tt:mm] -- Regards, Peo Sjoblom "make" <lage@tiscali.no> wrote in message news:2c61523.0503070721.2dacdd07@posting.google.com... > I want to find out how many houer iy is between one date/time and > another date/time. date and time one cell A1= 04.03.2005 08:30 > date/time two cell A2= 05.03.2005 23...

Budget Days
Hi, When you select 'Current Week' in the budget view, it doesnt appear to use the 'Week begins on' setting under Program Settings. I have an expense on Saturday and when I look at this view, it hasn't been included, thus not showing correct Actuals. Also, if you have an item in the budget that is weekly, how can you change it so that it is applied as a Budget Item in the Cash Flow view on a Thursday instead of a Saturday? Thank you, Matt ...

How do I create charts having non absolute reference values?
Not sure what you're asking. If you want to change an axis so it doesn't begin at zero, double click on it and enter your own scaling parameters. If that's not it, try using TWO sentences to explain what you need. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "rvboxel" <rvboxel@discussions.microsoft.com> wrote in message news:97BB5A7C-5AD5-4D84-83A1-02E8B96D2FAE@microsoft.com... > ...

How do I resize a data label box in a pie chart to stop text wrapp
How do I resize a data label box in a pie chart to stop text wrapping? Excel decides how large to make these pseudo text boxes, based on the chart size, the amount of text, and the font properties. Aside from changing these, you have no control over the label sizes. You could replace the labels with textboxes, but these don't stay attached to a data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Eton Careers" <Eton Careers@discussions.microsoft.com> wrote in message news:B18C20DE-FD79...

Macro to shift all source data in a chart by one row or column?
Hello all, I have a spreadsheet which contains at least twenty years of data and twenty or thirty charts. Each year we update all of the charts to move the forecast period to the current year and the next five years. Because there is data well beyond the five year period, a dynamic range for the chart data would not work. Can anyone suggest if it is possible to write a macro which I can run on each chart to shift the source data for each series in the chart by one column or row to the right? Any suggestions would be gratefully received! Thanks in advance, Shan try this defined ...

Exch 2000 server freezing several times a week
a little background I have an exchnge server all patched up, running on Win2k. My exch server will pause for a few minutes several times a week. I have 2 DC, both GC, the Exch server was a DC but removed at MS requeust and added a second nic. Running all private IP's, DNS servers are AD integrated, no WINS I ran dciag /v /l , here are my errors DC list test . . . . . . . . . . . : Failed [WARNING] Cannot call DsBind to ics-ml330-04.domain.com (10.0.0.110). [ERROR_INVALID_PASSWORD] List of DCs in Domain 'Domain': ics-ml330-04.domain.com ics-ml330-03.domain...

Can the cities listed under the various time zones be edited
Oslo is not displayed under time zone Sh2008;114869 Wrote: > Oslo is not displayed under time zone You can't edit that list, but why do you care? You certainly know what time zone Oslo's in, so choose that time zone. -- Brian Tillman [MVP-Outlook] http://forums.slipstick.com In Windows time zone list? Yes, it's just a registry setting, but its usually not worth the hassle. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mai...

how do i make pictorial graphs in excel
Hi, See Jon's page on custom formatting. http://peltiertech.com/Excel/ChartsHowTo/CustomSeriesFormatting.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "mupfeki" <mupfeki@discussions.microsoft.com> wrote in message news:A0EA6EF9-A3B1-4790-B6AA-9CFE16ABA3D6@microsoft.com... > ...

how do i make the dot space dot space that are used in bulletins?
I need to know how to make this . . . . . . . . . . . . . for a bulletin. thanks There are several ways to create a dotted line... For example, you can use a tab stop with a dot leader. Alternatively, use a paragraph or cell border. -- Stefan Blom Microsoft Word MVP "mjm" <mjm@discussions.microsoft.com> wrote in message news:4E87DFAD-002F-44BF-8183-3CCD17893754@microsoft.com... > I need to know how to make this . . . . . . . . . . . . . for a bulletin. > thanks ...