Simplify An IF Function
Jul 31, 2009is there any way of simplifying the 'IF' function in the 'Grade' column of the attached doc?
View 2 Replies
ADVERTISEMENT
Increase Speed And Simplify VBA
Oct 15, 2007I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula ....
Simplify Multiple If Else Statements
Aug 24, 2007I'm using one spreadsheet "Take Off", to pull data from a cell which dictates an objects shape, based on this shape I have a very large IF statement that pulls data from an adjacent cell where an objects dimensions are entered, the statement than proceeds to use these dimensions to calculate the weight of the object.
I've run into the problem with only 7 IF statements, I have gone online and tried to figure out how to avoid this since it is a common problem, but I can't figure out how to do it, most of the examples show very simple and basic formulas. I am unable to figure out how to translate this into my IF statement where I make reference to many cells as well as us differant formulas within the IF statement.
My latest solution was to breakdown the IF statements and try to my my own functions but I think I am too green to have done this properly. Any guidance would be greatly appreciated.
The following is the code I'm working with followed by how I tried to remedy the situation:
=If(Or(C21="BPLT",C21="L"),(((Get_Word(D21,"First")+Get_Word(D21,3))/1000)*(Get_Word(D21,"Last")/1000)*(E21/1000)*7850),If(And(C21="PL",L21<4),(Get_Word(D21,"First")/1000*Get_Word(D21,"Last")/1000*E21/1000*7850), If(Or(C21="BP",C21="PL",C21="BAR"),(Get_Word(D21,"First")/1000)*(Get_Word(D21,3)/1000)*(Get_Word(D21,"Last")/1000)*7850, If(And(C21="HSS",L21>4),(Get_Word(D21,"First
I tried to make my own functions:
aCalc: =If(Or( 'Take-Off'!C13="BPLT",'Take-Off'!C13="L"),(((Get_Word('Take-Off'!D13,"First")+Get_Word('Take-Off'!D13,3))/1000)*(Get_Word('Take-Off'!D13,"Last")/1000)*('Take-Off'!E13/1000)*7850),"")
bCalc:
=If(And( 'Take-Off'!C13="HSS",'Take-Off'!L13>4),(Get_Word('Take-Off'!D13,"First")*2+Get_Word('Take-Off'!D13,3)*2)/1000*Get_Word('Take-Off'!D13,"Last")/1000*'Take-Off'!E13/1000*7850,"")
and so on until hCalc, but when I put them together in a cell like this:
=aCalc()+bCalc()+cCalc()+dCalc()+eCalc()....
I get an error #REF.
I realize a problem might be that I'm referencing a specific cell within my formulas, and many examples online show cells referenced in the form =A$1$, is there a differant way for referencing a cell, because currently I rely on Excel to automatically do this by way of relative reference.
ADDED: Ok I scrapped making the functions because I'm not sure and I did some research on VBA so I tried this one:
Function Weight(Shape As String, Amount As Double, Dimension As String, Optional Length As Variant)
If (Shape = "BPLT" Or Shape = "L") Then
If (Amount > 5) Then
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 + Get_Word(Dimension, 5) / 1000 * Get_Word(Dimension, "Last") * 7850
Else
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 * Get_Word(Dimension, "Last") * 7850
End If
But when I try to put Weight(C6,D6,F6,E6) I run into the error "Sub or Function Not Defined"
How To Narrow Down Or Simplify Phone Numbers
Aug 25, 2013I'm trying to simplified these numbers so each one only consists 8 numbers from the right w/out the blank space or +,-.
Tracking Number
Recip Phone
495905380312
(65)96502898
304612528629
6598941713
495905380746
+6596813956
486044711528
+65 9459 5540
409709299705
6750 2518
409720592221
65-6381-3351
427249160171
6581233832
Simplify Calculations With Multiple Variables
May 29, 2014How to streamline calculations that include multiple variables.
I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:
Associate
Role
Function 1 Count
Function 1 Time
Function 1 Learning Curve
Function 2 Count
Function 2 Time
Function 2 Learning Curve
Function 3 Count
Function 3 Time
Function 3 Learning Curve
[Code] .....
There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:
Rookie/Function1/A = 1-2
Rookie/Function2/C = 4-6
Rookie/Function3/X = 10-15
I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.
I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.
Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.
In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.
How To Simplify Sequential Logarithmic Decay Formula
Sep 29, 2008I am trying to create a s/sheet whereby one can calculate how the concentration of an isotope changes (via an exponential decay) with respect to 2 variables:
1. its half-life (arbitrarily shown as 3 'units' in cell E2), and
2. its initial concentration or dose (arbitrarily shown as 100 units) AND subsequent additional exposures (arbitrarily shown as exposure to 50 units every 3 days) (shown in column E).
Thus for example, with an initial dose of 100 units (cell E6), after 3 days, the substance would have decayed by one half-life to 50 units. However, because there was an ADDITIONAL exposure of 50 concentration units on day 3, the EFFECTIVE dose on day 3 = 100 units.
Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function
Apr 8, 2014I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference
Mar 20, 2009Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedFunction Returns Value To Cell As Single - Function Is Defined To Return Double
May 14, 2013Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
Apr 13, 2007In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedCalling Function That Acts On Matrix Range Within Another Function
Jul 23, 2013I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
Nesting Index / Match Function Within Vlookup Function?
Dec 3, 2013let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
Function To Mirroring Cell Relative To Function's Location
Jul 17, 2008I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
Nested IF Function Error (entered Too Many Arguments For This Function)
Nov 25, 2008I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
Nesting Ceiling Function In IF Function - Conditional Formatting?
May 13, 2013I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
Force Function To Use Row Relative To Cell Housing Function
Mar 2, 2008I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
2007 Right Function With Embeded Find Function
Mar 26, 2009I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
Nov 19, 2009I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.
The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).
Scoping Function As Public - But Not Available As Worksheet Function
Sep 24, 2012I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".
However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"
Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?
Show Insert Function Dialog Pre Set To Function
Sep 14, 2006A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies View RelatedUser Defined Function Function Tooltip
Oct 9, 2007when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
Write The VBA Function Through The Function Wizard
Sep 16, 2009I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.
View 2 Replies View RelatedVlookup Function Together With An Offset Function
Oct 26, 2008I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.
The situation is as follows:
I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.
I have though of using the offset function, but i cannot figure out how to make this work.
Work Around To The 7 Function Limit With The IF Function
Nov 11, 2008I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).
I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.
COUNTA Function With Extra Function?
Dec 23, 2011I am using the COUNTA function which works great but I also have data that I dont want counted and do not know of the problem solver.
E.G
in B1 I have vlookup function thats brings back either "B", "C" or "M".
in c1 I have =IF(A1="C",B1,"")
Now, that tells me if the data is B or not. If it is, it says B If it isnt, its blank
Thats great but when im trying to count how many "B"'s there are, it counts the blanks as well!
COUNTIF Function After SUBSTITUTE Function
Jan 3, 2012I am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.
Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?
If One Function Returns Error Then Use Second Function
Feb 18, 2013I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.
View 5 Replies View RelatedIF Function - Can Combine AND / OR And Creating (at Least) Function
Jun 24, 2013I have the following function:
=IF(AND(I17>=$I$1,O17>=$O$1,Q17>=$Q$1,F17
AVERAGE Function Embedded Within An IF Function
Feb 18, 2010TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.
0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221
Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column
Oct 13, 2009On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.