I have data regarding the percentage of QC for the team.
I need to find out the individual's average QC scores from the list already available.
The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.
=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),
IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),
IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),
IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),
IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),
IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))
I use VBA with the macro recorder. Now I'd like to programming a code to calculate the average of three cells.
I've done a lot of measurements about 180 but it can be also more. For one test object I've always done three measurements to get then an average.
My values are beginning in cell C2:CXXX, D2:Dxxx and E2:Exxx. For example I'd like to get the average from C2:C4, D2:D4 and E2:E4. The result of C2:C4 I'd like to have in cell H2, the result of D2:D4 in cell I2 and the result of E2:E4 in cell J2. Then the next calculation is C5:C7, D5:D7, E5:E7 and the results in H3, I3 and J3. And so on.
I've attached my excel file, maybe I didn't explained it well. The VBA-Code is for sheet Export.
CalculationE.xlsx
I have three columns of data that pertain to this question:
1. Status (Open or Closed)
2. Assigned (names)
3. Date Opened (a date value)
I want a formula to calculate the AVERAGE NETWORKDAYS between Today() and the Date Opened where Status = Open and Assigned = .
I'm thinking it'll be a big, honking SUMPRODUCT formula but I can't figure it out. Honking is a ...'technical' term.
I just got Excel 2007 and wanted to know what formula to use to calculate the weighted averages in the attached excel file.
View 6 Replies View RelatedIs there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.
The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions.
I`m working on a compression of a picture. For most of you that probably is pea-nuts, but I really just stopped wearing diapers when it comes to excel. At this point I have a sheet in front of me with 300 rows (length of the picture) and 185 columns (breadth of the picture). The cells are pixels and contain values ranging from 0 to 255.
I now need to do an operation I simply can't figure out. I need to select four cells at once (A1:A2 and B1:B2) and replace A1 by the average of these cells and the other three cells should be emptied during that operation. This operation I should do for the whole sheet. In the end I should see a chart with values in every two cells (A1, A3, A5 ... for the first row) as the other cells will be emptied
I hope this question has not been addressed but the closest I can find is in this link:
[url]
I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income.
I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. -Although it would be better to base it all around the financial year if that can be done? (March 1st - April 31st in NZ)
Im trying to work out a function that will calculate the average accross a 3 column array. Obviously there is but I need to make any cell's with 'Free' give a value of 0.
I need some kind of IF("Free",1) type thing, to run before the average calculation, but I dont know how to put it together to make it work, I thought about a Loop, but then how do I make sure it only goes to the next cell in the selection?
In my Case Else, I have it set to take the Average of the values in cells M8 for all sheets other than the ones listed in my other Cases, same for M9, etc. for each worksheet I have.
The change I want to make is as follows: I'd like it say say if the value in Cell M8 is the same on all sheets then place x value in cell M8 on the active sheet, same for M9, on so on and so forth. Only if the values in Cell M8 for each sheet are not the same then take the average.
Private Sub CommandButton1_Click()
Dim sh As Worksheet, sum As Double, n As Long
n = 0
sum = 0
Dim arr() As Variant
Dim nbrOfAvgs As Long, i As Long
nbrOfAvgs = 19
i = 0 ....................
my main goal is come up with an average for a certain range of values...but if a cell equals "continuation" then do not count it's corresponding numerical value towards the average. i've attached a sample of the problem.
View 2 Replies View RelatedI have an array formula that calculates the average of numbers between two dates:
{=AVERAGE(IF('Date Range'!$B:$B>$H$4,IF('Date Range'!$B:$B<=$B$3,'Numbers to Sum'!$C:$C)))*100}
The start date is the day after the date in H4 and the end date is in cell B3. The dates are in column B and the numbers to sum associated with these dates are in column C.
Some of the cells in column C are blank and my formula is taking these blanks into account in calculating the average, while I would prefer not to count them in the calculation.
Can someone please suggest how I could amend the formula above to accommodate this?
I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.
I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):
= SUMPRODUCT(price, quantity)/SUM(quantity)...
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx
I am trying to find expected proportion of code per country by looking at current values. I have a list of countries and associated classifications (0-5) with counts, similar to as follows:
Country
code
count
USA
1
65465
USA
2
54651
USA
3
65411
[code]...
I am interested in creating a pivot table with the average of each code as a proportion of each country. The final table would be expected proportion of codes. The pivot table for this set would look like this:
Row Labels
Average
1
5.4%
2
3.9%
3
4.7%
[code]...
Mean per code of the proportion of code per country
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
one such formula is:
=IF(AND(AC11"No Calc",AC11>=1,AC11
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
I put my margin in G6 of the data sheet and the cost are the numbers on the Mail Rates sheet.
cost/(1-G6) and then round result up to next .25
=VLOOKUP(Calculator!E6,'Mail Rates'!A2:D32,Calculator!D6+1,FALSE)
I am using the vlookup above to bring the mail rates to a calculator. I want to add a margin to the mail rates before they reach the calculator.
The margin amount is in G6 of the "data" sheet and the margin formula is "mail rate"/(1-G6)
I would then like to round the result up to the next .25 dollar
Looking for a formula that can account for a minimum value.
The calculation is as follows 1.22*(x)+8=(y)
(y) must be greater than or equal to 20.
I am trying to calculate the present value of a terminal period in Excel. The manual calculation and excel PV function are off by about $98,000. Both calculations are using the same capitalization rate and terminal life. My PV Excel formula is as follows: =PV(discount rate-growth rate,remaining term (years),-terminal cash flow,,0)*present value factor in last year of cash flow) What is causing the difference in values? Is there something in the Excel formula that is causing the difference?
View 1 Replies View Relatedis a formula to work out how many paid sick days an employee is entitled to based on their employment start date
Up to 6 months service the entitlement is 5 days
After 6 months it is 20
After 1 year 40
After 2 years 60
After 3 years 80
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details....
The formula currently being used is... =IF(G50=E50, H50-F50+(H50
I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View 2 Replies View RelatedI am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.
View 9 Replies View RelatedI have a simple calculation - Say 0-6. What i need to do is, if the value is <7 place a 10 in the cell, or if the the value is <12 place a 5 in the cell, or,
lastly if the value is <21.5 place a 0 in the cell. Is this at all possible - as i can only do it with conditional formatting using colours at present
I have attached a time card that needs a formula to calculate the time for the high lighted areas.
In+out-in+out= total hours.
I'm trying to use the if condition formula however it doesn't give me the calculation?
=if(A2<20%,"B2/.80", A2>20%,"c2*.02)
My division and multiplying functions do not work or it doesn't calculate for me?
how can i create a formula by 3 variable?( power form: y=(x^a)*(v^b)*(g^k)*p that y,x,v,g are known and other parameter are unknown.)
View 2 Replies View Relatedif i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)