I'm looking for a formula that can do 2 different functions in one cell. I have a subtotal in A1 and my total in B2. Now, I want B2 to say if A1 is lessequal to $100,000 then multiply by1.5% plus A1 and if greater than $100,000 muliply by 2.5% plus A1.
View 9 RepliesBasically I want a formula that automatically inputs a code based on 'marital status'. This would be easily done using a vlookup except for the fact that one of these marital statuses has multiple options. Example: if the marital status reads "Married" the code can very depending on whether it is different gender/same gender. I have gender codes in a separate column.
For example:
Status--->Code
Single---------------->Sin
Civil P'ship------------>Civ
Married + MM--------->Mar 1
Married + FF---------->Mar 1
Married + MF--------->Mar 2
Married is, of course, in a separate cell from the gender codes, which is why I've attempted to bring the AND function into the IF statement. However, I'm having no luck.
The spreadsheet I am designing is already getting pretty big so I wanted to avoid having more columns than necessary. Is it even possible to group this together in one formula?
This is an example of a formula I have tried but returns the #value error.
=VLOOKUP(I31,Mapping!$A$1:$C$12,3,FALSE)+IF(AND(I31="Married",AH31="MM"),"MAR 12","")
I am currently taking Excel but neither my professor nor my text explained how to make a formula where the formula has two different operations, (e.g., division then addition) within the same formula. For example, in Y6, create this formula: Divide P7 by the sums of H4,I4, and J4. I tried this formula but I think it's wrong: =P7/(H4,I4,J4)
View 3 Replies View RelatedI have a list of part #s that i need to group together and sort by their corresponding dates. The formula I've written does what I'd like it to do (assign a value to part groups that i can sort by first before sorting by the date) , but is too large/has too many nested functions to be applied. This formula is going to be used with multiple queries from a database that updates regularly.
EXAMPLE OF DATA(6000+ entries)
http://www.excelforum.com/attachment...1&d=1228860131
This is the breakdown of my formula, I've bolded the parts that aren't constant
=IF(AND(LEFT(D2,5)>="S2000"LEFT(D2,5)<"S2005"),1,IF(continues)...................
I've been using Excel for years and had very few issues. However, I recently went into a spreadsheet to update it and was unable to select and enter data into an individual cell. When I click on a cell and try to enter #s nothing happens (my num lock is on).
Then when I try to click into another cell it just highlights that cell, along with any other that I move my cursor over. Once I click on one cell I can't stop the highlighting from happening. I can't even click on anything in the toolbar.
correctly using variables within functions used in formulas specified by VBA. Here's an example:
I want to place a formula within a workbook file called "Books 2003.xls" that goes to the version of the file for the previous year (or whatever year is specified) and does a Sum of a particular range (where that range is also specified as a variable) so it sums up the Total for the previous year for the same number of months that have data in them to date for the current year. Once VBA places the formula where it belongs, it should be able to always provide a running comparisson with the current year to date total and the totals for the same period for the previous year. Here's an example of the intent of this simple formula:
ActiveCell.FormulaR1C1 = "=SUM('[Books 2002.xls]Income Summary'!R4C2:R8C2)"
The workbook files will all be named the same way: Books 2002.xls, Books 2003.xls etc.... The range to be summed will always start at R4C2, but could then end anywhere from R4C2 (same as B4) up to R15C2 (same as B15).
I already have a routine that captures the value for the variable CurrentYear in the form "2003" and thus have another variable for PrevYear (= CurrentYear -1), and so then have a way to correctly specify the variable "BookName" to get the correct filename needed. I also have a variable for the CurrentMonth in the form of "1 through 12", and can use that to specifiy the correct RowNum needed to determine the end of the range (RowNum = CurrentMonth + 3).
I've tried to substitute the variables "BookName" and "RowNum" into the above formula with various combinations such as what follows, but I'm not getting the correct syntax with the right number of quotes etc...:
ActiveCell.FormulaR1C1 = ""=SUM('[" & BookName & "]Income Summary'!R4C2:R" & RowNum & "C2)""
This doesn't work, and I'd love to learn the correct rules for syntax when inserting variables into situations like this.
SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.
=SUMIFS($C$22:$C$30,$E$22:$E$30,>=LEFT(L21,3),$E$22:$E$30,<=RIGHT(L21,3),$A$22:$A$30,G22)
Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.
I am using Excel 2010.
I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")
Here is a sample table with showing the data and result
4
5L
4,5L
5
6R
5,6R
5
7L
5,7L
6
10R
6,10R
6
3L
6,3L
I've been struggling with the following problem for hours now and getting nowhere fast.
I've got '=(100/X6)*L6)' in cell M6 which gives my percentage fine but I need to round to the nearest integer with '=ROUND(M6,0)' Question is how do I combine both functions to provide the nearest whole number in M6 ??.
What would the formula be to complete all three of these functions in one cell?
64
-3
61
When positive & negative then G1+T1=X
-8
-8
0
When negative & negative then G2-T2=X
3
2
1
When positive & positive then G3-T3=X
I made a drop down list in cell H11 for Marital status (married,defacto,single,widow). Those are the 4 options in the drop down list.
Now im trying to make in another cell IF (H11="Married" OR "Defacto,950,false)
but wont work, i know im wrong but i learn by trial and error or last resort MREXCEL and m ytrial and error didnt work.
When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula?
E.g. say you have:
= SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207")
And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part?
Im using Excel 2003 by the way.
I'm putting together a table which shows monthly costs versus Total costs. Is there a way I could use if two choose between two option dependent on the value of cell A1.
Example
A1 = "Totals", multiply Subtotal in A13 by 12.
A1 = anyother value multiply by 1.
Something like:-
=if(a1="totals",(a13*12), =if(a1<>""sum(a13*1)
i have wrote the following code snipped:
Sheets(2).Activate
Sheets(2).Range("D8").Value = Application.WorksheetFunction.Subtotal(109, Sheet(4).Range("BJ3:BJ" & b)) + Application.WorksheetFunction.Subtotal(109, Sheet(3).Range("BJ4:BJ" & a))
The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet
I have six complex functions, which I would like to place in [A2] depending on what is placed in [A1], The functions themselves are quite extensive and I don't want to use any active functions (like INDIRECT) as this will cause slow calculations. [A1] will just be a given name to each of these functions.
View 3 Replies View Relatedreferencing constant cells in custom functions. This is a sample of my code:
Code:
Select Case Name
Case Is = "Ball"
Valve_Days = [V4] + Cushion
Case Is = "Check"
Valve_Days = [V5] + Cushion
Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
I want to calculate the following...
If Cell A>40, Then the result should be ((Cell A-40)*23.25)
If it is not >40, Return 0.00
This is for a timesheet / overtime.
Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:
View 4 Replies View Relatedi have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.
View 7 Replies View RelatedSo I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.
To visualize, i just want to add up the students that have not attended a class.
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type
Sample file attached.
I am trying to write a formula that will satisfy the following:
C1 and G1 are number values.
IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.
I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.
View 4 Replies View RelatedCurrently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).
Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
= left(worksheet name! row(1) column.range("startno"),2
I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.
Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.
The file I am working with is available here
(1) I need to work out the value of 2 years worth of interest on a loan - details would be
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months
I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...
the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)
The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...
(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...
what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee