Count If Formula And Place In A Graph
Oct 11, 2008
I have a spreadsheet of which i need to count instances on 2 COLS and place in a graph.
Example
D...................................... Col E
JO BLOGGS....................... ERROR
JO BLOGGS....................... ERROR
JO BLOGGS....................... SPOIL
JO BLOGGS....................... SPOIL
JANE DOE........................ ERROR
JANE DOE........................ SPOIL
I need it to count the errors Jo bloggs had so i can populate the chart, the chart would be no problem once i can separate the data as described. I would then have to do the same with Jane Doe.
View 2 Replies
ADVERTISEMENT
Mar 16, 2014
I am Using Excel 2013 on windows 8 OS,
I have no programming background but I have an Idea about it and I try to write some VBA Macros.
I have 27 Excel files which contain each of them 257368 Row Concerning personal information about persons
the File Format is as Follows:
Place of Birth (POB); Name; Father's Name; Family Name; ID Number; Ethnicity,>>
View 2 Replies
View Related
Feb 22, 2007
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies
View Related
Nov 24, 2013
My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.
I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?
View 11 Replies
View Related
Apr 12, 2009
Spreadsheet 1: So in column "A" I have all of the dates written out. Then i need column "B" to put the appropriate name next to the date that person chose in spreadsheet 1.
Spreadsheet 2: I have a live form that feeds data into a different spreadsheet.
I am using google spreadsheets.... The formulas are the same as in excel
View 14 Replies
View Related
Sep 6, 2005
"DRHannay" wrote:
> how do I place the sheet tab name in my spreadsheet by formula?
>
> Excel 2003
Try:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
View 14 Replies
View Related
May 9, 2008
I am testing the code below...
Sub applyCombination()
Dim shtP As Worksheet
Dim j, k As Integer 'j = row; k = col
On Error GoTo errorhandler
Set shtP = Sheets("Sys-Year")........
k = k + 1
Next k
j = j + 2
Next j
Exit Sub
errorhandler:
Exit Sub
End Sub
and, as it is a bit slow to paste a formula in each cell, I was wandering:
Is there any way I can place the resulting value ONLY?
(I don't want to do copy and paste special just values as it takes even more time)
View 9 Replies
View Related
Jul 9, 2008
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible)
,,,,,,,,,,,,,,,
Place formula in Active Cell Row & Column S
=MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m"
I have tried several combinations but with no success.
Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
View 9 Replies
View Related
Feb 10, 2014
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
View 8 Replies
View Related
Feb 10, 2014
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
=SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,""))
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
View 5 Replies
View Related
Jan 19, 2009
Column P is the percentage of body weight lost...
P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%
What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.
View 2 Replies
View Related
Jun 1, 2012
Excel 2007 I have 80 very similar sheets. The only difference is the sheet name (Phase A (1), Phase A (2) etc.). I want the formula located in cell B5 to be different on every sheet.
I have listed the desired formulas on a single seperate worksheet, named "Overview". On this sheet, the following columns have these data in them:
A - Sheetname (example: Phase A (1), Phase A (2) etc.)
B - Cell location (example B5, constant)
C - A formula (example: =Sheet1!G47, =Sheet1!G48, etc.)
I would like to create a macro that will loop through all the sheets, doing the following:
1. Check if the sheetname exists in column A of the Overview sheet
2. If it does, take the formula from column C of the Overview sheet
3. And put it in the cell specified in column B of the Overview sheet
Here is some code, to illustrate the above. I know the code is mostly nonsensical.
Code:
Sub Enter_formulas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
[Code]....
View 2 Replies
View Related
Oct 19, 2006
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View 8 Replies
View Related
Mar 17, 2009
I have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table.
I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells
I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates.
then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets.
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Energy Analysis WE15-03-09.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2A3A4A5A6A7A8=ABCDEFGHIJKLMNO1Reading DateReading Time8912116617118519682296710191119125612571292209/03/200900:0012.5926.74929.69668.728.6487.526.5616.2312.6416.3818.08317.02719.569309/03/200900:3011.8467.211.49610.1245.8726.821.817.9811.3216.711.96214.65619.243409/03/200901:0010.7368.11211.19811.286.27.415.2330.3412.0416.269.5527.26429.02509/03/200901:3010.78767.612810.68510.40725.6966.814.888.936.8416.618.53448.72645.4432609/03/200902:0011.0727.235213.01310.3235.9288.814.757.875.9218.059.38247.09445.3136709/03/200902:3011.2996.819210.26210.1765.70410.414.758.135.0916.489.0566.88325.1984809/03/200903:0011.8116.18248.952411.3695.88.314.697.774.9916.87.20964.71046.2496RAW DATA [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jun 17, 2009
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
View 2 Replies
View Related
Jul 13, 2007
whenever I select a series of dates for a line graph x axis Excel does not recognise the values as dates for the purposes of the scale tab in graph options. I suspect it may be because the dates are the result of formula in the relevant cells.
View 7 Replies
View Related
Oct 24, 2008
I have attached 2 files. One of them is called Macca.xls. This is the file that was created for me by shg on an excelforum. The other isOreDep_updated.xls - this file is the updated list that I want to convert into the Macca file or make it look like the Macca file.
I awas trying to make a couple of plots, but they were not llooking like some examples I have.
In the OreDep-updated file attachment is my data I needed to use. I needed to make 2 plots:
Plot 1: Resource Grade (log scale) against the proportion of deposits (linear scale)
Plot 2:Tonnage (log scale) against proportion of deposits (linear scale)
This was done with my old data that is shown in the Macca.xls attachment.
They are meant to cumulative proportion plots. As a line or scattergram. An example of what they are meant to look like is at: http://www.nbmg.unr.edu/dox/ofr962/c11.pdf on Page 4 and 5.
shg managed to make them look like I wanted with the log normal smooth line too.
My problem now is that I want to change the data under Name, Resource Tonnes and Resource Grade (basically replace the old data in Macca.xls with the new OreDep_update.xls, to come up with same type of plots). But whenever I try to copy and paste the data into the Macca file it messes up everything. I looked at the formula that was created and I really havent a clue how to change it, without affecting everything else like the plots.
I also need to add 2 new columns for date and for reference, and it wont let me do that.
View 9 Replies
View Related
Jan 20, 2008
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
View 9 Replies
View Related
Aug 21, 2006
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies
View Related
Mar 21, 2014
I 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 Related
Mar 26, 2009
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
View 2 Replies
View Related
Feb 5, 2010
I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.
View 4 Replies
View Related
Oct 19, 2009
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
View 9 Replies
View Related
Feb 22, 2007
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies
View Related
May 19, 2009
I have a file where I want to count number of cells where the value is greater than 0. in the attachment, i just want to populate that count below the column indicated therein. So in the example, desired result is two.
View 4 Replies
View Related
Jan 16, 2006
in writing a formula that will count the number of times
the store is listed (Column B) when it matches with closed (Column C).
On the table listed below I will return the data using a match.
From this table
A B C
1/8/2006 9:45Store 1Closed
1/8/2006 9:57Store 2Closed
1/8/2006 10:05Store 3Closed
1/8/2006 10:09Store 4Closed
1/8/2006 10:15Store 5Closed
1/8/2006 10:24Store 1Closed
1/8/2006 10:36Store 2In Progress
1/8/2006 10:41Store 3In Progress
1/8/2006 10:50Store 4Closed
1/8/2006 10:58Store 5Closed
1/8/2006 10:59Store 1Closed
1/8/2006 11:15Store 2Closed
1/8/2006 11:22Store 3In Progress
1/8/2006 11:24Store 4In Progress
1/8/2006 11:33Store 5Closed
1/8/2006 11:51Store 1Closed
1/8/2006 11:56Store 2Closed
1/8/2006 11:57Store 3Closed
1/8/2006 12:03Store 4Closed
1/8/2006 12:16Store 5Not Started
1/8/2006 12:23Store 1Closed
1/8/2006 12:28Store 2Closed
1/8/2006 12:57Store 3Closed
To this table
A B C
1/8/2006 9:45Store 15
1/8/2006 9:57Store 24
1/8/2006 10:05Store 33
1/8/2006 10:09Store 43
View 11 Replies
View Related
Jun 11, 2006
How 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.
View 2 Replies
View Related
Jun 17, 2006
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
View 3 Replies
View Related
Dec 30, 2008
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).
View 2 Replies
View Related
Sep 8, 2006
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
View 4 Replies
View Related
