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
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 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 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?
In the estimate form I have attached, I want it to auto figure shipping by placing a X in front of shipping type. Which it is doing but how can I get it to show $0.00 instead of false when no X is placed in front.
View 3 Replies View RelatedI have 2 columns of data: Column A is basically postcodes, but just the 1st part and Column B is a #2. If somewhere else on the sheet I type in the postcode IE. SW19 it will show besides it in my lookup the number 2. However if I type in a postcode that is not listed in Column A, I'd like to to return the value 1. I cannot figure this out. My code is:
View 3 Replies View RelatedHi. Is there a way to make OR() return 0 or 1 instead of true and false?
Or to convert true to 1 and false to 0?
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 RelatedIf a value in cell A1 on Sheet Report is found in Range B2:B10 on Sheet Hierarchy Then return True Else False
I have the following so far:
[Code] .....
I want Match to be either True or False. Also, is there a way to know the cell the value was found in if True? How do I make this work?
I have the following formula,
=IF(AH10="","",IF(VLOOKUP(AH10,Sheet3!C:D,2,FALSE)="","",VLOOKUP(AH10,Sheet3!C:D,2,FALSE)))
that I would like to return a blank cell not N/A if the value that is in "AH10" is not found.
I have the following formula:
=IF(ISBLANK(H16),"",IF(H16="Car","Ca",IF(H16="Boat","Bo")))
Where I would like it to return a Blank cell if H16 is blank. However it always puts False in the cell because there is a Vlookup that is hidden H16. Is there a way I can make it return as a blank cell instead of false.
I have no idea where to begin on this problem.
Group Name / Data
A / .01
A / .02
A / -.02
B / .05
B /-.01
C /.02
C /-.03
I need one true or false return for each group if the absolute value of the differences in any combination of a group's data is greater than or equal to .05.
Expected Result
Group A False
Group B True
Group C True
Below is the forumula I am using. The problem is, when the condition is false, it puts a "0". This then effects the conditional formatting as well as the count function at the bottom. I need it to just leave the space alone when the If statement is "False"
=IF(G3="USD",IF(COUNTIF(Data!$R:$AH,$J3),VLOOKUP($J3,Data!$R:$AH,2,FALSE),""),F3)
I have two price lists in workbook. One containing "normal" price list, other containing "action" prices.
I want to search for a product name code from column A in "normal" price list in column A of "action" list, and if it is found to show it somehow in any column in "normal" price list sheet.
This way I know that there is action price attached to that product and that I have to search for the price in "action" sheet.
I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:
View 6 Replies View RelatedIs there a formula or VB code like the =TYPE() function that will return whether a cell contains a FORMULA? Here's the issue:
I'm writing a macro to update 10,000 sheets. Each sheet has a column that I'm updating with a new formula. Some of these formulas have been previously overwritten with a random number that would delete the formula from the cell. I have to leave the overwritten values where there is no formula, and replace the values of the formulas where they are still intact. I assume that if I can identify whether there is a formula in the cell or not, I can choose that cell to skip or update.
I have two price lists in workbook. One containing "normal" price list, other containing "action" prices.
I want to search for a product name code from column A in "normal" price list in column A of "action" list, and if it is found to show it somehow in any column in "normal" price list sheet.
This way I know that there is action price attached to that product and that I have to search for the price in "action" sheet.
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 am looking for a formula to return either True/False in Column J if the font color in Column H is Red.
Is there a formula that can do this?
I have an IF statement that I need to look at different cells and return different values if ture or false, depending on what is selected in cell J2
Cell D2
if J2 = "FMB/FPI" then retrun the value of cell Y7
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then return the value of cell Y7
Cell E2
if J2 = "FMB/FPI" then retrun the value of cell AC7
if J2 = "FPI/FPI" then then retrun the value of cell AC7
if J2 = "FMB/OTHER" then N/A
Cell F2
if J2 = "FMB/FPI" then N/A
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then then retrun the value of cell AC7
I have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE
View 9 Replies View Relatedhow return a true/false based on a cell containing a string (text) vs a value?
I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.
Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
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 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 RelatedI'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