I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))
View 2 RepliesBasically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...
1.5 O.D. - 16GA - 304SS = $X
but
1.5 O.D. - 18 GA - 304SS = $Y
and such.
The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.
O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.
Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.
=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......
Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?
I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.
Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "$A:$C"
strResult = "='" & strDate & "'!" & strColumnRange
Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub
following
i tried formula =IF(F$1
I amtrying to find a way to nest a formula in the Match function.
How can i make the following formula more dynamic so that I do not specify the colum "AY"?
=MATCH(11,Data!$AY:$AY,0)
The information is contained in column AY labeled "New-Rate" in the data sheet
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 ?
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.
View 2 Replies View RelatedI am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.
I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.
Would anyone have any suggestions on how to shorten this down?
=IF(CR="AG",IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))),
IF(CR="G",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))),
IF(CR="MG",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))),
IF(CR="C",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,6,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,6,FALSE)))),
IF(CR="UC",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,7,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,7,FALSE)))))))
I would like to use a varible Cell for the following forumula:
ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"
I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).
I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.
I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.
There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.
I = 3 Rm= W*log(K)
.6
I am entering the following Nested IF(AND statement into a cell and getting a message stating "You've entered too many arguments for this function." Any idea on what formula I can use to get the needed information into this cell based on the fact that there are different inputs that can be entered into the related cell that will trigger the needed respons in the working cell?
=IF(AND(K1051="Down",L1051<2),"Yes","",IF(AND(K1051="Partially Down",L1051<2),"Yes",""))
I 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")))
I am trying to create a nested IF function (has 5 IF statements), however,
every time I put the brackets at the end of the function, it puts a little .
next to the false answer, removes the brackets and comes up with an error
message?
This is only happening on one particular PC, when I create the same formula
on other pC's, it works fine so I know my formula is correct.
is there a setting on the PC that must be wrong?
I have an assignment in which I have to use nested, IF, AND and OR functions. Does anyone know the syntax for this kind of function? Basically I have to determine leave time based on years of service and whether the employee is full time or part time. Part-time with less than 1.5 years of service = 0 days of leave Part-time over 1.5 and full-time under 1year = 3 days of leave and full time over 1 year = 5 days of leave. I know how to do a nested function, but I have not worked with both an AND and OR together.
View 10 Replies View RelatedThis formula seems to work fine for the first two logical tests, but when it come to adding the third, I get an error.
=IF(E5="Hourly",C5*D5,IF(E5="Daily",(C5*D5)*8,IF(E5="Weekly",(C5*D5)*32,''"))
I am receiving an error using the following string as I am not using the OR request properly.
=IF((+$B7&$C7&$D7&$E7)="","",IF(+D7="",+I6,+D7) OR IF(+D7="(blank)","",+D7))
I am struck in using IF /AND in nested function as follows,
A1B1C1D1E1F1G1
1 3 5 7 9 32
=IF(AND(F2>G2,AND(IF(A2>1,A2,AND(IF(C2>5,C2,AND(IF(E2>9,E2),""))
(Above, A1 to >G1 are cell address , in exact rows are actual values)
I need to keep track of my salesmen's time. However, the company will not pay for the first 45 minutes so I need a formula that says, if the time entered in under "worked" is less than 45 minutes, no time will be deducted. If the time entered is equal to or greater than 45 minutes, 45 minutes will be deducted. These are the columns needed and the Daily Hours would be the total of all the previous columns. WORKED TRAVELED LUNCH DAILY HOURS
View 2 Replies View RelatedI am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T
he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is
=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).
VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer
[Code] ....
My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?
VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"
I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.
The problem is this:
The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.
So I have:
A: total quote $
B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A')
C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600)
D: final result
I have multiple sheets within one Excel file. Each sheet is structured the same way. Column B lists Client's last names, which differ from sheet to sheet, though some clients may be repeated in several tabs. I have a column K which displays either the number 1 or 0. This is consistent between sheets.
I have created a formula that will look at the last name in a specific cell in column B of sheet 2 (FY 07) and search sheet 1 (FY 06) column B for the same last name.
=IF(MATCH($B5,'FY 06'!$B$5:$B$24,0),1,0)
If the same last name exists and if column K of sheet 2 and sheet 1 display a "1", I want it to place a 0 in the formula cell.
So, if there is a client that is in both sheets and has a "1" in both columns K of each sheet, I want the formula to let me know.
i have several days trying to convert a If nested function in a automate macro, the problem is that i have a lot of formulas, and the machine becomes slow, so i have to apply this formula to "column G"
=IF(LEFT(F2;1)="J";"C";IF(LEFT(F2;1)="g";"C";(IF(ISERROR(VLOOKUP(F2;rifs;1;FALSE));"NC";"C"))))
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?
as you are limited to 7!!!
excel 2003!
=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))
I had more than 7 nested if statements so I attempted to formulate the named ranged formula. My 2 ranges are as follows: LDD1=IF(MONTH(P1)=MONTH(F2),C5-F5,IF(MONTH(P1)=MONTH(G2),C5-G5,IF(MONTH(P1)=MONTH(H2),C5-H5,IF(MONTH(P1)=MONTH(I2),C5-I5,IF(MONTH(P1)=MONTH(J2),C5-J5,IF(MONTH(P1)-MONTH(K2),C5-K5,IF(MONTH(P1)=MONTH(L2),C5-L5))))))) LDD2=IF(MONTH(P1)=MONTH(L2),C5-L5,IF(MONTH(P1)=MONTH(M2),C5-M5,IF(MONTH(P1)=MONTH(N2),C5-N5,IF(MONTH(P1)=MONTH(O2),C5-O5,IF(MONTH(P1)=MONTH(P2),C5-P5,IF(MONTH(P1)-MONTH(Q2),C5-Q5))))))
Basically what it's doing is comparing the current month with the month in the column heading and calculating appropriately. However, when i input the if statement (=if(LDD1,LDD1,LDD2), i get an error saying that it's an inconsistent formula and the result that it gives is wrong, too. Problem cell is e5
I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.
Issue: All I would like to do is for a NESTED IF Function to be able to say this:
1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square
2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.
3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.
If 'Column A' - 4" Square, Then 'Column B' - A
If 'Column A' - 5" Square, Then 'Column B' - B
& So on...
But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:
User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A
User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B
& So on...
Currently to do the above function, I have utilised the following Excel NESTED IF Function:
[Code] .....
& The following happens:
1. User inputs data into 'Column A'
2. The NESTED IF Function then provides an automatic response.
3. However it relies on the data being inputted without the ' " (Quotation Symbol)'
4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"
Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.
Example - IF Function.xlsx
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
View 9 Replies View RelatedI'm having trouble with my logic again :/
AREA
Tp std
Tp
30
5
So I want the Tp in the 3rd column to show the result:
IF AREA is less than or equal to 20, Tp=Tp std / 2
IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)
IF AREA is 40 or greater Tp = Tp std
But as one equation
I have just been struggling with the range part.
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx