I'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:
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 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:
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.
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
I'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 ....
I 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.
Is there a way to write a forumula that read, if it's January, add column A1:A10, if it's February, add column A1:B10, if it's March, add column A1:C10 and it's April, add column A1:D10
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
I have attached a copy of my monthly work schedule. Sorry if it looks too noobish but I am. The question I have is on the schedule right now, it uses to of the same schedule, one is called request off and the other the final. The request off right now is where all changes are made, and they reflect on the final. I did it this way because I could not figure out how to make the cells autofill according to the day of the week, like if it was friday i needed it to fill in the shifts and normal days off automatically.
What I am trying to do now is eliminate the need for the second schedule, the request off one. I think I have figured out how to have the cells autofilled according to the day of the week, and also let the user enter in other stuff such as sick, vacation or whatever, and have it autofill if the cell is left blank, using vba. this is what i have so far, and it is working I was just wondering if it was possible to set it up for a range of cells instead of having to enter each one in manually.
If my input page B13 is either New Lease In-House or New Lease w/Co-Borker andmy input page B43 is - then take cell D57 in my Analysis report, mulitply it by cell B32 from Input page and divide it by 2, otherwise, take cell D57 on the analysis report, mulitply it by B42 on Input page and divide it by 4. I think I have a mess up with my brackets. This is what I have: =IF((OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker"))AND('Input Page'!B43="-",'Analysis Report'!D57*'Input Page'!B32/2,'Analysis Report'!D57*'Input Page'!B32/"4")
I want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?
I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:
I have a userform with 14 independant checkboxes on and would like it when the operator presses the 'go' button for the code to look at each box and if ticked put a "Y" in the column on a spreadsheet associated with that checkbox.
I have this portion of code set for each checkbox within the sub function, but it's a lot of code for something relatively simple:
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
I need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
Here is the first required formula, which will go in cell P7:
If N7>=75 a value of "Y" should be returned. If not, a value of "N" should be returned. BUT if N7 is blank, a blank should be returned.
Here is the second required formula, which is more complicated, and will go in cell Q7:
(1) If Cell P7="Y", then a "Y" needs to be returned in the cell. (2) If Cell O7>=2, then a "Y" needs to be returned in the cell. If not, then a "N" needs to be returned. (3) If Cell P7 is blank, then the cell needs to remain blank.
Here is the final formula, which will go at the bottom of the page.
I need the Q column to return an average of the number of "Y" values, not counting the blanks as numbers. (For instance, if there were 18 "Y"s, 2 "N"s, and one blank, the value returned would be 0.90.).
Im looking to make a formula that states if b3 =10 then a. if b3=12 then b, if b3= 14, then c, if b3=16 then d. A, B, C, D are referring to links to another sheet.
I'm running a macro with multiple statements for hiding columns, and it has been running well for years, now today i'm getting a error message in the macro while debugging that states "Unable to set the Hidden property of the Range class", and when I reset the macro, and try to manually hide the range, I get the error message "Cannot shift objects off sheet".
I want to have cell C8 in Worksheet1 to equal cells A25-A29 in Worksheet3 and also when cell F33 in Worksheet 1equal the Cells A35-A37 in worksheet3 a calculation is done based on the info in cell D18 in worksheet 1 and if it returns a limit under a set value the set value is returned…... Example;
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A35 in worksheet 3, I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2 then divides it by 1000, but if the answer is less then 550 I want it to return 550.
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A36 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.42 then divides it by 1000, but if the answer is less then 600 I want it to return 600. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A37 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.70 then divides it by 1000, but if the answer is less then 650 I want it to return 650. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
I have a data set where a1 is the starting cell and it needs to equal cells b9 or b6 and if the cells in the a column match the data in b9 or b6 it should be assigned an 1 if the cell data in column a does not match the data in cells b9 or b6 it should be assigned a 0. I've tried multiple formulas and have not been able to find the right one
