I have a set of data being output by a reporting application as XLS data. For each day of business the report is run, I have a set of 100 rows of data. In each of the these rows of data there is a date field and a locationID field.
What I need to do is add a calculated value on each row that specifies the day of the week (MON, TUES, WED, Etc.) and which occurrence of that days of the week it is for the data set. The enumeration of which occurrence of the day of the week (ie. MON-1, MON-1, MON-3) is determined not by how many dates fall on Monday for the data set, but how many Mondays fall between the earliest and latest dates for that location in the data set.
Example:
The data set contains sales data for location XX from 4/6/09 (A Monday) to 4/22/09 (A Wednesday).
The data set contains sales data for location YY from 1/18/10 (A Monday) to 2/9/10 (A Tuesday).
Example:
Location XX's data's earliest date is 4/6/09, it's latest date is 4/22/09
From 4/6/09 to 4/22/09 there occurs:
3 Mondays
3 Tuesdays
3 Wednesday
2 Thursdays
2 Fridays
2 Saturdays
2 Sundays
Location YY's data's earliest date is 1/18/10 it's latest date is 2/9/10
From 1/18/10 to 2/9/10 there occurs:
4 Mondays
4 Tuesdays
3 Wednesday
3 Thursdays
3 Fridays
3 Saturdays
3 Sundays
For all data for location XX, I need to set the new calculated columns to:
Where Date = 4/6/09 & Location = XX, DOW# column = MON-1
Where Date = 4/13/09 & Location = XX, DOW# column = MON-2
Where Date = 4/20/09 & Location = XX, DOW# column = MON-3
Repeat for all dates between min-max date for that location
For all data for location YY, I need to set the new calculated columns to:
Where Date = 1/18/10 & Location = XX, DOW# column = MON-1
Where Date = 1/21/10 & Location = XX, DOW# column = THU-1
Where Date = 1/25/10 & Location = XX, DOW# column = MON-2
Where Date = 1/24/10 & Location = XX, DOW# column = THU-2
Where Date = 2/1/10 & Location = XX, DOW# column = MON-3
Where Date = 2/4/10 & Location = XX, DOW# column = THU-3
Where Date = 2/8/10 & Location = XX, DOW# column = MON-3
Repeat for all dates between min-max date for that location.
A typical data run will have data for 4 or more locations spanning 30-60 days, so figuring out a formula that I can drop onto a column of the exported data so i can run pivot table analysis would be great.
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I know there have been posts regarding this before, but I can't quite get any of them to work. I'm trying to create a function which counts the number of working days between two dates. I've come up with the following code, but it doesn't work.
Function dayscount(Firstdate As Date, Lastdate As Date) Dim x As Long x = 0 For i = Firstdate To Lastdate If WorksheetFunction.Weekday(i, 1) > 1 And WorksheetFunction.Weekday(i, 1) < 7 Then x = x + 1 End If Next i daycount = x End Function
I then use the formula "dayscount(A1,B1)", where A1 is my first date, and B1 is my last date.
I created a formula to stop me from having to do an If statement in the case of an error in one of the variables...I'm pretty sure it work fine in all cases I used yesterday, but today when I use the formula to calculate eCatch (e8+e9,e9) the formula result is #name, even when then e8 and e9 are numbers...the vba is embedded in a module in the excel sheet used, so I'm not sure why its coming up with the error.
When this formula executes I have a value, but if some condition in the formula not true I don't want any value. I want the value in A1 to remain the same.
For example: =IF(C1>5;M1;A1)
If I write like this I have cycling. It is not good. I want just to return the value that already was in A1. I change the =CFORMULA with =IF just to explain the problem.
I have entered a date like May 21 in a cell. I want to enter a formula that will take that date and automatically add 7 days to it (after I create and run a macro of course). I just have no idea what formula to use to continually change the date from the 21st to the 28th to June 4th etc. each time I run the macro on a copied excel worksheet.
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used) (Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
Round to Nearest Half Based On Defined Range of Decimal thread.
So here is what happens in the first half of the equation....I round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in A1 is:
28.0 to 28.399 then rounds DOWN to 28 28.4 to 28.799 then rounds to 28.5 28.8 to 28.99 then rounds UP to 29
For this I am using shg's equation:
[Code] ........
This is all I needed for most of my numbers.....but one part goes one step further.....
NOW THE SECOND HALF......Once the first half of the equation rounds the number above, I need the second half of the equation to then do the following. If the resulting number from the first part of equation above is:
1 through 3 = its own size (1 is 1, 1.5 is 1.5, 2 is 2, 2.5 is 2.5, 3 is 3) 3.5 to 7 = 3 7.5 and above = 4 Resulting number from last part of the formula multiply by 2
So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multipled by 2.
I am pretty sure this second part would be an IF statement (or maybe there is a more efficient way), but I am still learning the ins and outs of that....but I don't know how to use the first formula and add the needed second part into all one formula.
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group. The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B Document No Category 11000001 A 11000002 B 11000003 B 11000002 A 11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).
There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.
Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.
I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.
I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.
So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.
I'm working on creating a custom formula that loops through each row of data and performs a range of calculations when a common number appears in a field (payroll number).
The formula is being applied to 2 sets of data, both are similar however 1 has a few more fields.
When I use the custom formula in the 2nd dataset I keep getting a Circular Reference.
I have attached a stripped down version of the whole spreadsheet, which just relates to the part causing the problems.
On the sheet 'Teaching (Yr1)', the calculation works fine. On the sheet 'Support (Yr1)', the calculation doesn't and Excel prompts with a Circular Reference.
I've tried using the Circular Reference toolbar to trace the predecessors, however it doesn't highlight what they are. All the inputs are blue, and none of them perform any calculations on the cell/column with the formula in it (unless I'm being exceedingly blind!)
Excel gives the following message ...
Originally Posted by Excel
Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you.
I've spent many hours (probably 10+!) debugging the formula trying to ascertain why it's happening and I'm stumped! I've run through all the named ranges, I've changed the formula and still no success.
What I have noticed though, that for some reason the same formula is 'called' multiple times, in fact 3 to be precise.
When I enable Tools -> Options -> Calculation -> Iteration, the formula calculates, however whenever the spreadsheet does an full calculation, it reverts back to #VALUE!
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
I am creating a custom autonumber in excel. The autonumber will be based on the value of another cell's value. So for eg, in cell A1 will look at B1, if B1 has the number 1, A1 will take B1 and add the autonumber to it, eg a, b, c.
If within, b5 the number is 2. So if the number in the corresponding row in b changes, the autonumber must restart.
I've decided to go with a-z for the autonumber as i realized for .1, .2, .3 is limited to 9 values in the list bec. 1.10 may be read as 1.1
I've tried a few options such as creating a named range, however the problem is getting the autonumber to restart.
Below is what i want to achieve via excel formula:
I have a file where I want to count number of cells where the value is greater than 0. in the attachment, i just want to populate that count below the column indicated therein. So in the example, desired result is two.
How do I set up a custom validation formula to prevent duplicate enties?
For example I I've already enter the song name 19 and Paul Hardcastle (BandName), how do I set it up where I can not enter that combination again on a row?
I was wanting to use a custom CountIF function but I could not get it to work.
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
I have a spreadsheet that keeps track of document collection.
Column A is document name Column B is department name Column C-N represent quarters of the year. Ie 1st qtr 2012, 2nd qtr 2012 up to 4th qtr 2014
Conditional formatting changes the row to red if the last day of the qtr is less than today showing those documents as past due.
I mark the Cell "Good" if the documents received meet quality checks.
What I would like to do is:
Create a formula showing the present completion percentage by department.
The trouble I'm having is discounting the future cells that aren't applicable until they become past due.
I thought just counting the red cells and green cells but I can't get any of the conditional formatting counting codes to work for me. Tried pearson's CF vba and similar.
In one cell I can get the CFColorIndex to work and pull back the color index but in another cell trying same syntax trying to get the color index of a different cell I get #Value. CountCFColorIndex I just get #Value no matter what I try.
Can I count blank cells in a range if the Qtr ending date is less than today?
Would I have to have a multiple if formula to capture each qtr?
I have the following problem, let's say I have the following arrangement in Column A (seen below) I want to count the number of cells with "Metal Loss" up until the word "Group" comes up and put the count in let's say Column B Cell 1. Then I want it to continue counting Metal Loss until it reaches another Group and put the next count in Column B Cell 2.
I'm working with an excel file that starts off with two columns and 1300 rows. Employee ID in A and Salary dollars earned in Column B. The total in column B is, let say $60,000,000.00. This is sorted by column B, desending. I've added a third column that tells me what 5% of the $60mil is (c2), C3 = what 10% of 60mil is, C4 = What 15% is, C5 = What 20% of 60mil is...and so on to what 95% of 60mil is in C20.
What I'm trying to determine is how many employees take up or make up 5% of the total salary (60mil)?, then how many employees take up 10% of the 60 mil, and so on to 95% of the 60mil.
See attached file. There are 10 Cells in column G which are Yes/No. On the Calculation sheet you can see the formulas which gives you the % on Score 1 Sheet. I have one problem which I don't even know if this is possible to resolve.
In Cell G12 if it is Yes then you leave G13 blank, if No then you must fill in G13. I was wandering if you look at my current formula on the calculation sheet in cell A4 is there a way where if G12 is yes then you count count 8 cells to gain the % but if G12 is no you count 9 cells to gain the %?
Trying to get a total formula for these rows..... Row T has 0 thur 9 ---on the left side the total of rows B3 to B12 and F3 to F12 and J3toJ12--Butt C--G--and K change twice a day.
Row T is fixed (does not move with the toal of the left... Would a formula work for this????meaning the numbers totaling by themselfs ---like 1 in row C has 3 and 1 in row G has 9 and 1 in row K has 1 = 13 ---which will be putt in row S total.
We change the rows twice a day with new results.. Butt Row T is fixed like I said before....with the total... We have three different systems 3digit,4digit,combo hope I explained it right.
