I'm looking to find a accurate average based on some data points. The issue is that some service times are 0 and so are considerably larger than the norm. So...
17
18
12
13
14
12
10
5
5
0
0
Assuming the following data 12,13,14,12,10 are probably the most accurate and all other are not.
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
I would like to search the last end points from my 2 columns and also have all the points it was taken until the end points.
It should start from column B row 1 then search the cell data to column C then back and forth until find the last unavailable points in column A.
Attached here is my sample excel data which have the expected output in column D and E.
I am looking to create an invoice details in excel where it correlates to the payment schedule. and the schedule is biweekly starting jan 3rd
I want to also match the start dates to each invoice date, where the invoice date is every 7 days (on a weekly basis)
But, I'm looking to have the invoice date driven in accordance to the employees start date
I'm doing this for a friend, but i'm not sure exactly what he is looking for, and what I have to do in excel
Does he just want me to keep on filling out employee 5 to finish the process?
Or should I create a separate column for each employee for it to be more detailed?
The Overall goal is to get an accurate cashflow
Attached can see what excel sheet looks like : excel.jpg
I have a spreadsheet that tracks productivity for various parts being made basically pcs made per hour against how many pcs should be made per hour shown in a percentage of 100 percent
For example: Part ABC - ran 450 pcs per hour. The rate is 9500 so it ran at 4.74% of the goal
The machine runs about 50 parts.
They had me create a sheet/chart to graph productivity. They told me to just take an average of all the percentage rates the individual parts are running at.
I believe this is where a 'weighted average' should come into play? Taking an average of all those averages is not going to give an accurate overall productivity percentage is it?
How would I go about making a 'weighted' average (if that is indeed necessary) to get an accurate productivity percentage?
I need to create a Report on a Daily Basis and there are certain things which I need to check to ascertain whether the report is accurate..
These checks are done on several sheets which confirm the report is accurate like the Total Count of Employees should be equal to the COunt of Zonal heads and the Cluster Managers in one of the Sheets..
So, without going in each sheet to do the same is it possible to achieve the same using a Macro..
Like some MsgBox which pops up when I click on a Command button "Check"..
Then If I get an Intel in a msgBox then I can go in that sheet to check or take some action or else just exit sub..
Havent used a spreadsheet in ages and i'm having a fun time here. Cant seem to get the right formula for what i need. Here is a brief explanation!
I have a grid (not related to excel) of 999 by 999 squares.
The center is basically co-ords 500|500.
Upper left would of course be co-ords 001|001
Upper right would be 999|001
Bottom left would be 001|999
Bottom right would be 999|999
Now, here is what I am trying to do:
I want to have 3 squares chosen out on a spreadsheet I have made . .
1) here i put in the start co-ords (ie: 500|500)
2) here i put in the destination co-ords (ie: 505|510)
3) This is the fun one!!! ... And where I need the help!
I need a formula to put here that will tell me the number of squares I have to cross to get from one to the other!
I am trying to include numbers in an average if two separate columns prove true. For example, I want to include all numbers in column "C" in the average if the cell in column "A" matches a name and the cell in column "B" is equal to the number 1.
View 2 Replies View RelatedIs it possible to find the minimum distance between two points using two separate lists of different lengths without creating separate columns of equal row sizes
? I am trying to find the shortest distance form one point to another which is a fixed value. I am using the standard distance formula: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2)).
My X1,Y1, Z1, has 2,000 rows in three columns that need to be compared to find the minimum distance from my one row/column of X2,Y2,Z2.
Is there a command that could go through and use each row of the X1, Y1, Z1, while looking for the MIN of the above formula without having to create an equal amount of rows for the X2, Y2, Z2?
I know I could do it by running this out the long way and then finding the min after I do all the math but I am wondering if there is an automated way for Excel to go through and do the forumla and then only return the MIN value?
I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?
AverageExample.xlsx
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
I have a client where I have agreed to a variable price per hour depending on how many hours is used.
So the first 40 hours of a month costs X USD
The next 40 hours of a month costs Y USD
The next 40 hours of a month costs Z USD
The next 40 hours of a month costs Q USD
And all hours above 160 in a month costs T USD
All hours are registered as decimal with 15 minutes as a minimum, so 0.25, 0.5, 0.75, 1, 1.25 etc.
Now the challenge for me is to set up a formula calculating the total cost based on how many hours is registered each month.
I.e. if I register 46 hours in one month the first 40 hours should be multiplied by X and the last 6 hours by Y. And if I register 173 the calculation looks like this:
40 * X
40 * Y
40 * Z
40 * Q
33 * T
And I can't figure out how to make the formula calculate based on what is between the various intervals.
I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.
The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.
Here is the code that I have so far.
VB:
Sub DataSeriesFormat()
'
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
'
Dim x As Integer
Dim val As Variant
Dim Green As Range
[Code] ......
I have a spreadsheet that I am trying to automatically determine a dollar figure based on the number of points a cell has. A if the first set of a goal, B is their actual goal and C is the actual number of points. I am trying to determine the dollar figure for D. For each point between A and B the person gets .50cents. For every point above B and up to C the person gets 1.50. In the case below if the person doesn't reach B than they would only get the 50 cents from A up to C.
A= 2537, B=3252 C=2820
I have attached my spreadsheet and everything is perfect except on one point. I can't figure out how to tell the spreadsheet if it is a negative number to give me zero.
I have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine.
The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc.
I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.
Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution. From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable. i would find the range of the third value, assign a gradient based on the spread, and colorize each point. My VBA syntax is lacking for chart functions
View 7 Replies View RelatedI have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies View RelatedI have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.
View 5 Replies View RelatedI have a data for sedimentation along a pipe. I measured sediment concentrations at different lengths of the pipe, after different intervals of time. So I have 35 concentration values, take at 5 different points along the pipe, at 7 different times. Is it possible to plot a chart with these 35 points, with depth on y-axis, time on x-axis? These 35 points are percent removals (0-100). .
View 4 Replies View RelatedWhat I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.
My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.
If I replace the #N/As with say 1, then it messes up the trend data points.
It seems like I need to be using the trend command, but any solution would be welcome.
I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?
For example:
1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75
I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.
I have a line chart that has 4 points. Is it possible to make the data points point to the next point? If so how can I do that?
I have created a series of charts in excel and the data sources for them are located on a hidden sheet. I need to keep all the charts active as I have set up macros that enable a viewer to automatically change the chart with a click of a button. However the data behind these charts is confidential and I cannot let the viewer have access to the individual data points.
My issue is that when you 'hover' your mouse over the lines on the charts, the various data points can be seen
Is there a way to make these data points hidden when you hover your mouse over the chart but at the same time keep the source data active behind the chart?
I need to map the names of the companies on the x axis (ANF, AEO, ARO, PSUN) and 5% to 30% on the y axis. Plotted for each company should be the min and max. So two points for each company. How can I plot this data. Underlying data as follows:
MINMAX
ANF18.6%19.8%
AEO7.8%21.0%
ARO6.2%9.0%
PSUN6.1%14.2%
I was creating a sales report: See below
Sales for Shook, Emily(131520) Total Sales $160.18
Sales for Clayton, Casandra(131008) Total Sales NO SALES FOR THE WEEK
Sales for Jofery, Rebecca(126310) Total SalesNO SALES FOR THE WEEK
Sales for Brea, Olga(124257) Total Sales$140.21
Sales for Mastro, Rachel(131521) Total SalesNO SALES FOR THE WEEK
Sales for Rodriguez, Blanca(111550) Total Sales $154.33
Sales for Katz, Mary Isabel(126840) Total Sales$269.87
Sales for Kitson, Mackenzie(130466) Total SalesNO SALES FOR THE WEEK
Sales for Blagniceanu, Adriana(126518) Total Sales$161.25
Sales for Best, Jessica(128350) Total SalesNO SALES FOR THE WEEK
Sales for Sanchez, Vanessa(126437) Total SalesNO SALES FOR THE WEEK
The Sales For (Last Name, First Name) was a concatenate I created to give everyone a unique identifier. Than I used a vlookup based on the the sales report to get there total sales
=IFERROR(VLOOKUP(D7,sales2,5,FALSE),"NO SALES FOR THE WEEK")
My problem is say for example: Sales for Sanchez, Vanessa shows up twice on the report stating she has total sales for $40 and $60 how can I get excel to calculate that within my VLOOKUP Function. If there a formula I can use to combine both values. I was think SUMIF is the most likely answer but I'm having problems.
How do i conditionally colour chart data points. This is an example of my data from which the chart is created from.
Salesman A 10
Salesman B 10
US Total 20
Salesman C 20
Salesman D 10
Philipines Total 30
When the chart is created, i would like the country total data points to be in orange while the rest of the data points should remain in white. I tried this code and it returned me an error.
ActiveChart.SeriesCollection("Average of Margin").Points("ID Total").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
I also tried the online F1 help it told me to use Points(Index). However i do not want to refer to the ponint with an index as the number of salesman or country could change. Therefore changing the index points.
Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68
Cant get series 1 to display beyond X value of 20
Attached is an excel file of my issue. I want to stretch the graph so it shows at least 3-4 months. at the moment is only shows 1 month.
Timeline format - Copy.xlsx