Charting A Continuous Trend Line With Repeating X Axis Data |
Charting A Continuous Trend Line With Repeating X Axis Data - Excel |
|
Problem I'm having is because the days of the month repeat (1-30, 1-31, 1-29, etc) excel wants to stack the graphs instead of create one long trendline which spans the whole 6 month season.
I want the x value data to show up as the day of the month but the only way I can create a single continuous line is to make the x values not repeat (IE 1-30, 31-60, 61-90, etc). I also want to be able to be able to correlate the series of days with the name of the month, something like:
1-30 1-31 1-31
Nov. Dec. Jan.
I feel like I need separate "dummy" x values; say 1-180 (in order to create the uninterrupted line) but have it reference and display the desired values of 1-30,1-31,1-31.
I don't know if this is correct or perhaps I'm going about this all wrong.
Which is why come seeking assistance
Hope what I described is not confusing...
Similar Topics
Is there a way to have the Min and Max values adjusted dynamically for the Scale of the values being displayed ??
I know that I can use named ranges to display various sections of data -- month by month, or quarter by quarter, for example.
But when the value of these ranges vary greatly from section to section I end of having to manually go and adjust the Min and Max values of the chart scale.
For example, if I was looking at a graph of the S&P 500's prices last November, a Min and Max range of 750 to 1200 would be fine. But if I had a dynamic range established and scrolled over to view the S&P 500's prices for this past February, I would need a Min and Max range of around 650 to 850.
Is there a way to have these Min & Max values adjust automatically depending on the values being displayed ??
Thanks
StanSz
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.
Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.
Thanks,
Dave
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?
--
Knowldege is Power
Basically I want to export data from excel worksheets to various places on a word document I had. I have created a word template as well as bookmarks for that template, as that seems to be the recommendation for performing such a task with excel.
My problem is ... Now what?
I basically have 1 constant worksheet that contains all my affiliates info (Name, Address etc), which i'd like to use to populate some bookmarks in my word template, and then i have another worksheet for the previous month, indicating sales etc for that affiliate. I intend on adding a new worksheet every month that i would use to create new invoices.
Furthermore, i'd really like to be able to update a new file (or perhaps a new page on the same file) for each company with the use of a button, similar to the one's you'd see in an access form.
Can anyone help? Let me know if you need me to clarify.
Thanks!
Matt
I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells.
So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.
I've found this thread that has one for numbers, but I can't figure out how to do it for text values...
http://www.excelforum.com/excel-prog...m-numbers.html
Thanks all!
I'm working with Excel 2003 sp 3 on Windows xp.
I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:
=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)
That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:
weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours
Total = 41 man days and 1 hour
I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".
Any advice is greatly appreciated!
Happy New Year!
~ Brenda ~
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.
I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.
Any help would be greatly appreciated.
Nicole
I have three columns containing last name values from 3 different sources that I've brought into one sheet. I am trying to find a way to compare all three values to establish if they are the same.
Of the three values there is no master, Each value may be different. So essentially it would be like doing =IF(a1=a2=a3,"match", "nomatch")
But of course this isn't possible.
I don't care about obtaining detail about which value matches which. I just need true or false response as to whether all three values are the same or not.
Thanks for any help anyone can provide.
Aaz
Say I had a line graph for 4 years and I wanted it to be a solid line for the first three and a dashed line for the last one, is that possible?
Many thanks for your help
i have a simple spreadsheet where i'm totalling a few values thru a formula. it's similar to this:
=D13*F13
i'm trying to create a funcion that says if d13 * f13 >= 300, i want the value to stay at 300 max and at the same time, if d13 * f13 < 300, i want the total to remain whatever it is.
i have been able to create both of these formulas successfully by themselves but i can't combine them. is this possible?
any help would be greatly appreciated!!
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
Example:
A1: 50
A2: 10
B1: 60
B2: 20
A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60
When I click save, it will change the display value to 80.
I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.
Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.
I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.
Any ideas how to fix this?
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.