I have the following formula and I want it to just find "January" in the range as they have 1st January, "2nd January and so on. So if it finds a cell with January in it, it includes it. =SUMIF(C10:C700,"January",F10:F700)
View 2 RepliesI am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
View 1 Replies View RelatedI'm graphing data from November 1994 to present. Since it's such a long time period, I only want to indicate a given year once (1994, 1995, 1996, etc.) with a tick mark every quarter (e.g. 1994 | | | 1995 | | | , etc).
I'm having difficulty doing so since the graph starts in November. The graph right now shows 1994 as a full year and thus the data doesn't correspond with the correct dates.
I have about 80,000 dates. What I need to do is to place an x by the date if it falls inbetween January. For example, I have start Date in A1 and End date in B1. If the start date was 10/20/2013 and the end date was 6/11/2014 then that woub give me an "x" because it went through January. If the start date was 11/23/2013 and the end date was 1/1/2014 that would give me an "x" because it fell in January but if I had 8/4/2013 as a start date and 12/29/2013 then that would be blank. Hope that I made myself clear enough.
View 2 Replies View Relatedcolumn "A1" i have a set date in the format of year / month / day (2007/01/01)
i want to extract from that information the "Month" in Text form e.g from cell A1... cell B1 = January.
i tried "=month(a1)" then custom set the cell to "mmmm" but this seems to display a random month which doesnt co-inside to cell A1.
i require this information to feed a pivot table i am working on and wondered if anybody could help.
just a side note... after this i would be looking for a formula to work out (based upon the month) which Quarter it falls under... then the week (1-5) based on the date within cell "A1" to feed the results for the pivot table. though this is some way off going by my current plight.
I'm trying to find the calculation which will automatically have the date for the first Monday in January to show
e.g. Cell A1 input 2010 Cell A2 would show 04/01/2010
Cell A1 input 2011 Cell A2 would show 03/01/2011
etc
How I can subtract a year when I formulate based on if the current month is January.
Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.
I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.
I need a formula for identifying the last cell, of every third cell, that has an numeric entry, of a row. For example, in a range of (F7:V7), I need a formula that examines F7,I7,L7,O7...., and displays the last cell that has a numeric entry, while ignoring the other cells, ie G7, H7, J7, etc.
View 8 Replies View RelatedI only want to include the Month of January in a sumproduct function on another page using a name manager array.
I was thinking of using
=SUMPRODUCT(INDEX(DATA,0,17),--((MONTH(INDEX(DATA,0,5)=1)*INDEX(DATA,0,17))
Where the dates are in the 6th column and include blank cells at the bottom and in the first row it says Date.
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:
View 3 Replies View Relatedi have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies View RelatedI have a module that contains the lines below:
Dim myRng as Range
set myRng = Range("B1:B100").Find(what:= "Symbol")
I have run this module frequently and successfully over the past several months, during which time Column B has been hidden. When I tried to run it today I got a Run-time error 91: Object variable or with block variable not set. I checked to see that "Symbol" was present in the stated range (it was), and noted that when I went to debug the Run-time error, myRng was 'Nothing'.
Now I find that if I unhide the column before the set myRng statement, the code runs without a hitch.
My question is: Is there a known restriction on the Range.Find Method that prevents its use on a hidden range?
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?
2/18/1975, 156.7519, 175.8083, 235.4675, 253.765
4/11/1973, 237,7642, 346.5342, 113.3145, 321.435
5/12/1985, 342.7625, 123.4233, 253.4321, 357.234
etc.
etc.
etc.
I have a table of financial data based on individuals contributing to a non-profit by year over 6 years.
I am looking for assistance in determining the first year of contibution, the last year of contribution and also the gap year if any of giving.
I am trying to find a specific value if the cell falls in between a certain range. Currently I am using a nested if statement to find this value but would like something easier to work on. I am trying to find the MAV based on weight. Currently this a formula that I am using.
[Code] .....
Attached is part of the table it is drawn from.
mav.jpg
Is there an easier way to do this? I want to have a value in a cell of say 100 g and that would return an MAV of 7.2 g.
Please see attached : Excel Problem.xlsx‎
In the Worksheet sheet there are two columns:
Value - value to find in the Report sheet which identifies the table
Assigned Name - name to assign the table
Basically, I need a macro that will:
> Find the value in the Report sheet
> Select the current region except the 1st row
> Name the selection the assigned name
So for example, for Total Received and Shipped (cell A2 in Worksheet):
> Find the value "Total Received and Shipped" in Report (cell B2 in Report)
> Select the current region except the top row (A3:C9)
> Name the selected range as "Total_Received_and_Shipped"
And vice versa for the other values in the Value column.
In vba code, how can I restrict my "find" to a given range? I'm dynamically building the range (anywhere from 5 - 100 cells in column B on my spreadsheet) and need to see if two values are present anywhere in that range. There will be multiple "ranges" to search, all in a single column.
View 2 Replies View RelatedIn a range (a1:a10), how would I find the min and max? Then remove the min and max from the range. Then avg the remaining 8 calls.
View 6 Replies View RelatedWhy is this code not working? It don't return anyting else but 0.
Code:
Dim RowNo1 As Long Dim RowNo2 As Long
On Error Resume Next
With Application.WorksheetFunction
RowNo1 = Selection.Find(What:=.Max(ws3.Range("D3:D31").Value), LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
RowNo2 = Selection.Find(What:=.Max(ws3.Range("D3:D31").Value), LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
[code]....
Looking at the data below, Let's say that I have the number 12900. Can I have it look in column J and ignore all of the other numbers, then when it finds the right line, it copies (in this case) line name "A8346162"
Here is what my data looks like:
WDMS DSS ABCDEFGHIJ141LINEVOLUME FST_ORIG_TAPELST_ORIG_TAPEFIRSTRECLASTRECFIRSTSPLASTSPSEQUENCE_NUM142A8346481B08711B08712555190555194229424025688559412674143A8346321B08711B08712555195555197240324955593568812782144A8346161B08711B08712555198 249625175688566912875145A8346162B08711B08712555199555202252026215688559312897146A8346001B08711B08712555203555205262227175593568812999147A8345841B08711B08712555206555208271828135688559313095148A8345681B08713B0871455520955521228142913559356881149A8345521B08713B087145552135552152914300956885593101150A8345361B08713B087145552165552193010311055935688197151A8345201B08713B087145552205552223111320756885593298152A8345041B08713B087145552235552253208330355935688395153A8344881B08713B087145552265552303304340456885593491 Excel tables to the web >> Excel Jeanie HTML 4
I have a list of dates for a 10-year period in a range that is sorted in descending order and does not include weekends or holidays. The 10-year period is variable. Based on the date in Row 1, I want to return the row numbers for the date 5 years ago, 3 years ago, 1 year ago, 6 months ago, 1 month ago, and 1 week ago.
How can I do this using VBA? I tried calculating the date by subtracting a number of days from the date in row 1. For example, for 3 years ago, I'd use the date in Row 1 and subtract 1095, where 1095 comes from 365 days/year * 3 years. Using this method, the calculated date could be a Saturday, Sunday, or holiday. In that case, I want to return the row number that includes the date just before or just after where the calculated date would be. I tried using the Application.Match function, but ran into problems with it.
I have used the following match formula to find the position of the 1st non zero value in the range A1:A5;
=MATCH(0,A1:A5,1)
This formula works fine as long as there are not any zero values following the 1st non zero value. In the following case this formula doesn't work;
value 10value 20value 3100value 420value 50
The match formula above returns the incorrect answer of 5 instaed of 3.
I am trying to find the maximum value (MaxVal) in a range of cells and want to select this specific cell. I've found the GoToMax code to do this, which functions properly as long as I type in the cell values myself. However, the range from which I want the MaxVal come from a different worksheet by the Excel "Index"-formula. And somehow my code does not accept these values. Copy - Paste special. either.
Dim WorkRange As Range
Dim MaxVal As Double
Set WorkRange = Range("H11:H17")
MaxVal = Application.Max(WorkRange)
Range("H1").Value = MaxVal
WorkRange.Find(What:=MaxVal, _
After:=WorkRange.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select
I have account range between 30000 - 39999.
I should not use any account code from this range.
Need a formula or VBA code to validate a single account code
whether this account code is included or not included on the above range.
for example i am using account code 35000, while using this account code
the formula should display the message like "OK" or "Dont use this account"
I have more than 1000 ranges in each and every rows.
I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this:
Smith, 1, 30
Smith, 2, 36
Smith, 3, 18
Johnson, 1, 125
Johnson, 2, 130
Lopez, 1, 8
Lopez, 2, 12
Lopez, 3, 15
etc…
I'm only interested in the data for the latest Revision. There's over 500 names. A lookup or pivot could be possible?
I have a table that shows across the top row date headings for year and month. So it would show like this, 2008/01 2008/02 2008/03 2008/04 etc to year ending 2009/12
I need a formula which will tell me the first date that a customer started transacting. Under each date heading is a count of transactions for each year/month by each customer. So, I need to search for the very first transaction number and return the date.
Create a formula to find a number in a range. For instance, A1 is qty 1-100, any qty in this range,the cost charges is $10(B1).Whereas A2 is qty 101-200 and any qty in this range, cost charges is $8(B2).
C1 is the cell for me to enter the qty, if I enter 90, I want D1 to return as $10. If I enter 110, then D1 to return as $8.
I have two worksheets to track students registering for one day workshops, which are offered monthly. In my first worksheet called 'CourseCapacity' I track the number of students that can register for each workshop. It looks something like this:
Col A - "Title"
Workshop101
Workshop102
Workshop103
Col B - "Capacity"
5
10
7
My second worksheet tracks the current number of students registered for each workshop for each month. What I want to do is read the course capacity from the first worksheet and display it for the monthly offering of each workshop.
Col A - "Title"
Workshop101 - 1/23/2010
Workshop101 - 2/20/2010......................