I am trying to denote certain numbers with an asterisk (i.e. -10*) and then averaging a range of cells but excel thinks it's multiplication. Is there a way to ignore this? Range of numbers: 0 to -10. Tried left function but I need it to work no matter how many characters there are.
View 2 RepliesI 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 am trying to create a macro using arrays to calculate average sales from a list of sale amounts that originate from different cities. Each city has its own city code and I want to display the average amount of sales for each city. The attached file is the template that ive created to do this. I am having trouble getting the arrays and loops to work. If anyone can help me out I would greatly appreciate it.CitySales2.xls
View 3 Replies View RelatedLet's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.
So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.
I'm looking for some help. I need a function that can check if each of two seperate values is less than one. If less than one then divide by 2. Then give me the average of these two values. So it could be that it takes 0.5/2=0.25 for one value and 3 for another to give me the average of 1.625.
I need to calculate the average of values in a particular column if some other column contains a particular text. For e.g. I would like to calculate average age of all the people who belong to the Comapny "XYZ". Which means that I have two columns here one is "Age" and the other one is "Company". So For all the people who belong to the company "XYZ", I need to calculate average age.
Company Age
XYZ 24
ABC 26
ABC 34
XYZ 30
My average should be (30+24)/2 = 27.
I have an Excel file ( book) that I use for scheduling my restaurant employees. There are currently formulas in place that retrieve information from cells on one sheet to a cell on another sheet. For example:
If, on the first sheet I schedule Joe the following section on Tuesday AM (D14), his name will be automatically shown on another sheet which list the section/floor plan in quick view.
I need a formula (on a third page) that will allow me to tell me how many times an individual employee has had this particular section. The sections are listed with a letter preceding it. Such as D**, P**, B**, SB**, etc.
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)...
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 &
View 5 Replies View RelatedI'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.
I was going to create a summary page and reference column d.
The following just gives me the min, I want the min average if possible:
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?
=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)
I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
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.
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.
so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View 9 Replies View RelatedI have a rows of the following.
=IF(H1="","",IF(H1="Fail","1",IF(H1="Pass","2","3")))
=IF(H2="","",IF(H2="Fail","1",IF(H2="Pass","2","3")))
=IF(H3="","",IF(H3="Fail","1",IF(H3="Pass","2","3")))
=IF(H4="","",IF(H4="Fail","1",IF(H4="Pass","2","3")))
=IF(H5="","",IF(H5="Fail","1",IF(H5="Pass","2","3")))
The following are the outputs.
1
2
2
2
1
When I do summation on the output cells, the results returned 0. Do I have to do anything procedure in order to add up values that are generated by If-Else?
3 different condition using IF. See the attachment.
View 2 Replies View RelatedI need to find the last date when a company bought any product from my company.
So i have taken the export from the CRM, now i want if the company name collumn is same take the max function in the date collumn fro that perticular company.
I am attaching the file also with a small example.
Is thre an easy way to set up the following testing conditions:
- if A1=A2=A3= ... =A10, do something?
--
Additional information:
- I'm using Office XP
- I'm using Windows XP