I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset.
View 9 Replies
How do you calculate the average of the last 20 values in a dynamic list? The list is in column A and updates every day by adding a new value in column A.
View 3 Replies View RelatedI would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.
For example
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
Therefore in the current list the average is AVERAGE(A5:A14)=6.5.
The next entry added to the list will be in cell A4 making the list look like this
A4, 1
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5
I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.
as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.
My attached files contains stock returns for companies. Each sheet contains the returns over a 5 year period for a certain stock, with the ticker symbol of the stock used as the sheet name. I want to write a sub that presents the user with a user form. This user form should have an OK and Cancel buttons, and it should have a list box with a list of all stocks. The user should be allowed to choose only one stock in the list. The sub should then display a message box that reports the average monthly return for the selected stock.
View 4 Replies View RelatedI have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...
View 9 Replies View RelatedNeed formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.
View 2 Replies View RelatedI need help with a formula to calculate average number of rebills per client.
I don't know how to get excel to add the number of unique client in a given row. Example
Column A
Client 1
Client 1
Client 1
Client 2
Client 2
Client 2
Client 3
Client 4
Client 4
Client 4
Client 4
Client 5
Formula needs to calculate number of Unique clients. In this case, the answer is 5, but how can I get excel to calculate for me?
Im creating a spreadsheet at work, which has a drop down box with the options of open and closed in it.
I also have a column for length of time of the claim, what i need to do, is now find a way of getting a average length of a claim, but only to see the closed option from the drop down box.
for example,
row 1
open date - 02/06, closed date - 01/07, dropdown box - closed, life - 29
row 2
open date - 10/06, closed date - n/a, dropdown box - open, life - -39400
as you can see from the example above, i cant simply do a average of the whole column, as would read the open ones as well. so is there a way of doing the formula, but telling it to only see the open, or closed ones?
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
how to find the average times of two different sets of groups. There are the baseline times and intervention times. Both groups have three subtopics: Time Fell Asleep, Woke Up, and Duration (how many hours I slept). I am not sure how to find the average of each subtopic. Here are the times....
Baseline times:
Fell AsleepWoke UpDuration
2:30 AM10:00 AM7:30 hrs
4:30 AM12:30 PM8:00 hrs
[Code]....
I have a column of percentages, how do I get an average % rate at the end of my column?
View 7 Replies View RelatedI want to calculate the average for 6 cells across 6 different worksheets.
For instance in sheet 7 i want to calculate what the average of sheet 1 A1, Sheet 2 A1, Sheet 3 A1, Sheet 4 A1, Sheet 5 A1 and Sheet 6 A1 is. However some the cells contain blanks/zeros which are resulting in an error message. The overall figure still needs to be divided by 6 to get the average.
I need to calculate the average based on SKU patterns.
For example, if the SKU begins with "CZD", then take the average SKU that meets that pattern.
SKU
Items Sold
CZD80
45
[Code] ......
I have two columns of data. Column B is age column C is gender. I want to calculate average male age and average female age. Suggestions?
Sample
B C
57 f
53 f
47 m
40 f
42 m
Average female age is ___
Average male age is ___
I need to find the average time it takes students to take exams . I use the following formula =text(end time - start time, "h:mm"). I am able to calculate the amount of time it takes a student to take the exam. Now I need a formula to calculate the average time students take to complete a test. I have over 80 times i need to average. Whenever i try a formula I keep getting 0.
View 9 Replies View RelatedI have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.
I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.
I have an array formula in the attached file to calculate the average of the Top 3 (cell F6). I put that same formula in cell F14 to calculate the average of the Bottom 3 (just change the "large" to "small", however it doesn't work. The result shows zero. How do I fix this?
View 10 Replies View RelatedTrying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula
Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price
I thought this formula (an array) would work for me
=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.
what adjustments I can make to my formula or is there a better formula to use?
I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.
I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.
Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.
View 3 Replies View RelatedI'm using Excel 2003. I have about 190 rows that I use on any given day to enter start times & end times. I calculate the difference in Column E. Is there a formula that will calculate the average time as I enter them in the rows? Some days may have only 100 entries, other days may have as many as 190. I don't want to keep adjusting the average formula for column E.
View 2 Replies View RelatedI'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:
{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}
I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.
I'd like to calculate an average over a variable range. In col.A there are grades from A4:A21. In col.C there are the values for the start row of the range and in col.D the values for the end row of the range.
For instance the value in C4=4 and D4=9. In cell F4 I want the average calculated over A4:A9. Value in C5=10 and D5=15. In cell F5 I want the average calculated over A10:A15.
I am making a summery table of lots of soil contaminant data.
In my summery table, for each determinand I want the average value automatically taken from data from a number of other tables within this same spreadsheet.
For example, for pH- I would average the pH value found in each table, from cell D6, G24 and AB9.
The problem:
Out of the three tables where I take each pH value from, on occasions only one will have a value- the other two will be "0".
How do I ensure that, when averages are taken, it will always ignore '0' values?
I have few data that I need to calculate average:
A B C
9 3 6
5 2 4
3 6 8
I need to calculate the average of column A/B/C --> Average (A2:C2) and Average (A3:C3) and Average (A4:C4). The end result should be pasted to another workbook from A1:A3
I would like to use VBA since I have written some VBA to other parts of the report....
I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.
A
B
C
D
E
F
1
Ticker
Quantity
Price
Ticker
Average Price
[Code] .......
And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
I am trying to calculate a weighted average and have used a sum product to do so. My problem is that the values decrease over time and I have done this in cells d13 and e13 and this seems to work ok. My problem is that I have many months so I don't want to use the formula this way as it will be very long. I tried to use sum and offset to try and effectively sum each row but this doesnt work.
Weighted Average
B
C
D
E
F
G
3
months
4
rate
original value
1
2
3
4
[Code] ...
