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 RepliesI have several buttons on my userforms that use the following ...
View 12 Replies View RelatedI'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)
I am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not.
View 5 Replies View RelatedI've created an equation but when it's false it returns a "0" I tried an IFERROR with no luck.
=IFERROR(IF(AND(C6>="",E6=""),"update system",),"")
I'm trying to apply the istext to a cell that has multiple values eg: [car, boat, truck] so I have setup the cell with the formula
=istext(c147 = "car",1,0)
it returns false constantly. Is that becuase of the other values separated by comma's in that same cell?
Is there a way to do this so that I don't have to go through 300+ cells that have multiple values to separate them?
The formula I am having issues with is:
=IF(L14="YES",IF(B14="","",NETWORKDAYS(B14,IF(AH14="",TODAY(),AH14),IF(L14="NO",NETWORKDAYS(B14,IF(V14="",TODAY(),V14))))))
I'm looking for formula to return a value based on the amount of networkdays between 2 dates.
Column L has a drop down for
"YES" and "NO"
If "YES" is selected then I need the formula to subtract AH from B (if B is blank I don't want the formula to return anything)
if AH has no date then I want it to use todays date.
If "NO" is selected then I need the formula to subtract V from B (again, if B is blank I don't want the formula to return anything)
if V has no date then I want it to use todays date.
The formula seems to work for everything as it is EXCEPT when L is "NO" it only return FALSE.
I have a formula in column A of the following sheet. The problem is that when the entry in cell H contains a letter as well as a number then the formula in column a returns 0.
Also if the value for false in the forumula is changed from 0 i.e to 1 or 2, then a value is returned in column A (this is shown in the red highlighted cells).
AssetRegexample.xls
Does anyone have any idea why this is?
I have the following formula that sometimes returns an #N/A value. Is there a way I can stop that from happening?
View 9 Replies View RelatedI 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?
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
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 RelatedI 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
&
^ &.........................
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.
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.
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 RelatedI'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 RelatedHere'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.
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?
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)
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
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 RelatedI 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.
I have an IF Function that if C4 says HUD then it returns the infor in a range of cells on the HUD worksheet, which is a separate worksheet from where this formula is {=IF(C4="HUD",HUD!A1:C100,"")}. I have also used =IF($C$4="HUD",HUD!A1,"").
But now, on the cells that it is returning, it puts 0 in cells that are blank on the other worksheet. What can I add to this formula to eliminate that?
Both of these functions are working otherwise. They are just returning 0 in cells that were blank.
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 RelatedI 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
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies View Relatedtrying to create a 'countdown' to time of macro completion (based on a static performance time of 1:48). What I want to do is compare a time (ProgTime) set as static at the beginning of the code with 'TIME' and return the difference in seconds. I use the following code:
[Code] ....
When I step through, I can see that ProgTime and Time have different values and yet 'Test' always returns a value of '0'. I tried using 'Now' instead of 'Time' and the same result ('0') occurs. I have experimented with declaring 'Test' as an integer, a long and a date and the result is always the same! I've tried using 'Seconds() around the datediff to ensure an answer in seconds, I've tried 'format' around it with multiple decimal places to make sure I'm not missing a tiny value: Nothing seems to do the trick!
I am new to VBA programming in Excel (currently 2003) so hopefully this isn't a "dumb" question.
I would like to re-arrange a group of data from a table of several rows and columns to a single column where the data from the first row is translated to a column then the second row is appended to the end of that data ....
ie:
( 1 ) ( 4 ) ( 7 )
( 2 ) ( 5 ) ( 8 )
( 3 ) ( 6 ) ( 9 )
to
( 1 )
( 4 )
( 7 )
( 2 )
( 5 )
( 8 )
( 3 )....
I thought a user defined formula would be a good place to start but am having problems getting the address of the cell that the function resides in to use to calculate the offset from a fixed cell (to be entered into the function as a argument). In other words, how do I get the address of the call that a UDF is entered in into the UDF? activecell.address is returning the cell that was last clicked not the cell were the UDF resides.
Secondly is there a better way to achive the results that I am looking for other than through a function? The size of the table will vary from worksheet to worksheet. I would also like to add an argumnet that lets me exclude cells with certain content from the resultant list.
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?