I have a huge set of data values given per 0,5 hour. Now I want to make a chart which shows how many hours each value has taken place.
Here is what it looks like with the arranged data as y-values. Instead of the x-axis showing the number of values, I would like it to show the above mentioned.
I track inventory/sales for a very small sole proprietorship. I'm looking to track the number of items that I sell based on the text that I enter in a certain column.
Specifically, I want to have a visual chart/graph to show the percentage of each size/color of an item that I sell, as I sell it. When I enter the words "3T pink" I want the chart/graph/whatever to show another quantity of this category. As I sell a new item, I want the chart to reflect it.
The problem I'm having, is that the graphs/charts require two data sets, but I want the graph to calculate it for me! All I want to do is enter the text for specific items, and when the text occurs subsequent times, I want this reflected in the graph/chart.
It seems so much more simple in my head....basically I want a dynamic chart, not based on a static data set.
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
View 9 Replies View Relatedi want to hide rows in accordance with the line numbers that have been determined with Macro, on G3 (Start Rows) and H3 (End Rows).
And i have attached my example file on excel (.xls)
I have a list of "GPC" or company identification numbers. And for every campaign being sent to the company it will have the same GPC of identification code. I want a formula that indicates if it is the first time mailing the company (as a result return nothing for column D ""), the second time mailing them, third, or fourth. So basically I need a formula to find the duplicate values and return whether it is the first, second, third, or fourth time that code appears in column A.
The current formula I have is a little off because it checks against the cell beforehand. I'm not sure how to modify the formula or what different forumla I need. Attached is an example. I am looking at column A and trying to return the data in D. You can also see the faulty formula I currently have in place.
I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.
Right now, I'm using the following:
=SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)
This is a re-submission of a question previously submitted because the title for the first submission was so poorly worded.
I have a column that has numerical values in random order. I want to locate the first occurance of a value in that column.
I have unsuccessfully tried an Index-Match function - apparently unsuccessful because the values must be in ascending or descending order?
I am writing a program which would look for look for the word " TOTAL" in sheet1 and then assign several values in sheet 2 based on cells offset of address of "TOTAL". This is repeated and down rows of sheet 2. Everything works except that it is giving repeating the first "TOTAL" address; i.e. it doesn't seem to go to the next met criterion.
Sheets("Summary").Range("A1").FormulaR1C1 = "=COUNTIF(Sheet1!C,""TOTAL"")" ....
In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this
sat
sun
mon
tue
wed
thus
fri
[Code] ..........
In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I *think* I know the answers to this, but we'll see...
Let's say I have five salesmen:
Mr. Red
Dr. Green
Ms. Blue
Mr. White
Mr. Black
Each month they make sales.
So I have a table, where each salesman is a row and each month is a column, and the cell is populated with sales.
I'd like to do a COMBINED Horizontal Bar chart, where:
- Each row is a month
- The size of each person is actually their PERCENT of Sales, not the total amount (without me having to add rows of calculations)
- Each month automatically sorts the colored boxes in size order.
In other words, here's sample data:
Red - ( 0, 3, 0, 9)
Green - ( 8, 3, 6, 5)
Blue - ( 7, 3, 2, 8)
White - ( 4, 2, 3, 7)
Black - ( 5, 5, 5, 5)
So the top bar would have four colored boxes, in the following order: Green, Blue, Black, White The green box would be 33 percent of the total width, the blue one just a titch smaller, and so on
The next bar would have five boxes, and would be the same length as the one above, and have, in following order: Black, Red, Green, Blue, White The black box would be just smaller than a third (5/16 of the length); red, green, and blue would all be the same size, and the white box would be a titch smaller
The next bar would also have four boxes (time for Red to go!), and would be the same length as the ones above, and would have, in the following order: Green, black, white, blue The boxes would be sized appropriate to their ration in that row (ie, even though Black has sold five each month, each month it makes a different proportion)
and so on.
A doughnut chart does the percentage-of-total thing snazzily, but the sorting gets weird.
I actually havve a ton more data than in my example, so manual calcs and manually manipulating won't work here.
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View RelatedThe order of entries in my chart legend make no sense to me. Why is it not matching the order of the source data?
View 1 Replies View RelatedHow to rearrange slices of pie chart in descending order without sorting the data?
View 1 Replies View RelatedI am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.
Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2
I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.
I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.
At the moment say I put in the following
Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75
[Code]....
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
Refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?
Attached image: Capture.JPG
I've got a series of values in a column as follows:
10 Mb
10 Mb
1000 Mb
1000 Mb
114 Mb
128 Mb
128 Mb
Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
because it starts with a "1". How do I tell Excel to sort by the entire
numerical value instead of the first number?
I'm recording statistics of sport and since I'm dealing with lots of players (and it's easier to add the statistics in when they're sorted by team and name rather than by highest amount of a stat) I need to have a separate section of the spreadsheet to return the 10 best in a stat.
I've found other formulas but I'm not good enough at spreadsheets to be able to manipulate formulas by adding stuff in so it's been hard to get exactly what I want My problem is that I need this to sort by goals (so most goals first) and then by Games played. Here's an example of what data I'm working with:
Ideally I'd like the list to show all these things (so #1 would be Player N), just sorted by goals and then games.
I have a range of data in columns a-g (the length of which i am unsure of). I have been manually sorting them for the values in both column a and column g, but the data in column b-f are needed for my work. would it be possible in one sheet to have column a "y" and on another column a "N" sorted and then subsequently, the numbers in column g copied and pasted along in rows after so you would have a-g copied in h-n and then for the next number o-u and so on. there are multiple of the same value in column g, and a. would it be possible to make a macro which would sort this for me
View 1 Replies View RelatedI have a dump which looks like below sample:
3
3
2
2
The actual dump will have around 200 - 500 rows of data.From the above I need to manually group them which are similar. E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work as sometimes the data with the same range are not in same order. simpler logic/VBA code if any, which will automatically put group numbers if the data in two rows are exact match.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
I will be inputing raw data (20 digit numeric code) from a barcode scanner into column A. I would like to extract the values from certain groups of those digits (first 3 digits and only those digits moved to C column, digits 4,5,6,7 copied to D column, etc.)
View 3 Replies View RelatedI have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).
So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.
example
1
2
3
1
2
6
4
3
6
7
In the Above range i want excel to show me
how many items are duplicate
If the duplicate item is shown i want the result to be shown in next cell
LIKE in the above example i want excel to show 1 is common , 6 is duplicated twice and therefore result should be shown in next cell
I have two sets of 2 columns. One column in one set is identical to a column in the other set, but they are arranged in different orders. I would like arrange those two columns side by (matching their numbers) so that I can find the average of the two non-identical columns.
View 2 Replies View RelatedOn the attached worksheet, our guys are regularly inspected for performance. Column 'H' shows the average of their grading from each inspection (shown in cols L,P,T etc) What I would like to develop is either a macro button that when pressed sorts them all into order according to their average score in Col H, with the highest score at the top, or to have the sheet do this automatically as the data is entered. Obviously all of the data in the engineers row (all of his inspection history) will need to move up and down accordingly, but not get left behind, or mixed up with someone else's records.
View 4 Replies View RelatedI have Excel 2003 with named cells, for example...
cell cell name data
A1 AAA 3
A2 BBB 1
A3 CCC 2
and so on...
When I autofilter (ascending) these cells, the data in these cells sort correctly, but the cell names stay where they're at.
cell cell name data
A1 AAA 1
A2 BBB 2
A3 CCC 3
I'm not an Excel expert so I'd like to know is there any way I could get the cell names to sort with their data.
cell cell name data
A1 BBB 1
A2 CCC 2
A3 AAA 3
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
I know the formula =large but my problem is the formula for the equivalent value for it. Note: In my xcel file attached you can see there are multiple items with the same total numbers and also blanks
View 3 Replies View RelatedI am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).
View 14 Replies View Related