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?
I have two issues with an excel graph to automate it.
My first problem is that I have to manually select the source data when the data changes inside my spreadsheet. I have 35 slots that represent a 5 week period. Because the first day of the month does not always start on the first day of the week, I have to allow the extra 5 slots in my template. I have the cells set to show blank when there is no data entered in certain cells. Therefore, only days that the machine is up gets put into the 35 cell table. When I do this, the blank cells show up as 0 on the graph and it skews the line. I need some solution that will allow me to tell the graph to ignore zeros.
The second issue is the title in my graph. I have a set title, but need it to pull "Month Year" and "c81" from the worksheet it is on. I cannot seem to get it to populate automatically in the label. I would like it to look like the following:
614 Machine Uptime Percentage
%Month Year%
MTD PCT (%c81%)
where the %name% is the automatically populated item. I don't necessarily have a problem with hiding a cell and linking the label to that cell inside the graph, but I need it to change those two items automatically.
Im certain that a resolution to both of these items exist and I am certain it is an easy thing that I will be kicking myself in the rear over once I find out the resolution, but I need to get this done and im tired of wasting time. I hope this makes sense. If you need me to post an excel file as an example, I will be more than happy to.
I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
Formula:
[Code] ....
I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
What I have is a spreadsheet with a whole bunch of sporting data on it. see attachment for an idea of the layout, Stats.jpg
In these data each round a player is given a rating. What I want to do is calculate is the average rating for each player across the last 3 and 5 games they have played. When a player misses a game they get a rating of 0 for the round. I want 0 ratings to be ignored in the running averages. Additionally, games yet to be played have a 0 in the cell and should be ignored. To get overall averages ignoring 0's was simple I used the averageif function, but I couldn't figure out how to use that but only extract the last 3 non 0 values. I currently have data up to round 12, so if a player has played in 12, 11 and 10 then the 3 round average should just be the average of these 3, but if they missed for example round 11 then the 3 round average should return the average of 9, 10 and 12.
The data is set out in the following way.
In column B I have a list of player names from cells B3:B618.
Across row 1 from N1:AM1 are labels "round 1" "round 2" etc.
The ratings for each player in each round are in N-AM and from 3-618. i.e. N3:AM3 contains the weekly rating for player 1, 4 contains player 2 etc.
I would like the 3 round averages to be returned for each player in column L and 5 round averages in column M.
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
View 1 Replies View RelatedI have to correct a spreadsheet that takes a bunch of values and creates an average.
=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%
I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.
So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.
I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%
It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.
So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try
=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%
It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.
The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.
I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 Replies View RelatedI need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. I'm not getting this to work.
View 6 Replies View RelatedI have a spread sheet where there are multiple rows of price quotes from different suppliers.
Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".
SupplierA
SupplierB
SupplierC
SupplierD
1.3
No Bid
2.3
No Bid
How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.
=G31=MAX($G$31:$J$31)
This does not seem to work, I don't know how to exclude the text values from the MAX function.
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
i have a list which is populated with data from another sheet.. if there is no data in the corresponding cell on the other sheet then the cell is blank.. i've tried using:
=OFFSET($Z$2,0,0,MATCH(REPT("z",255),$Z:$Z))
but it doesn't see the blank cells as blank i.e. no data in them as they contain formula's.. (even although there are no values populated) - can anyone offer any help in relation to this???
Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.
Here is the set up:
Column AColumn BColumn C
(Side)(Qty)(Price)
Buy5,51215.67
Sell119,428null
Buy24,20945.77
Sell20,05412.25
...
I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))
The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.
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 RelatedI have a bar graph..and on top of each bar, i want the x value for that bar.
View 2 Replies View Related 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?
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.
I've two collumns, after some index the values of cells are zero
how i can draw a chart without selecting those zero cells?
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 RelatedI 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 RelatedI 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 RelatedI 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 RelatedI 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.
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....................
Using Excel 2003. I have a data range for a graph. The values in the cells are the results of a simple If function - If(m28>0,n28,0). The results are taken from a larger data input exercise. But, the graph line (a simple graph!) plots the FALSE value (0) when I would like there to really be no value & hence no plotted point if the result is FALSE.
View 4 Replies View RelatedI would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
View 7 Replies View RelatedHow can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500
1/3/2000 $600
3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.
A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
http://img179.imageshack.us/my.php?image=orangeshm4.jpg
Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):
Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
I have a column I want to average however it contains zero's and #DIV/0 entries in it.
Those entries are from linked sheets that have not been populated and are supposed to be there. I want to average the column that contains these values but want the values excluded from the calculation until they are populated with real numbers.