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 RepliesI am trying to learn more about functions like IF(), AND() MATCH() and LOOKUP()... but so far I cant seem to get it right. I am actually trying to get a simple working formula that can take a value from the first tab and cross reference it with a table in the second tab to return another value in the first tab.
For example....
On the first tab/sheet named "Details", i have a list of people, an area of work they fall in and then a column named region that categorizes their areas of work into groups
A
B
C
1
Name
Area of Work
Region
2
Mr A
J
?
3
Mr B
6
?
4
Mr D
Z
?
5
Mr E
18
?
Then on the next tab/sheet named "Category", I have a table which lists the areas that belong to a region;
A
B
1
Region
Area
2
1
A-J. 1-6
3
2
K-P, 7-15
4
3
Q-U,W,Z,16-20
I have tried formulas like IF(AND(B2=Category.B2),"1","ERROR") but it keeps returning me "ERROR" when it should return "1"
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 RelatedI am trying to write a formula, I have 6 sets of criteria with a lower and higher range, if the number falls within the criteria I would like it to return the Alpha number,
eg, 104, will return D
MinMaxReturn030A3160B6190C91150D151240E241360F
I am trying to make a function which will take a date as input and display the season for that date. i.e. the input 1-Jan-1992 will return "Summer" (southern hemisphere seasons)
View 10 Replies View RelatedI am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
I have week numbers from 1 to 52, now i want to get which week number will falls in which month, is there any formula in excel
for eg. Week 01 - 05 will fall in January month (2014), likewise..
I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View 3 Replies View RelatedOf the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?
View 2 Replies View RelatedI have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
I've written this code to find a value in the worksheet chosen from the combo box (I think it works as its doesnt error), but I now need too write some code to pull out the row number from my code and put data into the same row (but different columns). This is my code so far:
Code:
Private Sub BookButton_Click()
Dim rFound As Range[code].....
I have a database with over 100 products listed across the first row.
Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
For Example
A B C D E etc.
Products --> X Y Z AA
Proj 1 2 3
Proj 2 1 4 5
Proj 3 2 4
etc.
I want to be able to create a report for any given product.
The report could look like,
Product Z
Proj 1 3
Proj 3 2
So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.
I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.
I need a function to find the number of decimal places of a certain number (in this specific case doubles)
The first solution would be something like this:
Code:
'returns the number of decimal places within a double
Public Function getDecPlaces(inputNum As Double) As Long
Dim ndx As Long
ndx = InStr(1, inputNum, ".")
If ndx > 0 Then
getDecPlaces = Len$(CStr(inputNum)) - ndx
End If
End Function
But i feel there is likely a much better way of doing this..
I've been using a Index and Match Functions to locate values,
What I'm trying to do is as follows:
Column A contains x values and Column B contains another value
A length is entered at the top of the worksheet, when the x value is greater than half the length the values will be mirrored about the centre point.
To do this i'm doing x-(length/2) to give the corresponding value of x on the other side of the length
I can do the If functions etc to sort all this out, but the match function isnt working, see attached, it isn't finding values which are in the list, when i use 0 for exact match, i've tried using 1 but it still isn't finding the correct values.
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345
Hello,
What I am trying to do is
Search the number written at ColumnB in ColumnA and if found write YES to column C
if not write No
I'm running a macro that pulls rows from a different spreadsheet based on certain criteria. I need to add an if statement that will determine if the dates in a column fall in between 2 dates specified in cells.
View 3 Replies View RelatedI am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"
This function searches in whole sheet,but i need only in C column!
Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
I am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.
Ive attached a worksheet to explain it better.
I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.
Attached File : Test.xlsx
I'm surprised and confused as to why =Match("",A1:A300,0) doesn't find blank cells in the "who does what" worksheet of the attached file.
View 2 Replies View RelatedI wrote a custom find function to search for a list of numbers across a bunch of worksheets (6 in total). A cell reference is sent to the function and it basically returns the worksheet name and address for the first occurence, or nothing if not found.
Now I realise that the number of cells that need to be checked is fairly severe across 6 worksheets (finding 65 numbers * 65536 * 256), but this still takes about 1 minute to execute... is that about right? Code was hacked together quickly and is nothing special...
Public Function My_Find(Optional my_range As Range) As String
Dim ws As Worksheet
Dim my_cell As Variant
Dim result As Variant
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
I am using Office/Excel 2007 and Windows 8
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
A
B
C
D
E
F
G
[Code] ..........
results for each formula used
95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)
95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))
3 formula: =COUNTIF(B1:G2,B1)
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
I've created a userform that has one ComboBox (ComboBox1) and two text fields. I am trying to get the userform to return information to my worksheet in the same row as the name that is displayed in the ComboBox. This is my VBA code.
Private Sub Cmdpayment_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Sheet4
iRow = Cells. Find(What:=Me.ComboBox1.Value, After:=C5, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ws.Cells(iRow, 12).Value = Me.txtpdate.Value
ws.Cells(iRow, 13).Value = Me.txtpayment.Value
Me.txtpdate.Value = ""
Me.txtpayment.Value = ""
End Sub
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
Refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?
Attached image: Capture.JPG
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.