Graph Plummets To 0 For Zero Values For Future Dates?
Sep 16, 2013
I have a dashboard which has dropdown boxes to pick out the data you want to see. From this data i then have graphs which are graphing 0s from the data making the graph look horrible.
I know I need to add NA() to the cells so that 0s dont graph, but all my data has formulas in and i don't know how to add the NA part to the existing formula.
my formula for each cell is
=IFERROR(VLOOKUP($A9,'DB data'!$A$1:$HH$2003,COLUMN(FP1),FALSE),"-")
This looks up name such as "sign on time", then goes to another sheet and retrieves the data.
Where can i add the NA part. I've tried instead of the "-" at the end but doesn't work. and ive tried instead of FALSE and this stops the formula from working.
View 4 Replies
ADVERTISEMENT
Nov 14, 2007
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
View 9 Replies
View Related
May 5, 2014
I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0
1
2
3
5
7
10
15
20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
View 2 Replies
View Related
Jul 15, 2008
How can I create a formula to calculate a future date, when it is not a “specific day” in the future? I mean, I have today's date (Jul 15/08) and I need to calculate from that, when it will be the first Monday of November in 2009.
View 9 Replies
View Related
Aug 15, 2013
Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.
I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$HQ$1000").AutoFilter Field:=4, Criteria1:="TBD"
ActiveSheet.Range("$A$4:$FQ$1000").AutoFilter Field:=12, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="
View 2 Replies
View Related
Feb 14, 2014
I need creating a formula that combines the following if statements in cell C107:
=IF(AND(B107
View 1 Replies
View Related
Sep 16, 2009
I'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
View 14 Replies
View Related
Sep 16, 2009
Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)
This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..
View 2 Replies
View Related
Aug 15, 2012
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
View 1 Replies
View Related
Sep 11, 2009
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
View 14 Replies
View Related
Dec 7, 2013
I want to numerically graph in a 2D stacked column graph the following:
Training Completed on "Time"
Training Completed but "Not on Time"
Training Not Completed
The training Due date was 10/30/2013.
Book1.xlsx
View 6 Replies
View Related
Dec 27, 2013
I need to set up a graph (smooth line chart possibly) with an (x axis) that are not regular intervals. I've attached an example.
the amounts would be (y axis) and the corresponding dates (x axis).
That would be great to start with. Then I have one of these sheets for each month, can there be a continuous graph made using each month or does it need to be individual graphs.
Davyccexamp1.xls
View 4 Replies
View Related
Feb 9, 2014
I wish to plot a Stacked column graph of dates work is completed. Several peoples names along the bottom and dates up the side. The stack shall be broken into 6 steps, each step denoted by the date it was completed.
I would also like a marker at the top of each stack indicating the deadline date.
I'm working from a large table of dates.
View 1 Replies
View Related
Feb 13, 2012
We have a bunch of 8 digit Purchase Orders (POs) with end dates. We want to show all ten different PO's on a Line graph on the left with the end date at the bottom. Our issue seems to be that:
1. The graph doesn't show the exact PO #, but does a range. We need each PO number listed. How do we get the graph so show each individual PO on the left?
View 2 Replies
View Related
Dec 17, 2008
I have a spreadsheet that I'm trying to make a graph that shows the amount of letters mailed in a certain month.
The first line has 01/24/2008 as date received and 01/28/08 as date mailed. I'm trying to make a graph that shows the amount of letters received and mailed in each month. I know there are probably numerous ways to do this but I'm looking for the most effiecent and I would like to be able to update the same spreadsheet and have the graph update as I add information.
View 9 Replies
View Related
Jun 27, 2014
It appears that the bar graph automatically turns all dates into the first day of the year in question.
For example, what I have in the source chart is the following 3 dates: 6/11/14, 6/11/13, 6/11/12, however when I convert the table into a bar graph, it changes the dates to 1/1/14, 1/1/13, and 1/1/12.
Here's a snip of the table: Data source.JPG
And here's a snip of the chart: Chart.JPG
I've tried going into the advanced settings and making the intervals fixed (starting at 6/11/12 with 1 year intervals), however it does not seem to change the display.
View 2 Replies
View Related
Apr 3, 2014
I want to make a single graph from two data sets that have individual dates.
help!.xlsx
E.g.
Data set 1
1. January 2010 2. january 2010 ... 10. december 2010 etc
10 15 ... 10 etc
Data set 2
1. January 2010 ... 1. January 2015 etc
200 ... 200 etc
View 8 Replies
View Related
Jul 24, 2014
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am using Office 2007.
View 1 Replies
View Related
Dec 9, 2013
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
View 9 Replies
View Related
Jun 3, 2014
I have a pareto for a manufacturing facility that has a list of nonconformists and their occurrences in columns K and L. My issue is since it is a running total for the year at certain times the values are 0 for the number of occurrences. This calculates fine, but the graph is very large and the failures are all located on one side instead of being evenly distributed. I am trying to get it to only plot for what has occurred and not have a bunch of blank values. To make it a little more complex I'm trying to avoid macros.. I can get them to show up blank just can't get the bar graph to resize itself to fill the chart.
View 3 Replies
View Related
Jan 16, 2010
I have a bar graph..and on top of each bar, i want the x value for that bar.
View 2 Replies
View Related
Feb 19, 2010
I have a line graph that charts certain data per day in a month.
During the weekend there is no data. The graph however considers the number as zero, and I want it to just "skip" that day and plot the line directly from the friday date to the monday, instead of the line touching zero on saturday's and sundays.
Is it possible to do this? Or am I forced to adapt my table to only include days of the week?
View 8 Replies
View Related
Jul 9, 2012
The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.
[URL]
We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:
[URL]
The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?
View 4 Replies
View Related
Dec 31, 2013
I am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are:
- Date
- Hourly Rate
Values inputted on a daily basis are:
- Hours Worked
- Volume Picked
Calculated values are:
- Total Cost
- Cost Per Unit
- Mean [=average(CPU)]
- UWL [=mean+(2*stdev(CPU))]
- UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
View 3 Replies
View Related
Oct 22, 2008
I've two collumns, after some index the values of cells are zero
how i can draw a chart without selecting those zero cells?
View 6 Replies
View Related
Oct 20, 2006
one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
View 2 Replies
View Related
Dec 19, 2013
I am using bar chart to display top 5 units sold each year to my Excel file. In the data table, I have listed all the units and its number (quantity) sold. The problem is, the legend of the chart displays all the units instead of just displaying units that has values (top 5 units each year) which makes the legend hard to read (see attachment) How do I get Excel to select legends only if there is a value for it?
View 2 Replies
View Related
Oct 21, 2009
I am trying to create a line graph to show the trends (up or down) of I-Fund vs Gold. The trouble I am having is how to set the axis. The date one is an easy one, but the gold range is more or less 800-1000, and the I-Fund is 13-18. How can I have these both on the same graph to compare? I am attaching an Excel sheet as a reference. Obviously I need to delete the empty IFund cells.
View 2 Replies
View Related
Jul 17, 2014
I was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this).
View 14 Replies
View Related
Oct 19, 2007
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23
21-40 = 1
C4 = 30
21-40 = 2
D4 = 45
41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
View 3 Replies
View Related
