IF Function To Stop 401k Match At 1500 If False
May 6, 2009
I'm trying to create a IF function to stop at $1500 for 401k employer match. To make it simple the variable in column D is constant but in reality it's based on 50% match amt up to $1500 of amount contribute(% of gross pay). The accumulation of matching is a rolling balance. If the match amt is less 1500, then match amt. But False, the left over amt equal 1500 then STOP there onward.
IF(SUM($D$3:D19)+($C20*0.5)<1500,$C20*0.5,SUM($D$3:D19)+($C20*0.5)-1500)
View 4 Replies
ADVERTISEMENT
Oct 12, 2007
I have two columns A & B. If I enter "Short" into Col A, I want "Long" to automatically appear in Col B, and vice versa. This is the code I have created so far. =IF(C4="Short","Long",IF(C4="Long","Short",IF(C4="",""))). This works but when I paste the formula into all cells in Col B, I get "FALSE" showing up in every cell. How can I stop the "FALSE" from populating every cell in Col B while Col A remains blank?
View 4 Replies
View Related
Nov 11, 2009
I have this equation
=(INDEX(abc!B8:D18,MATCH(Sheet1!C39,abc!A8:A18),MATCH(Sheet1!D39,percentiles,0)))
it gives me this #N/A. I'd rather have it show $0.00.
View 9 Replies
View Related
Nov 29, 2009
I have several buttons on my userforms that use the following ...
View 12 Replies
View Related
Apr 28, 2012
Cell A1 = Employee
Cell B1 = Salary
Cell C1 = Years Worked
Cell D1 = 401k contributions
Cell A2 = 3%
Is there a formula I can use in Cell D1 (401k contributions) to multiply B1 (salary) by A2 (3%) if C1 (years worked) is greater than "1" and for it to show a "0" if C1 (years worked) is "0"?
If there is a formula that will work for this, it gives me the opportunity to adjust the percentage amount in cell A2 to see how contributions would differ.
View 5 Replies
View Related
Sep 25, 2009
I have a master spreadsheet which shows agreeed rentals for vehicles, on the sample this is the "OCS" tab. I receive a spreadsheet which contains the actual rental charged, in my example this is the tab "Invoice" in my example the VRN are in the same order on both sheets, in reality the invoice spreadsheet is not in the same order as my OCS sheet. My task is to match the VRN field in the invoice sheet to the VRN in the OCS sheet, then look at the amounts and if they match then return "true" if they don't return "false
View 2 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Jun 5, 2009
I am trying to use the IF function to return a false value if the combined sum of a row is not the same as either of the 1st 2 cells. If that makes any sense!
This is the formula I would like to use. =IF(SUM(g3:g9)=g1 or g2 then y if true and x if false. I thought it would be easy but I can't figure out how to do it
View 2 Replies
View Related
May 30, 2006
created my 'IF' function with the 'True/False' statements, but i am wanting to have yes/no instead and i cant figure out how to do it.
View 9 Replies
View Related
Jan 18, 2007
I am still having bother with if statemnts (within a cell in excel - not a macro)
Sicarii kindly assisted me the other day with this...
=IF(A1="&","V",IF(A1=" &"," V"))
*note* there is nothing set in this for FALSE.
How do I get the statement to return nothing (i.e.: have "" in the return) instead of 'false'? To explain further, I have in a column, the following:
&
^ & (i.e.:'(space)&')
&
33
&
^ &.........................
View 7 Replies
View Related
Mar 26, 2014
I'm trying to create a rec report, ideally on one page, for multiple criteria. The situation is we get a report on our trade positions (investment stuff) from an outside service we use. We also have a report internally from what we've booked. We're looking to tie out are basic things like price, original par, revised par, trade date, counter-party banks, etc. I've consolidated data from both sources on one page (DATA) where all data points are lined up (say EXTERNAL is from rows 1-500, and INTERNAL is 510-1010). Now I need to create a front page showing the result of each tie-out. The lookup value will be a combination of the fund name + trade ticket number which would almost guarantee a unique ID (fund name XYZ, ticket number 12345 = XYZ12345)
Current tabs on spreadsheet: DATA, EXTERNAL, INTERNAL. I need to add a REC page
Question: What formula can I use to check on each and return TRUE/FALSE? The goal is to have about 8 columns on the REC, each for one of the tie-outs (let's use price as an example.). I know the basic VLOOKUP to return one value, but it appears in this case I will need VLOOKUP to check two matching values based on unique ID XZY12345, compare the price, and return TRUE if they match, FALSE if they don't
Here's something I saw from one of our spreadsheets that I think does the same to confirm counter-party, though I have no idea how to read this lengthy formula.
=EXACT(VLOOKUP(IF(Q2="COMPANY",R2,Q2),FUNDS!$M$1:$N$400,2,FALSE),
INDEX(PENDING!$G$4:$G$3218,MATCH(CP!C2,PENDING!$N$4:$N$3218,0)))
View 9 Replies
View Related
May 7, 2009
I have calculated the t values then i have three tables for 3 confidence levels, 0.01, 0.05,0.1 in which I compare the t value with the value from the
t-test table. If the t value is larger it returns a 1, if not a 0. This works fine.
To summarise the information I have a final table which I wish to return the highest significance of the given t value.
I am using this
=IF((G53=1),0.01,(IF(G44=1,0.05,(IF(G35=10,0.05,"none")))))
the problem I have is it always returns 0.01 even if g53=0. If i remove the formula from g53 and replace it with a 0, it returns 0.05 instead (even if g44 is retruning a 0).
I have considered copy paste values and doing this but that rather removes the automation aspect of the spreadsheet if I need to make adjustments.
View 4 Replies
View Related
Dec 10, 2009
This is probably quite a simple request but here goes:-
=SUM(h2,e2-f2)
I would like excel to perform this calculation but only if another cell contains a date. I couldn't figure out how to do that so I added another column next to that cell and added =ISNUMBER(A8) to return a true or false statement. I was then going to hide this column but still refer to it.
transaction cleared amount amount balance
date date in out
A B C D E F H
1 $500.00 BALANCE BROUGHT FORWARD
2 1/1/10 TRUE 1/2/10 TRUE $5.00 =SUM(h2,e2-f2)
3 1/2/10 TRUE FALSE $300.00 =SUM(h3,e3-f3)
4
5
6
7
I don't know if this makes sense of if anyone can suggest another way of working this out. New to excel so sorry if this is basic stuff.
View 9 Replies
View Related
Oct 18, 2007
I've been asked to adjust some thing with a worksheet someone else created. On the sheet labeled "Criteria" there is a list of dates and some formulas that use these dates. I can't figure out why the result of the formula is a number, i.e "0"; I thought it is supposed to return "TRUE" or "FALSE". If I insert a new sheet, within the same workbook, and insert the same formula, I get "TRUE" or "FALSE". For some reason, the "Criteria" worksheet doesn't return "TRUE" or "FALSE", it returns numbers. I just need to know why this is happening so I can replicate it in the new workbook I'm creating.
View 2 Replies
View Related
Jul 27, 2009
I'm trying to determine if a rangeName is defined so that I can delete it without a programming error. I've used the idea that if I try to goto a non-existant rangeName, I'll get an error. I've tried to design a function that will return true or false if the rangeName is defined on the basis of that idea. Can you take a look at the code and see where I went wrong?
View 4 Replies
View Related
Aug 7, 2007
I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.
The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.
=INDEX(Archive!$E$1:$E$1999, MATCH(1, IF(Archive!$B$1:$B$1999='Data Log'!$A2, IF(Archive!$A$1:$A$1999='Data Log'!$C2, IF(Archive!$D$1:$D$1999=1,1))),0))
In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:
=IF(ISNA(index function), "pending", index function)
The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".
View 5 Replies
View Related
Mar 5, 2014
Here's my formula:
=AND([@[Tenure (Yrs)]]>=5,[@[2016 Sales]]>=150000)
(Where should I enter high and low? Do I need an if formula nested into it?)
I'm trying to get it to read if the customer reads both criteria which is they've been there 5 years and over 150000 sales =
High if not Low.
View 4 Replies
View Related
Mar 5, 2008
I need to check one column to see if it is less than another one
If it is I need to have the IF function return specific criteria for true and then for false but then I also need it to rely on the results of another question from another column if the result of the first query is less than the figure in another column again I need to return specific criteria for tur and then for false
An AND function requires both to be true but when one isnt what do I write?
View 9 Replies
View Related
Aug 22, 2008
Sub do_it()
Dim x As Range
Set x = Columns(4).F ind("*test*", lookat:=xlWhole)
If x Is Nothing Then GoTo 999
r = x.Row
Cells(r, 1) = Cells(r, 1) & " (W)"
999
End Sub
I need the above code to continue down column 4 and add the " (W)" to the entire column.
The code stop when it finds the first match.
View 9 Replies
View Related
Mar 10, 2009
Where in this function would I put " " to return a blank field if false is returned?
=IF(D18>=1,(VLOOKUP(C18,C7:E13,2,FALSE))-(VLOOKUP(C18,C7:E13,3,FALSE))+(VLOOKUP(C18,C7:E13,3,FALSE))*D18)
View 4 Replies
View Related
Mar 11, 2014
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
View 2 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Mar 19, 2014
I have a program that requires the last 8 days to be total, unless there are 2 days off in a row. So my cells need to sum the previous 8, but then stop when two zeros show in the column and then restart following. One zero should continue to total the previous 8 days. As shown below, for 1/10/14 I should see 57.5. The sum needs to restart on 1/13/14 to show a total of 10.5. I have tried multiple formulas with no luck.
Col A Col B
1/5/140
1/6/1412
1/7/1412
1/8/1412
1/9/1411
1/10/1410.5
1/11/140
1/12/140
1/13/1410.5
1/14/1411
1/15/1411
View 5 Replies
View Related
Oct 16, 2008
Hi, I am runnning this marquee label function in a userform (Dash). When I close the form, using 'unload frmDash' the function is still running in the background. Can someone give me a tip on how to end it at the same time as the form is closed?
View 14 Replies
View Related
Aug 20, 2008
I have a data range - C4:C54 and I want an AVERAGE calculated in C56, but I only want a value displayed in C56 when a data entry is put in. Up to 50 data points could be put in anywhere e.g. only 14 values in C16:C30, or 44 values in C6:C50, or the full 50 in C4:C54.
The formula I've been trying is
=IF(ISERROR(C4:C54<>""),AVERAGE(C41:C54),"")
However, when no values are in the range it displays DIV/0! whereas I'd like the cell blank.
View 4 Replies
View Related
Aug 28, 2012
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
View 5 Replies
View Related
Jan 18, 2013
I have a vba function linked to conditional formatting that i want to temporarily stop while a sub to insert a line is running as the sub stops when it hits the sheet where the vba function operates. When i disable the vba function (or remove the vba function) the sub works fine, if the vba function is not disabled the sub stops.
View 4 Replies
View Related
Jul 4, 2014
I have 3 conditions within my spreadsheet, which only 1 condition can have a numeric value in it at any one time.
The result of my calculations returns the following results
Condition 1: False; Condition 2: 168001.5072; Condition 3: False
I then use the MAX command to pick up the cell that has a value in it.
The command I use is: =MAX(L28,N28,P28)
The problem occurs is when I use this MAX command it Rounds up the value from 168001.5072 to 168001.51 - I need to stop this rounding from occurring.
Now I have just been told on this forum about how to use the TRUNC command to stop my original formula from rounding my final value (168001.5072) but I'm not sure if using the TRUNC command will work in this situation.
I can not use the "=" command becuase depending on the values I use any 3 of the conditions can be met, which will mean a value will sit in either condition 1, 2 or 3, so that is why I need to pull the MAX value.
The attached image shows my issue.
The value on the right 168001.5072 is being rounded up and displayed as seen on the left as value 168001.51 and I would like ot know how to stop this from occuring.
image2.PNG
View 4 Replies
View Related
Oct 29, 2008
I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)
Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.
=IF(ISNA(B3),"0/0/00 00:00",NOW())
I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?
View 9 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
