Find First Number In Specified Cells Then Average?
Feb 8, 2013
We are trying to track the average star values of reviews posted to our clients accounts. Here is an example of the spreadsheet we have set up:
Company Name
Review 1
Review 2
Review 3
Review 4
Review 5
Company 1
[CTY] 4 Stars review content
[ISP] 5 stars review content
[GGL] 4 stars review content
[CTY] 3 stars review content
[YELP]5stars review content
In this example, we use the initial [ ] to track which site the review was posted on ex: [CTY] means city search, [GGL] means google, etc.
We want to be able to track the average star value on each site. So the formula for City Search would scroll across the row of reviews, pick out reviews 1 and 4 and then find the first number in each and average them. The result in this case would be 3.5
From the research we have done so far, this looks like it will require a complex array formula. How to make this formula? Is this even possible what we are trying to do?
View 5 Replies
ADVERTISEMENT
Mar 6, 2014
I need to find the cells that are greater than 48 and then find the average of this number.
Is there a formula that could put this together?
Average if greater than 48.xlsx
View 1 Replies
View Related
Jun 29, 2006
I am trying to find a formula that works out the average of the figures in column K.....my problem is that so far in column K there is only one positive number (11)- the rest of the cells are DIV/0 so the average is showing as DIV/0. This is incorrect as the average is really 11 - how can I sort this out with a formula?
View 9 Replies
View Related
Jul 31, 2006
I need a formula that will look for a name in column "A" and if it find that name it should average the numbers in column "E" to "I". So if TEST ONE is found in A5 then average E5:I5.
View 3 Replies
View Related
Apr 20, 2013
I am trying to calculate the average of the absolute values of a range of visible cells. I use filters to extract the data rows I need from a large spreadsheet. Then I need to calculate statistics on the resulting data, i.e. the visible cells. The line I am using to calculate this is:
VB:
ThisWorkbook.Sheets("Tracker Channel Stats").Cells(newRow, 4).Value = WorksheetFunction.Average(Abs(R.SpecialCells(xlCellTypeVisible)))
However, what I get back is simply the value of the first visible cell in the range, i.e. the average of the absolute value of one number is that number. how to make this work
View 1 Replies
View Related
Jul 2, 2014
I want to count the number of cells that have a value that is above the average of all of them. Ive tried the COUNTIF formula but no luck.
Sample attached : sample weds.xlsx
View 4 Replies
View Related
Oct 28, 2008
I'm once again here asking for a smart trick to do a difficult (for me) thing. I need to write a formula capable of averaging the n cells on its left. The problem is that the number of cells to be averaged is a variable contained in anothe cell therefore the exact number is not known (a priori) and changes at every sheet refresh.
View 3 Replies
View Related
Jan 24, 2014
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
View 6 Replies
View Related
May 29, 2013
I am trying to do the following.
Monday=0
Tuesday=5
Wednesday=10
Thursday=0
Friday=5
Saturday=0
Sunday=0
Averages
Monday=0
Tuesday=5
Wednesday=7.5
Thursday=0 (I have got a value return of 7.5)
Friday=5
Saturday=0 (I have got a value return of 5)
Sunday=0 (I have got a value return of 5)
I need to work out averages for cells higher than zeros, in other words, I need to ignore those.
Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.
I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").
I would also like to hide the annoying #DIV/0! error.
View 9 Replies
View Related
May 20, 2014
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?
View 7 Replies
View Related
Jun 14, 2013
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.
View 14 Replies
View Related
Nov 30, 2007
I am trying to find number of cells in a range.
I tried:
=COUNTIF(U97:U103,AND(">"&5,"<"&8))
and assumed it will count the 6 and 7's.
It always results with 0.
View 10 Replies
View Related
Jun 11, 2009
I use my spreadsheet to keep score during tournaments. when i calculate my leaderboard at the end of the event i list the results first place all the way down until it finishes the list of players. i need to be able find players with the same or equal score and list them as tied for the same place.
another words if i get to the 3rd place and i had 3 players tie for third i need my leaderboard to show them all in 3rd place . currently it would number then 345. i listed below how it should format it. i also attached a file it will make it more clear.
View 2 Replies
View Related
Mar 3, 2008
I have named cells. Rw1, Clm1 and Blk1
I have the numbers 1 through 9 in cells A1:A9
I need to create formulas to find a certian number.
Example:
A2 would have the value 2
If $A$2(value 2) is in Rw1 or Clm1 or Blk1 then "" if not $A$2
View 9 Replies
View Related
Mar 6, 2008
I have this formula in columns C:D and G:F -
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=IF(ISERROR(MATCH(A1494,Jan07!A:A,0)),"",INDEX(Jan07!A:B,MATCH(A1494,Jan07!A:A,0),2))
Some of the cells end up with nothing in them. Columns E and H are the differenced of C & D and G & F respectfully. The problem is that some of the cells in E and H state #VALUE! because there isn't any data in the other columns (C2 is blank, D2 is 24, E2 is #VALUE!). If I update C2 with 0 then I receive the correct answer in E2 of -24.
I want to have a macro auto fill all blank cells (even though there is the formula in them there isn't data) with a "0". I am using the below code and it is Compile error: Type mismatch at the "True" part of the code.
Sub FindEmptyCellAutoFill()
Dim rFound As Range
With ActiveSheet.Range("C:D", "F:G")
Do
Set rFound = . Find(What:=" ", LookIn:=xlValues)
If rFound Is True Then AutoFillValue "0"
If rFound Is False Then Exit Do
Loop
End With
End Sub
View 3 Replies
View Related
Oct 28, 2011
I'm looking for another excel game changer (for the work I do anyway).
I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?
Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.
View 6 Replies
View Related
May 8, 2009
I'm trying to use the .Find and .FindNext functions to find how many cells in Worksheet("WAS") have the same value as the ActiveCell (B3 in this case) on the Worksheet("DDS"). Basically i'm just trying to figure out how many times this sub goes through the Do While loop. However, "tick" keeps coming back as a value of 1. I know there's something I must be doing wrong or something i'm not allowed to do but i'm still pretty new at this.
View 2 Replies
View Related
May 21, 2014
Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.
The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.
Current formula:
{=IF(COUNTA(C2:D2)<=0,"","✔-"&MAX(LEFT(C2:D2&"0",4)*1))}
The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.
View 1 Replies
View Related
Feb 11, 2013
How to find out the number of cells in a range containing dates less than a specified date.
View 4 Replies
View Related
Jun 10, 2013
I want to find the total number of times the number 1 appears in cells B2 to B33.
In each cell I have codes such as 4919409382a, 5021193035v and so on.
I have tried =COUNTIF(B2:B33,"1") but just get 0 returned.
View 3 Replies
View Related
Dec 9, 2009
(1) I want to find the maximun number for the past n periods in a range of cells, but I want to be able to vary n by changing the number of periods in one cell at the top of the sheet, i.e. if i enter 10 in cell A1 the function will return the max number for 10 periods over a certein range, if I change the figure to 20, then the max number for 20 periods will be returned!
(2) I have a column (lets say column c) which will return a number of values if my criteria are met and 0 if not, I want a function in another column which will replace the 0 with the next non 0 figure in column c, the gaps between the non 0 figures can vary. I feel it involves looping in some form, and writing a new function, but I am not sure.
View 9 Replies
View Related
Nov 21, 2006
i m given 12 max value of my 292 cells. now im asked to find number of cells that falls within each of these max values?? im asked t use histogram. how will i do it?
View 3 Replies
View Related
Jan 30, 2014
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 Replies
View Related
May 25, 2008
I have 3 excel sheets (A,B and C) linked to a master sheet (X). In each sheet (A,B and C), people enter unique idetifier in first column, its type (mr, ab, J) in the second column, start date in the third column and finish date in the fourth column. At the end of every week, i subtract each start date from the finish date and get the number of days spent for each unique idetifier (column N). I preiously posted a thread (Calculate difference between dates excluding weekends) to inquire about how to calculate the difference between finish date and start date and exclude the weekends. I received some nice solutions, but none has worked as the start date cane be weekend as well.
My second and major query is once i have the difference in weekedays for each unique identifier in all the three sheets, I want to calcuate the average of each identifier type (mr, ab and J) in the master sheet. In the master sheet, i have used sumproduct to calculate the average of each idetifier type from column N for each sheet. That means i have a column for A sheet, subdivided into three types, which include the average. I am using sumproduct for this. Once this is done for each sheet type, i need a final colum in the master sheet that calculates average of all mr from A, B and C together, and similarly for ab and J. The problem here is if i combine all the sumproduct formula, i am not gettng the right answer.
My formula looks like SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40)) for all mr in sheet A. I then divide this by SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>"")) to get the average of all mrs in sheet A. I do the same for sheet B and sheet C for all three identifier types. Now i want to combine the formula for all sheets together under mr, Ab and J. I thought the following should work ((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40))+(SUMPRODUCT((SheetB!$E$5:$E$40="mr")*(She etB!$M$5:$M$40))+(SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40)))/((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>""))+((SUMPRODUCT((SheetB!$E$5:$E$40="mr") *(SheetB!$M$5:$M$40<>""))+((SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40<>""))).....
But this is giving me wrong value. It is working fine when done for individual sheets, but when combined for all three sheets together, the value is wrong.
View 9 Replies
View Related
Oct 4, 2008
Column B: The day of the week that the date is, Sunday, Monday, etc.
Column C: Every date in 2008
Column D: my data (the number to be averaged)
What I need the formula to do:
Look in Column B for every instance of "Sunday", and count them up (they count only if the data in column D is not 0) and use that number to average the number in column D
So if I have 52 Sundays and we are only 4 weeks into the year and the 4 data points are 50, 100, 75, 25. The average will be 62.5 instead of 4.8 (divided by 52).
View 6 Replies
View Related
Nov 27, 2008
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
View 7 Replies
View Related
Jul 27, 2009
Attached Dummy file. I would like to be able to get the average time spent on a process for the day recorded on the "Results" tab. I have posted the answers it should be on the Results tab, which was done manually. I would like the file to do it on it's own. On the individual's tab, the time and instances will be recorded for each job. I believe I am close, and the only thing I need is the Results page to be able to return the desired results. Ranges varies day-to-day, and I think (maybe), I can figure out the dynamic range settings.
I would like to avoid creating a helper column for each set, because then I would be dependent on the person expanding on this. I am also avoiding Pivot Tables, I understand it, but not the ones that needs it. This will have about 15 tabs for employees, and could be possible that more or less will happen, depending on workload. This will continue to be manual for now.
View 5 Replies
View Related
Jun 2, 2009
I have a total in F16 that i need to find an average of.
The range is set from f4 - f15 (12 months) and need to find the running average per month. but as we only have done 5 months of the year so far that is the only average required (F16/5).
I need this to count at the end of june (F16/6) basically not counting any zero months (jul, aug, sept etc until the end of each)
View 5 Replies
View Related
Jun 12, 2009
I am just looking for an function which will gives me the average of the hours worked on daily basis. A breif descripiton is i have an workbook in sheet1 i have the sample report which i struggle to prepare on daily basis for each client.
Sheet2 i have the Raw data in column H i have the client name and then in column I is Date and finally in J the hours we worked on in it. To say i just want the average if the each client on the particular date as shown in Report sheet.
View 2 Replies
View Related
Apr 20, 2012
I want to find the average of a range.
I've put the range in order and I want to take off the top 7.5% and the bottom 7.5%. Then find the average of the data that is left. (the middle 85%)
Can I do this with a formula?
View 8 Replies
View Related
