IF Statement in Excel










i want to test a cell - a2 - for a value >0
if >0 i want to multiply a1*a2
else do nothing
t.i.a.



I am trying to set up If Statements to read dates for several projects and
from those dates set priority levels of A, B, C, etc... Can anyone offer
assistance?



Hello. I have worksheet in which I have data in the A column, data in the B
column, and (sometimes) in the C column. I'm having trouble with the correct
formula. I want to put in the D column something that states If "C1" is
blank then enter A1. And If "C1" is not blank, then concatenate A1 and B1.

I hope that makes sense and thank you in advance.





I have one cell (G17) that takes the overall average of four yearly averages (C9, C11, C13, C15). However, if one of the years (C9, C11, C13, C15) does not include data then it is marked as #DIV/0!. How can I calculate G17 without having to manually delete the (C9, C11, C13, C15) cell that contains the #DIV/0! error? I'm sure the solution is an IF-then statement but I'm having a hard time executing it.


Hi

I have the following formula in cell F16

=IF($M$1 =3, "149028","")

However, what I would really like is for the user to be able to input a
value in F16 is M1 does NOT equal 3.

Is there a way to do this. The current formula leaves F16 blank if M1
does not equal 3, but when the user types another value, the formula is
erased.

what am i missing? Many thanks




I could be way off here, but... I am trying to do an IF statement and then depending on the answer run a macro or end the statement. In Lotus I was able to do the BRANCH macro, but it seems you cannot do that in excel. This is an example what I'm trying to accomplish.

If the value of cell M50<= to 25 then do nothing - if it's greater than I need another macro to run.

Thank you for any help!!


Hello,
I have the following IF statement:

=IF(SUM(F5:G5)=0,"",F5/SUM(F5:G5)*100)

In charting this data, the "" is charted as a 0 instead of a blank. I want
it to be a blank. Is there a way around this?

Thanks,

Bill





I am trying to creat an IF statement that will be true if the number falls
within a range of numbers. I want it to say IF A2 is less than 200 but
greater than 100 then this cell will display 6". I only know how to write an
IF statement for a less than or equal to statment but I dont know how to make
it fall in a specific range of numbers. please help thanks.



A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3

How can I do the following in A1, A2, A3:

If B1 AND B2 AND B3 are Blank the A1=YES.
IF B1 OR B2 OR B3 have any values then A1= NO

Thanks!


Widow's Son need help to program templates and then mail to members with
labels



I have a formula that returns a value based on the value of another
cell on the same worksheet. For example, cell D1 has a value of
97.00%. The formula is

=if(d1<.93999, 0, if(d1<.94999, .35, if(d1<.95999,.55,
if(d1<.96999,.75,if(d1>=.97,.90,"")))))

The formula should return a value of .9 in the cell containing the
formula. I am getting the #Value error though and can't figure out
why. If I change the last if statement to read if(d1<=1 then the
formula returns the correct value. The syntax may be off slightly
because Excel is not correcting me but I think you have the idea. Any
ideas?




I need help with a problem I have please.

I have two separate sheets A and B.

I am trying to count the number of times a product appears in sheet B and the return this value in a cell in sheet A. sounds simple enough.

However a simple VLookup function is not working because the value that has to return to the cell in sheet A has to match 2 criteria along an X and Y axis.

I am trying to count the number of times the product appears in sheet B relating to a specific placement and bring the value back to Sheet A in the specific cell.

Here is an example of what I mean as the sheet i am working from has thousands of rows:

Sheet A

Product 2 Product 3 Product 4



Placement 1 value? value? value?

Placement 2 value? value? value?

Placement 3 value? value? value?




Sheet B


Placement 3 Product. 5

Placement 2 Product. 3

Placement 1 Product 3

Placement 2 Product. 3

Placement 3 Product 2

Placement 3 Product 2

Placement 1 Product 2

Placement 2 Product 4

Placement 3 Product 4

Any assistance would be very much appreciated.

Thanks,

Adrian


Can I create a formula to return a calculation based on a certain value selected from a drop-down list?

See attachment for more details. Thanks!!!


I need to cut an entire row and paste to another sheet within the workbook if the data in a specific cell equals the data in another cell. I need a macro that will check all rows for this condition and perform the cut and paste.

Example: If C2 = C8, select row C and move to worksheet "2".

Any help with this would be appreciated.


I am looking for a formula that will subtract subject c from subject a because the output variable reads "x" for both rows. I want it to skip all the "y" variables so that A-C = 50 and C-E will equal 40 for example. I was playing with a nested if statement but can not get it to work.


Subject Score variable output
Subject A 100 x 50
Subject B 75 y
Subject C 50 x 40
Subject D 25 y
Subject E 10 x


I'm trying to use an IF statement for referencing a cell's text value within a data range and if there is a match, then go to another cell and display its value, if not then "No Match"

As an example: Cell R6 contains the text "AA06", the range of data I want to see if "AA06" is present is from E13:M24, if it is present, I want to display a specific cell as the true value and if not "No Match"

Here is the IF statement I can get to work on a single cell reference:
=IF(R6=D13,D14,"No Match")

Here is one that give me a #value! error
=IF(R7=E13:M24,E17,"NO TX")

Any help is very appreciated.


Is it possible in excel to have an If statement send out an email message if certain conditions are met.

E.G.
I have a database with user account expiry dates. What I want excel to do is send me an email message when account is approaching expiry. Mission critical operation. Any help greatly appreciated.


I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.


Hi guys just wondering if there is a way to ask in an if statement to check if the text is in bold and return a value and a zero if not. Thanks in advance


i'd like to say IF within a cell, along with a number there is a particular letter, then produce a particular result. anyone know how to do that? thanks


I am trying to ignore blank cells in a certain column. Columns A and B contain data. Column A contains number between 2 and 11, and column B contains measured data.
The formula I am using is as follows:

{=VAR(AVERAGE(IF(A1:A100=2,B1:B100,"")),......}

This formula goes on using 2 to 11 in the logic test statement and takes the variance. But, if one of the cells is blank in the B column, an error occurs in the IF statement evaluation, leading to an error as the final answer.
This workbook will be used by others, and there is no way to predetermine which cells will be blank when data is pasted into the worksheet.

Thanks.


Hey,

I am trying to make an If statement that will look determine if a cell is a number or text, if it is a number than display that number times a constant, if text than display a different cell. Here's what I've got so far:

=IF(F12>0,F12*85,L12)

The true part is working, but the false returns a #value! error. I'm thinking I need to nest another if statement but don't know how to tell excel to differentiate between numbers and text. Note: The text in the cells are all different, so I think I'm looking for something that indicates generic text.


Hey gang,
How do you do two "if" statements?

I need to have two tests before i get a final result so i need to know the protocal to get two if statements.


Hello,

I have a simple work book. Please refer to the attachment Book1.xls.

In B1, the data is limited (by Data Validation with a drop down List) to Yes and No.

I need to limit the range of data in D1 as, If B1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if B1 = No then the data entered into D1 should be Less than or Equals to 50

I prefer to do it with Data Validation. Please help.


Hi,

I have one column of sample numbers and another column of tree species (text values). There are a lot of trees for each sample number. I need to find the most common tree species for each sample number. So I have this formula to find the most frequently occurring tree species for all samples:

=INDEX(B:B,MATCH(MAX(COUNTIF(B:B,B:B)),COUNTIF(B:B,B:B),0))

and that works. Is there a way to modify the formula to find the most frequent text value for only those rows of B where the value of column A equals 1,2,3, etc?

Thank you!!


Is there a way to look at a cell that has a numeric value and create code that is based on the number of decimal places the imputed number has? For example if I enter 90.32 I want a particular result, but if i enter 1.0574 i would like a different result.


I've searched through about 4 pages of threads with "time" in the title but cannot find an example of this situation.

I am trying to build an if statement to test variables that are in time format and then perform a calculation.

I was able to get an example working if I convert the start times to integers rather than time values. However, the data won't be provided to me as integers.

So, I need a nested if statement (using "and") that will test for two situations using cells in time format or I need to write a macro to convert the time data to integer format. I've been working on the former most of the day and have hit a brick wall.

Can someone take a look at this and offer a solution or perhaps something I haven't considered.

Thanks


Here it is in a nutshell:

I have the following parameters that i must meet.

in cell A1 i have a value that is changing.

I want cell A2 to look at this value and determine what to do, it must meet the following things. I am thinking this can be done with an if statement but i am not sure exactly how.

if A1>600, A1*.02
If A1>399, A1*.03
If A1<400, 5.50

so basically what i am trying to do is look back at A1 and if the value falls between 399-599 multiply that value by 0.03, if the value is lower than 400 then report 5.50, if the value is 600 or greater then multiply by 0.02

Thanks in advance.


Hi,

I want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.

If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.

Thanks

Matt


I am trying to write a formula that uses both an if statement and a vlookup statement.

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?


Hi all, here's kind of an interesting one I think. I have a formula in a cell which attempts to grab a value on another sheet that's dynamically changing in real-time (it's a live stock price changing in realtime via DDE). My objective is to grab this current live stock price when it is between the times of 10:30 AM and 5:00 PM, bt as *soon* as it goes past 5 PM, I want this formula cell to display the very *last* (ie.the one just before) value it had just before the time rolled over past 5 pm.

Here's my formula:

Code:

=IF(AND(TEXT(NOW(),"hh:mm:ss") >="10:30:00", TEXT(NOW(),"hh:mm:ss") <="17:00:00"), Tickers!T12)


... as you can see, the "live" price I'm grabbing is Tickers!T12. This works fine during my specified times, but as soon as it goes past 5PM, it displays FALSE in my formula cell (presumably because I never specified a 'false' condition in my IF statement). Instead of it showing FALSE, I need it to show the very last live price it had *before* it rolled over past 5 PM. Maybe this is not possible?

Thanks!
Shawn


Hi All

Is it possible to use an IF statement to change a cells fills color? I am wanting to change a cells fill color if my IF statement is true, and if false then make it nofill.

Thanks for any assistance

Redders


Hi,

I want to extract specific entries from the bank statement to copy them and paste on a separate excel sheet. How can i do this? This I need to do for a specific bill payments has been made. Thanks for your kindly effort.



I'm looking for some help to include IF into the following formula

=IF (G21=IN), CEILING(((E23*F23*G23)/462)*D23,0.5), IF (G21=CM)CEILING(((E23*F23*G23)/366)*D23,0.5)
Currently it is giving me an error I can't figure out. Appreciate the help

thx
MK



Here's my Min statement

=MIN(C21,C23,C25,C27,C29,C31,C33,C35,C37,C49,C51,C53,C55,C57,C59,C61,C63,C65,C67,C39,C41,C43,C45,C47 ,C69,C71,C73,C75,C77,C79)


However i need to add a few more cells, so it looks like this:

=MIN(C21,C23,C25,C27,C29,C31,C33,C35,C37,C49,C51,C53,C55,C57,C59,C61,C63,C65,C67,C39,C41,C43,C45,C47 ,C69,C71,C73,C75,C77,C79, C81, C93, C95, C97, C99, C101)

But when I do that I get an error that says " More arguments have been specified for this function than are allowed in the current file format"


Does anyone know how to counter this?

And no I cant just drag the min all the way down the column, because I need certain cells, which are every other one.


Hi,
I would like to download my bank statement directly to a template. I can very easily download from the website to excel, but then I am stuck with a lot of time consuming formatting, sorting, summing, etc.

I notice there are a lot of options from the Data>Get External Data tab. I am thinking this may be where my answer is but I'm not very familiar with these options.

Thanks


Hi,

I have a spreadsheet which calculates the time of each delayed plane leaving an airport. I have been able to calculate the delay. I now wish for the spreadsheet to separate the data dependant on length of delay. For example I have tried using an if statement along the lines of =if(A1<15,1,0) to return 1 if the flight was delayed by less than 15 minutes, but this returns 1 for all flights delayed, irrespective of their departure delay.

Thanks,

Luke


The numbers in B3=0, C3=2 and D3=0 the formula I'm using is:

=IF(B3="0",1,IF(B3="5",1,IF(B3="1",2,IF(B3="6",2,IF(B3="2",3,IF(B3="7",3,IF(B3="3",4,IF(B3="8",4,IF( B3="4",5,IF(B3="9",5))))))))))

For cell "B3" the formula works

Now for both the next two cells C3 & D3 the formula is:

=IF(C3="0",1,IF(C3="5",1,IF(C3="1",2,IF(C3="6",2,IF(C3="2",3,IF(C3="7",3,IF(C3="3",4,IF(C3="8",4,IF( C3="4",5,IF(C3="9",5))))))))))

=IF(D3="0",1,IF(D3="5",1,IF(D3="1",2,IF(D3="6",2,IF(D3="2",3,IF(D3="7",3,IF(D3="3",4,IF(D3="8",4,IF( D3="4",5,IF(D3="9",5))))))))))

Doesn't work and displays "FALSE" ?????

I don't the luxury of wasting time, the formula should work but I don't understand why it isn't, could anyone please help me. (Probably just stressed and tried.)

Thanks ahead of time,


I have two images stored in a spreadsheet (Picture 1 & Picture 2). I want an IF statement of IF(A1="Yes",Picture1,Picture2) and it will insert the image in the cell in which the IF statement is.

Can't figure out the syntax or how to get the brougt in.

Grateful for all help...


Hello all.
I am trying to get the 3rd of the conditional formattings to format a color when a different cell has text in it that ends with an "A".
I have this formula in there now and not getting the result.

=VALUE(RIGHT($D$11,1))=A

Thanks in advance for any help!


Please help with this if statement.

I need c3 to multiply by c2 if the number is positive. In the actual case with a -13,642,000 the rsult in c3 should be 0. If it were 100 it would be -17 since it is a tax.


Help?


how do i set up a spreadsheet so it works like a bank statement?



Hello,

Does anyone know, how I might call an VBA function from an Excel Worksheet =IF function?

I'm attempting the following.... If J7 = matches a particular cell on the Config sheet, I want the VBA function Username to run. I think the IF statement is sort of correct, however I'm not sure how/or whether I can run the VBA function from this particular formula.

=IF(J7=Config!C6:C7,"=username()")

Thanks in advance.


I have a workbook with several text boxes eg Text Box 1,2,3, etc

I want to create an IF statement in a cell which says, if text box 1 contains "Hello" then 0 otherwise 1.

Can anyone help me reference to a text box, as so far im finding it impossible.


i am using excel 2000. I want to know if you can use an IF Statement to run a
macro. E.G If a cell equals 1 then run a certain macro



Is there a way to hide a row based on if a value exists in the row. For
example if I have values in rows 2-40 all in column A. I enter a value of
27 in cell A1. I would like all the rows with values of 28 or higher to be
hidden. Is this possible?





Hi all,

I want to refernce a combo box on my sheet within an IF statement in a cell. e.g. =IF(mycombobox="hello",C1="huihni",C1="njdskds"). What is the correct syntax for the mycmbobox bit?

Many Thanks,

Greg


I am trying to perform a vlookup function & an if statement in the same cell. if a cell from spreadsheet 1 is found on spreadsheet 2 then check on the same row if cell j2 = l2. if cell j2 = l2, if true then check and see if j2 is > k2 by more than 10, change the color of cell in column w to green.