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.
View 4 Replies
I have a situation where I copy a worksheet to further on in the same workbook (essentially, using it as a template to create new sheets). I have certain cells that contain references to named ranges, e.g. on NewSheet1, cell $A$1 contains =StudyNo. Unfortunately, it shows as #NAME?. But, if I go to the formula bar and press enter, it resolves the name properly. I thougt maybe that automatic calculation was on manual but it is not. Why is this happening and what can I do to resolve it.
View 3 Replies View RelatedI am using a simple averageif formula
=AVERAGEIF($L$14:$L$462,"
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 have some custom formula =CFORMULA(...).
I put it in some cell, for example A1.
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.
Actually I have my own formula.
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 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.
View 9 Replies View RelatedRound 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.
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:
Column F
Column F
1.a
1
[Code] .........
I've created a custom Function. I would like for there to be a Helper when a user is entering the Function arguments.
For example if I go into a cell and type in =VLOOKUP(
A popup with appear that shows...VLOOKUP(Lookup_Value,Table_Array,col_index_num,[range_lookup]).
Is it possible for me to build something like this in my Function?
is there a way to turn the following formula into a function ?
=IF( COUNTA(A1:A7)=SUMPRODUCT((A1:A7<>" ")/COUNTIF(A1:A7,A1:A7&"")),"No Duplicates","Duplicates")
I just have no idea where to start ?
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
B
Song
BandName
19
Paul Hardcastle
[Code] ....
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.)
Formula where A1 is the Start Date:
Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1
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 require a custom data validation formula to limit cell value to maximum of 4 decimal places.
0.0001 ok
0.02 ok
0.3 ok
0.12345 fail
0.123456 fail etc
I added a custom add in ages and ages ago and have forgotten how I did it! I'm now trying to remove it but not having much luck.
Its not listed in the Tools>References menu and its not added on to a workbook saved in the C:Documents and SettingsUserApplication DataMicrosoftExcelXLSTART folder.
Are there other ways I could have added it? If so can someone let me know as I might be able to remove it then!
If I send a workbook out with the zoom set at 90...will the zoom be at 90 when others open it.? Also, is there a worksheet event that I could insert to force a specific worksheet to always open with the zoom at 90??
View 2 Replies View RelatedI have a 9 digit number that I want to format as 999.9999.99 Can anyone tall me how? Does anyone know a good link with pointers on custom formats?
View 9 Replies View RelatedWhen using format cells > number >custom I want to custom oC with the degree symbol in superscript. how to get superscript into the custom box? Also where is the superscript icon for the quick access toolbar in Excel?
View 3 Replies View RelatedI am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
I am working on a excel file to help me interpret survey data. I am having some problems transposing data from tab1 to tab2. So basically, I need all the data from Z on tab1 to be transposed on individual lines on tab2, attached.
View 5 Replies View Relatedi had a play with a document that contains a custon menu bar and now the menu bar is always there.
the code that puts up the menu bar is ...
I am trying to add aright click menu item a cell.
I am using the following code in a module.
Sub addmenu()
Set nbar = Application.CommandBars("Cell")
Set ntrnew = nbar.Controls.Add(Type:=msoControlButton)
ntrnew.Caption = "Paste In"
ntrnew.Visible = True
ntrnew.BeginGroup = True
ntrnew.FaceId = 110
ntrnew.OnAction = "Test"
To trigger the first code when needed.
I know this all works when I put it in brand new workbook it works my custom menu appears and I can run the the "Test sub"
However as soon as I put this in the workbook where I want this to work it does not show the menu.
It is there though as when I step through the remove Sub I can see it matched and then deleted.
I thought at first it was protection but that is now removed but still no joy.
I can not see any setting that controls these sub menus.
I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?
View 9 Replies View RelatedI would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
to complete the attached worksheet. My aim is to “bring” into the RANGE column the contents of corresponding cells in that column whose name is chosen in the Select dropdown. I suspect that one of these lookup and reference functions can do this task but am lost as to which one of them:
CHOOSE
COLUMN
COLUMNS
HLOOKUP
INDEX
LOOKUP
MATCH
ROW
ROWS
VLOOKUP
With page breaks in Excel, is it possible to have different pages span a different number of columns?
Right now, I have my page breaks laid out so I've got 3 separate pages. Currently, they are each 30 rows in height, and 30 columns in width.
However, I want the first one to be 20 columns wide, the second to be 30 columns wide and the third to be 40 columns wide.
If I try and drag the vertical blue bar, I adjust the column span for all 3 pages. I need a way to drag the vertical blue bar for each page separately.
