Nested IF - Is VBA The Answer: Combine The Formulas Into One?
Jun 12, 2006
I need to combine the following formulas into one but obviously it won't work due to the limitations. Would the following be better using VBA? If yes. At the moment, they are in 3 separate columns and working but indepenantly. It basically says, if the agreement is older than todays date, write expired, if not if its for 12 months, then calculate the percantage according to number of days. and so on for 24 months and 36 months...................
View 2 Replies
ADVERTISEMENT
Oct 27, 2009
i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 Replies
View Related
Dec 21, 2006
=IF(E23="A",4,IF(E23="A-",3.67,IF(E23="B+",3.33,IF(E23="B",3,IF(E23="B-",2.67,IF(E23="C+",2.33,IF(E23="C",2,IF(E23="C-",1.67)
Obviously im writing a script that correlates letter grades with GPA values. The problem is, after i try to put in the next grade (a D-) it throws up an error saying "the formula you typed contains an error". Is there some kind of limit to how many IF THEN statements you can put in a script? if so is there another easier way to what im trying to do?
View 4 Replies
View Related
Apr 13, 2007
I'm working on a formula that will determine the lowest number in a range.
The complication is that the range varies based on how many rows match a certain criteria.
The formula I have created works correctly, however, I need to have the range go up to 10, and because of the limit of 7 nested IF's, I have to stop at 6.
=IF(A2=A1,"",IF(COUNTIF($A:$A,A2)=1,G2,IF(COUNTIF($A:$A,A2)=2,MIN(G2:G3),IF(COUNTIF($A:$A,A2)=3,MIN(G2:G4),IF(COUNTIF($A:$A,A2)=4,MI N(G2:G5),IF(COUNTIF($A:$A,A2)=5,MIN(G2:G6),IF(COUNTIF($A:$A,A2)=6,MIN(G2:G7),"")))))))
After a result is returned from this formula, I have to do the almost exact same formula, based on the min value from these results.
View 9 Replies
View Related
May 28, 2007
I undrstand that you can only ave a certain amount of nested IF statements within one formular, is there a way round this?
What i'm looking to do is this.
VLOOKUP(VLOOKUP(location,IF(postcode="BA",LocationBA,IF(postcode=" BS",LocationBS,)),2,FALSE)
I have 13 options that can be chosen, but only have room for 6 nests of which BA and BS are currently above. what would be the best way to have all 13 within the lookup table array?
View 9 Replies
View Related
Dec 8, 2009
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.
View 3 Replies
View Related
May 23, 2008
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))
View 5 Replies
View Related
Jul 27, 2006
On a spreadsheet, I want my formula to caculate the following: if "certain cell" = "certain name", then do this calculation, if "certain cell" = "certain name", then do this calculation....and so on.
I have a drop down list of employees, which would be "certain name" and it is in a cell, which would be "certain cell". The calculation I want to do is if it is a certain employee, I want to take the manually inputted hours in another cell and multiple it by the employee's rate of pay, which would be it's own calculation including labour burden. I have 13 employees that I want to have as part of this. So, if the first valuation comes up false, it carries on to the next and so on until it finds one that matches. I have tried IF commands, but it is too long, so I tried to split it, didn't work. Tried lookup and it didn't work. Tried named formulas, didn't work. It is entirely possible that I am totally on the wrong track or in over my head, but I know this should work.
View 6 Replies
View Related
Mar 29, 2014
Ok, These are the three formula's. How do I combine them or can I.
=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)
=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)
=IF((B2="COM"),[@[Gal Billed]]/1000*3)
View 4 Replies
View Related
Nov 9, 2007
I have two formulas that i need to combine as one, but i dont now how.
This is the other formula:
=SUMPRODUCT((Huollot!I2:I500="Ty”n alla")*(Huollot!K2:K500="Warranty")*1)
The other one:
=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
View 14 Replies
View Related
May 9, 2014
I would like to combine the 2 formulas below but I don't know how to do this.
FORMULA 1
=J015_100!S6
FORMULA 2
=IF(ISNUMBER(SEARCH("Y",J015_100!U6)),"APPROVED","")
I would like to display the value of formula 1, unless the value of formula 2 is "Y" in which case the value would be "APPROVED".
View 2 Replies
View Related
Feb 20, 2008
Working on the same simple chart NBVC helped me on, but this is for AVERAGE instead of SUM and with an added variable.
View 14 Replies
View Related
Feb 3, 2014
I would like to have cell A3 contain two different formulas. I may be doing this wrong, so here is what I would like to do:
Cell A1 will contain a number that will change from month to month, but will need to show as $0 until then. Cell A2 will contain a fixed sum number from month to month Cell A3 will contain the difference between cell A1 and A2
I wouldl like cell A3 show as $0 until a number is inputted into cell A1
A1
$0
A2
$2000
A3
$0
The formula I have in cell A3 is =SUM(A2-A1), but cell A3 shows $2000. I remember doing this before, cut can't remember the exact formula. Something like =if(A1
View 4 Replies
View Related
Nov 14, 2006
i have 2 worksheet function IF statements that of course look for certain conditions, but in some instances i need to combine the IF statements in one cell, the 2 i need to combine are below:
=If(D3="S","Sick",If(D3="SW","Swapped",Sheet2!B3))
=If(Or(C1="Line On",G1="Line On"),Sheet2!B3,"")
so what i need is for the cell to show either Sick, Swapped or the contents of Sheet2!B3 however if both C1 and G1 show Line Off then cell must be blank, which is what i achieve with the second if statement.
View 3 Replies
View Related
Aug 12, 2014
I have an excel sheet that have a large set of datas. In these datas in one column most of the datas are indicated as green but some of them are red. I can pick the red ones by using filter option but I also wanted to show the red cell values in one cell. For example, Cell1;Cell2;Cell3 like this (the ; should be between them). I try to use macros but that is not the program that I have full knowledge about it. S
( Like this
IF
A(i)= red
Then
B(i)=A(i)
end if
)
View 3 Replies
View Related
Jul 23, 2007
I have two formulas that I have worked out to deliver the results independently. I would like to join them together into one formula and add an If condition that if the two match then add a "+" to the end of the string.
#1 finds the base value :
=IF(ISERR(FIND("/",I2)),I2,LEFT(I2,FIND("/",I2,FIND("/",I2)-1)-1))
#2 verifies the value is a valid color:
=INDEX(M1:M41,MATCH(J2,M1:M41,0),1)
I would like to join the two formulas together and if they match then add a space, " " and a "+" to the end of the string. If there is no match then return the base value.
View 9 Replies
View Related
Aug 3, 2009
i have about 100 worksheets in my workbook ..i need to combine them into one worksheet ...all my 100 worksheet has many formulas in some of the cells.
i want to combine all the worksheets one below the other with a gap of 5 blank rows after each worksheet contents....i just want the values in each worksheet to be pasted and no formulas...
View 9 Replies
View Related
Jun 24, 2014
I need to know if it is possible to combine these 2 formulas (1. (RIGHT(C23,0)&RIGHT(C23,4)) and 2. LEFT(E23,2)&":"&RIGHT(E23,2)
into a single cell. I have a date time group that is in text format. I need to take the last 4 digits (the time) and pull it out into another. Then take that cell and convert it into an actual time ie 2:30.
example: 01may2014 0831 first formula pulls the 0831 into another cell. then the 2nd formula converts it into a time 8:31.
After this occurs, I can then subtract times from different categories in my spreadsheet.
My, problem, is that I cannot combine the two formulas. They work great by themselves. I am just trying to make formulas more compact and hopefully more efficient.
View 5 Replies
View Related
Mar 12, 2014
I'm looking for a way for Excel to combine formulas together into a more compact form for me.
Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.
Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?
Here is the one-cell version of a formula I work with.
[Code] .......
That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.
Attached File : Excel Formula Combination Question.xlsx‎
View 7 Replies
View Related
Oct 14, 2009
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
View 2 Replies
View Related
Jun 15, 2013
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
View 9 Replies
View Related
Apr 21, 2009
I am creating a Sales Pipeline. I have learned everything I know about excel on my own...lol. So probably not so much. My problem is I need a formula that can total a range of cells only "if" they meet "2" different criteria. Is this possible. I am attaching my workbook. I have formulas in there but Im not getting totals. I dont know where Im messing up. (all info in work book is fiction)
View 4 Replies
View Related
Jun 4, 2009
in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is $168 but in FEDEX that one is not correct 1kg to Japan is $169 but now show $342, the formula in "G36" should be correct, what's wrong of my formula????
View 3 Replies
View Related
Nov 11, 2009
I have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies
View Related
Jan 1, 2010
I want to tell Excel that, if a cell value is between 1 and 100, to enter one result in another cell. If the value is between 101 and 250, enter another result in that same cell. If the value is between 251 and 500 enter another result in that same cell and so on about another 12 times.
View 5 Replies
View Related
Oct 14, 2009
i just wanted to know if we can open a pop up which will give us answers. i have attached a example work sheet of what i require
View 3 Replies
View Related
Jul 3, 2009
I have a formula that is in ColF Row4, and it is copied down to ColF Row 13. But trouble is that when I autosum this column I get 6, but the answer should be 8.
The data in Rows A-E are extracted from an external data source. I've attached the spreadsheet. =IF((AND(A4=7,D4="")),1,(IF((OR(A4=7,D4>=2)),D4,0)))
View 4 Replies
View Related
May 24, 2008
I have to following code. I have an issue now where I have to Pop a textbox from based on the answer to one of my questions.
If cboProjOffset = "Other" Then
userSalOther.Show
Problem is I actually want the text box to populate on the same line of the range answer in column m. At present it doesnt seem to be doing this. Can anyone help?
Public gintEditMode As Boolean
Public gintTotalEntries As Integer
Public gintLineNumber As Integer
Private Sub cboProjOffset_Change()
If cboProjOffset = "Other" Then
userSalOther.Show
End If
End Sub
View 9 Replies
View Related
Apr 5, 2009
in cell d1 i have
hk/q46559
in cell i1 i need to return whatever is before the "/"
in this case hk
and in cell i2 i need to return the number after the q
in this case 46559
View 3 Replies
View Related
Jan 11, 2010
I have a few 2007 workbooks that are set up to track and compare individual budgets over time. The majority of the cells are referencing workbooks that have yet to be created.
On opening these workbooks the users are confronted with an informational window informing them that "This workbook contains one or more links that cannot be updated", to which they are instructed to choose "Continue". Is there code I can add to these workbooks that would automatically choose "Continue" once a user enables the security warning for Automatic updates of links? I believe this would be the same as an On Open event.
View 7 Replies
View Related
