I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
Read & Sign Tracking-061614.xlsx‎
I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)
******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=
ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as
=SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))),
--(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9))))
Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............
.
I've created a simple dynamic chart using a combination of data validation and an index match match formula. Every time the user makes a choice the chart updates with the right info. However is there a way to create a choice where the user can show *all* the choices at once on the chart ?
Dummy.xlsx
I have a very large spreadsheet (almost 9000 rows). I have filters on all columns, however when I click to see all the results for the column under the filter, just to view the contents of the row, some are not there. Does a filter have a limitted amount of rows it can hold.
View 2 Replies View RelatedI'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.
However it is likely that not all of the options will be used.
What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.
Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.
How to filter a table according to a value and show a particular column in the combobox?
For example i have a table like this:
[Code] .....
And i want to filter "A" and insert the column 3 values into the combobox list.
Combobox must show Smone2 and Smone4
Here you can see 4 columns (C, D, E, F)
I would like to compare Column C, D, E, F and If column C = Column E., i would like to show the result from column F to Column d.,
For example:
For column E >> The result in column F is: Port Blair.,
I would like that to be in Column D after a match.
Is there any forumula which will do this.,
I have a wordlist (65000 words in Column B*) in a worksheet “w1” and poems (about 21000 rows) in another worksheet “w2” where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, author’s name, book’s name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. It’s about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value “1” or “0” in which “1” means this entry has already been processed or already has a reference and “0” means it has yet to be done.
If my word B* from “w1” exists in verses B or C of “w2” the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown “(verse B; verse C); (value of Col.A)” and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of “w2”. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button “search for further results“. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me “1” should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got it’s reference the value in A w1 should be changed from “0” to “1”. And by next search all words with A value “1” should be ignored and only “0” words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
I'm making a chart for values of L6:L40 which are all percentages.
The chart is a bar graph..
On C6:C40 i have movie titles. (the amount which vary by week.
ON MY CHART.. i always have at the bottom a bunch of bars that just show 0.00%
How can i get the bar graph to only recognize values either where..
values are greater than zero in the l column rows.. or value is not equal to "" null in the c column?
Is this possible?
I have a line chart displaying monthly profits. Is there a way that I can just display the data up to todays date. As the days got on, I want the chart to grow. I currently have the whole month on the chart and I want to get rid of the straight line showing to the end of the month.
View 9 Replies View RelatedI have data that has values showing 0 and I don't want them showing up on the chart. I have excel 2003 and 2007 that I need it to work for.
This is what I am using for excel 2007 and it works fine.
=IF(COUNTIF('Closed Transaction'!I6:I38,"Friends")=0,NA(),COUNTIF('Closed Transaction'!I6:I38,"Friends"))
I need a forumla for excel 2003. The one above still shows the 0's on the chart.
I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.
However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?
02/07/2012
14/08/2012
43
00/00/0000
#VALUE!
[Code]...........
I made a chart to show performance above and below average for sales teams.
When team is selected with no salesperson, value should be 0.
Employee selected and will show there week by week trends above or below. Seemed like a good idea.But the zeros (team level) are being displayed as plus or minus bars which isn't right and the labels show "0".
I find the pivot table is actually showing strange values if you click in the cell even though it displays a 0.Notations positive or negative like 3.80647894157197E-16.
The underlying data is an average formula and selecting invidual criteria, I show there are no anomalies.
I'm trying to plot a chart of some sort in Excel to display 4 angles that different blood vessels branch off the aorta. Ideally I'd like to input the values of these angles which would vary from patient to patient and have them be displayed in some kind of chart. The chart I'm hoping to achieve would be cirular with the 4 angles represented by spokes which extend out all of the same length. I've attached a rough sketch to show what I'm trying to achieve and I'd be hoping to be able to change each angle in a worksheet and have the chart update to reflect.
View 9 Replies View RelatedI've attached a chart with variance data for each month of the year. I've used an IF formula to "NA" the months with no data, so nothing shows up in the chart. However, I would like to know if it's possible to have the chart only show months with data. The IF formula gets rid of the data point but it doesn't prevent the chart from showing the month on the X axis. In the attached file I manually selected the three months with data.
View 4 Replies View RelatedI have excel 2010....
I am having problems creating a chart off of the following data. Each of the cells which have a numerical value are v-lookuping from another sheet. It needs to remain this way as when I switch brands (through data validation), the numbers will update and the chart should as well. Right now, the chart shows these values as zero. I have done this a million times in excel 2007, but now with 2010, I keep having this problem
2010/Jul
2010/Aug
2010/Sep
[Code].....
Attached please find two charts based on data from 2003 until year-end 2006 (estimated). I want to show that the data is "seasonal" and sales depends on the month it falls on... i have several issues with the chart, is the second chart "too" overlapping and is there too much data to read? 2) how can i get the text boxes to go in the same direction as the chart..i know in powrerpoint you can move the direction of the textbox but how in excel? in sum, i can only choose one chart but still want to make each chart look much better.
View 3 Replies View RelatedI'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
View 2 Replies View RelatedI have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
I would like to be able to enter the data into the grey cells and then have the yellow cells auto generate a result. The only problem is that the required value in cell B5 is from when the entered value in B4 is looked up in the table, rounded UP to the next highest value and then the answer from the next cell.
So, if I enter in B4 a value of 500, a lookup is made in the table and the next highest value would be 553 with the correct answer shown in B5 being 13.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedAlthough I follow the usual procedure for Chart insertion, the resulted chart is missing data.
View 5 Replies View RelatedI have a countif statement based on certain criteria in a table which is reading off of a drop down list on a separate sheet.
Apple (a1) =COUNTIF(Table2[Fruits],+'Drop Downs'!A1) ...and the results are 3
Pear (a2) = =COUNTIF(Table2[Fruits],+'Drop Downs'!A2) ..and the results 0
Banana (a3) = =COUNTIF(Table2[Fruits],+'Drop Downs'!A3) ...and the results are 1
I have also set up a bar graph (this may change to a pie chart at some point) which is feeding from this. My question is, if I do not want to any results with 0 value to show up on my chart (both on the X&Y axis), how do i amend the formula to do this? I read in a couple of places about the NA() function or about just hiding the rows, but if this chart is updated "live"on a weekly or monthly basis, I would like to chart to move with the data simultaneously...so the numbers will change, sometimes they will be 0, others they may not.
Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.
I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.
I am doing an ANOVA analysis of survey results for a set of products, and would like to present a chart to graphically represent the results.
View 1 Replies View RelatedUsing Excel 2007
I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07
And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.
What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.
What I am trying to create is is the following:
1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012
2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column
Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.
Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.
So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)
I have been messign witht is all week and just can't nail it.. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?
DateSkillST HrsOT HrsAreaType
4/1/2012nb - mech b0.00 1.50 Log FM NOMX07
4/1/2012nb - scaffolding0.00 8.50 Bogs NOMX02
4/1/2012nb - coatings mech b0.00 3.00 Log FM NOMX07
[Code].....
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View 9 Replies View RelatedI've dug through the chart options, but didn't find a way to show the major grid lines on the Surface Chart itself.
View 1 Replies View RelatedI can't get the zero's to show up in the part number formula in the wooksheet. Also how do you get the zero's to stay when you type numbers such as (03,04,ect.) the zero always disapears. I need it to stay as a number and not text.
View 6 Replies View Relatedwhy formula's I type in to a particular worksheet show all the time(this is a workbook containing numerous macro's). I have checked under 'Options' that the show formula's box is not checked
View 5 Replies View Related