cell variables in IF formula

i have this formula (Thanks Biff)
=IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
now i want to add in further variable - if J13 has "cmt" or "divs" then the 
cell is blank, then if any of the above...
thank you
0
6/6/2005 5:59:26 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
280 Views

Similar Articles

[PageSpeed] 17

=IF(OR(J13={"","cmt","divs"}),"",IF(J13=0,"Stock not 
rated",IF(OR(J13={1,2}),"Sell","")))

-- 
Return email address is not as DEEP as it appears
"Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message 
news:A22CF23A-B579-4130-A4B2-8A0BDE30623D@microsoft.com...
>i have this formula (Thanks Biff)
> =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
> now i want to add in further variable - if J13 has "cmt" or "divs" then 
> the
> cell is blank, then if any of the above...
> thank you 


0
mind-the-gap (250)
6/6/2005 6:20:47 AM
Hi!

The formula will already take care of that.

As is, if J3 is ANY VALUE other than 0,1 or 2 then the formula cell will be 
blank.

Biff

"Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message 
news:A22CF23A-B579-4130-A4B2-8A0BDE30623D@microsoft.com...
>i have this formula (Thanks Biff)
> =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
> now i want to add in further variable - if J13 has "cmt" or "divs" then 
> the
> cell is blank, then if any of the above...
> thank you 


0
biffinpitt (3172)
6/6/2005 6:23:49 AM
woops, i meant if A13 is not cmt or divs, then the rest
i have this =IF(A13={"cmt","divs"},"",IF(J13="","",IF(J13=0,"Stock not 
rated",IF(OR(J13={1,2}),"Sell","")))) but i dont think its right. because 
only part of A13 will contain either cmt or divs, so would that be a search 
if??

"Biff" wrote:

> Hi!
> 
> The formula will already take care of that.
> 
> As is, if J3 is ANY VALUE other than 0,1 or 2 then the formula cell will be 
> blank.
> 
> Biff
> 
> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message 
> news:A22CF23A-B579-4130-A4B2-8A0BDE30623D@microsoft.com...
> >i have this formula (Thanks Biff)
> > =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
> > now i want to add in further variable - if J13 has "cmt" or "divs" then 
> > the
> > cell is blank, then if any of the above...
> > thank you 
> 
> 
> 
0
6/6/2005 6:33:02 AM
Hi!

Try this:

=IF(OR(ISNUMBER(SEARCH({"cmt","divs"},A13)),J3=""),"",IF(J3=0,"Stock not 
rated",IF(J3<3,"Sell","")))

Here's a posting tip for you:

Why not just ask one question and give all of the appropriate infomation all 
at one time instead of posting multiple times and adding more and more 
conditions onto to the original question? That would make finding all of the 
replies easier and they'd all be in one thread!

Just a thought! <g>

Biff

"Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message 
news:40A0634C-37F0-4D1D-80D5-AE9F5DD4002C@microsoft.com...
> woops, i meant if A13 is not cmt or divs, then the rest
> i have this =IF(A13={"cmt","divs"},"",IF(J13="","",IF(J13=0,"Stock not
> rated",IF(OR(J13={1,2}),"Sell","")))) but i dont think its right. because
> only part of A13 will contain either cmt or divs, so would that be a 
> search
> if??
>
> "Biff" wrote:
>
>> Hi!
>>
>> The formula will already take care of that.
>>
>> As is, if J3 is ANY VALUE other than 0,1 or 2 then the formula cell will 
>> be
>> blank.
>>
>> Biff
>>
>> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in 
>> message
>> news:A22CF23A-B579-4130-A4B2-8A0BDE30623D@microsoft.com...
>> >i have this formula (Thanks Biff)
>> > =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
>> > now i want to add in further variable - if J13 has "cmt" or "divs" then
>> > the
>> > cell is blank, then if any of the above...
>> > thank you
>>
>>
>> 


0
biffinpitt (3172)
6/6/2005 7:00:33 AM
Thanks Biff. i do try to give all relevant info but its not until i implement 
suggestions that i find further things that i have wrong or inadequate, but i 
will keep that in mind
:o)

"Biff" wrote:

> Hi!
> 
> Try this:
> 
> =IF(OR(ISNUMBER(SEARCH({"cmt","divs"},A13)),J3=""),"",IF(J3=0,"Stock not 
> rated",IF(J3<3,"Sell","")))
> 
> Here's a posting tip for you:
> 
> Why not just ask one question and give all of the appropriate infomation all 
> at one time instead of posting multiple times and adding more and more 
> conditions onto to the original question? That would make finding all of the 
> replies easier and they'd all be in one thread!
> 
> Just a thought! <g>
> 
> Biff
> 
> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in message 
> news:40A0634C-37F0-4D1D-80D5-AE9F5DD4002C@microsoft.com...
> > woops, i meant if A13 is not cmt or divs, then the rest
> > i have this =IF(A13={"cmt","divs"},"",IF(J13="","",IF(J13=0,"Stock not
> > rated",IF(OR(J13={1,2}),"Sell","")))) but i dont think its right. because
> > only part of A13 will contain either cmt or divs, so would that be a 
> > search
> > if??
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> The formula will already take care of that.
> >>
> >> As is, if J3 is ANY VALUE other than 0,1 or 2 then the formula cell will 
> >> be
> >> blank.
> >>
> >> Biff
> >>
> >> "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wrote in 
> >> message
> >> news:A22CF23A-B579-4130-A4B2-8A0BDE30623D@microsoft.com...
> >> >i have this formula (Thanks Biff)
> >> > =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell","")))
> >> > now i want to add in further variable - if J13 has "cmt" or "divs" then
> >> > the
> >> > cell is blank, then if any of the above...
> >> > thank you
> >>
> >>
> >> 
> 
> 
> 
0
6/6/2005 7:09:10 AM
Reply:

Similar Artilces:

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

Update Cell Value Based on different Cell
Hi, new to the forum but I've been reading it for a couple of days now. Looking forward to talking and learning with everyone. I think I a better than average with Excel but this problem is driving me nuts! have a list of lottery numbers listed by date drawn (see below fo example) on one worksheet. On another sheet I have a count of how man times a number has been drawn. I would like to add a column to show th last date a number was drawn and have it update automatically when I ad a new drawing. However, I haven't been able to figure out how to ge the date to update automaticall...

Insert file name into Cell
Is there a way to insert the file name into a cell, rather than on header/footer? Hi Bonny, 1996FEDT.XLS =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1] =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]" for more information, worksheet examples, and coding examples for pathname, filename, sheetname and combinations of ...

Different formats within the same cell
Hello, i want to enter a word in a cell but only have one of the letter fomatted bold, eg - "wo*r*d" Is this possible -- Message posted from http://www.ExcelForum.com Hi, Yes. Enter your word into the cell, go to the formulae bar, highlight th letter you want to have in bold and then click on the bold icon. This will format just that letter to bold -- Message posted from http://www.ExcelForum.com sure. Just edit the word>highlight the letter(s) and change the font,bold,color etc. will NOT work withing a formula. -- Don Guillett SalesAid Software donaldb@281.com "...

Excel cell format #2
how can i display preceding zeros in excel without formatting as text? In article <5CBDC357-B0B2-49C2-906C-73E94C6172B9@microsoft.com>, "rockfam8" <rockfam8@discussions.microsoft.com> wrote: > how can i display preceding zeros in excel without formatting as text? Precede your entry with an apostrophe. For example... '012345 Hope this helps! Or give it a custom format like 00000 (as many 0's as you need) rockfam8 wrote: > > how can i display preceding zeros in excel without formatting as text? -- Dave Peterson ...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...

vlookup function to return the cell address of the found item
I have a one column list of data (around 3,000 items) - and I am using the vlookup function to determine if an item is in that list using something like the formula below: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list","in list") I would like to know if I can have this function return the cell address or row number to indicate the location of the item in the list - is this possible? Thank you for your time and assistance You can return the (relative) row number using MATCH, like this: =if(iserror(vlookup(A1,D1:D3000,1,false)),"not in list",MATCH(A1,...

How do I wrap text for formulaes
I have a worksheet with a lot of formulaes - need to print on a single sheet but cannot seem to wrap text them - any ideas will be welcomed. TIA, Naz --- Message posted from http://www.ExcelForum.com/ I'm not sure if this works for formulas, but it certainly works for text. Highlight the cells you need to wrap, in the menu, click "format", "cells", click the "Alignment" tab, then put a checkmark in the box beside "Wrap text". If that doesn't work, someone else will have to come with a different fix. You might have to change your row h...

Formula shows as text
I'm trying to write a formula into a cell and it will work in some, but in other cells it makes the formula show in the cell as if I am just typing text. Why is it doing this?! Please help! hi make sure the cell isn't formated as text. regards FSt1 "EmilyE" wrote: > I'm trying to write a formula into a cell and it will work in some, but in > other cells it makes the formula show in the cell as if I am just typing text. > Why is it doing this?! Please help! ...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

Adding a changing number of cells.
I am trying to add up part of a row of numbers. The number of cells in the row that I am trying to add will change depending on a variable in another cell. For instance, if there are numbers in the range a1:t1, I am trying to obtain the sum of the range that will always start with cell a1 and end with ?1 where "?" corresponds to a number in cell b1. The number in b1 is a variable and will change occasionaly based on other conditions. So if the number in cell b1 is "6" I would want the sum of a1:f1. Does anyone have a suggestion? Thanks, Thanks One way: =SUM(...

Percentage max formula?
i need to find out how divide two cells but the answer cannot be more than 150% for example: I want to divide A2 and A1 abd have the answer be on A3 but the percentage in A3 can not be higher than 150% A 1 1 2 3 3 300.00% =MIN(A2/A1,1.5) Format as % -- Kind regards, Niek Otten Microsoft MVP - Excel "liaper" <liaper@discussions.microsoft.com> wrote in message news:690B1395-2A9C-4D37-9727-9CA512E1B425@microsoft.com... >i need to find out how divide two cells but the answer cannot be more than >150% > fo...

Average Row Formula
Is there a way to calculate the average of a row if some cells are empty but when calculating the average they should have the value of the cell to the left? If the following data is enterered A B C D E F G 1 3 5 6 For the calculation the cells should use the data A B C D E F G 1 3 3 3 5 5 6 Also what would the average formula be if you wanted to skip one of the cells from the average calculation? Thanks Tom pls do NOT multipost. It wastes resources -- Don Guillett SalesAid Software donaldb@281.com "Tom" <tsanders123@hotmail.com> wrote in message news:111546...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

Displaying cell references next to embedded cells in Word 2000
Hello Group. I've embedded some excel cells into my report written in Word 2000. Is there a way I can display the cell references next to the embedded object in the printout. For example if cells B10:C15 are the embedded cells, I want those who read the printed document to see exactly which rows and which columns the numbers belong to. Something like this: B C 10 Jan 10% 11 Feb 15% 12 Mar 34% 13 Apr 14% 14 May 10% 15 Jun 12% So when I say somthing like "The formula used here is C10/SUM(C10:C15)" my readers would be able to refer to the ...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

I believe it would be called a rolling formula????
I have a complicated problem. I have a spread sheet that calculates employee points. The spreadsheet itself contains all the employee points ever earned. I need a way to only calculate the last six months. And then every time the months moves forward (from May to June) it will automatically calculate the last six months including June (ex. June back to January). The other points that were incluided from December on the spreadsheet need to stay there just not be calculated. Is there a way to do this and if there is how. Thank you -- Catherine Foster MBS-Aumsville Toll Free 800-682-14...

substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and selling price into cell H and get total in cell i. so it would have to substract cell F from cell H and put total into cell i. how do i do this? currency only Assuming your data is in Row 1, put this formula in I1 and press Enter.........be sure and type it just as shown, as the leading equal sign is what tells Excel that the following characters are a formula. =H1-F1 Vaya con Dios, Chuck, CABGx3 "vadarpug" <vadarpug@discussions.microsoft.com> wrote in message news:617768AC-D261-47AE-861A-E6BFD...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Formula for a fill color help
I can't figure out how to make a formula that will recognize a color a a value. More specifically; If b1 is made to be green (color index 4) would like c1 to insert the letter Y. I am I'm looking for an I statement so I can use it throughout the whole workbook. The formula if I understand it should kind of look like (in C1): =If(b1=colorindex4,"Y","") I'm looking to put a "Y" in c1 if b1 is colored in green. I would really like to also know how to insert a fill color in an formula! Thank you for any of your help and time, Brya -- Bryan J Yo...

IF Formula 05-17-10
I'm needing a formula that will do the following... If cell A1 is 1 it shows with the word Developing. If A1 is 2 it shows with the word Performing. If A1 is 3 it shows with the word Leading. Thanks you could put this formula in b1 =CHOOSE(A1,"Developing","Performing","Leading") -- Gary Keramidas Excel 2003 "Redroc" <Redroc@discussions.microsoft.com> wrote in message news:7C464B73-6438-4BD7-8002-DC494926E073@microsoft.com... > I'm needing a formula that will do the following... > > If cell A1 is 1 i...

Paste a range of cells in the body of an email
I have been trying to get the code to work for copying a range of cells and pasting it into the body of an email I am using excel 2007 and Lotus Notes 7.0.2 My current code works kinda sorta. It will copy and paste the text into the body of an email. But it does not open a new email it replys to a email that is in my in box. It also sends an email but it is a blank email. I would do just an attachment but the department I need to send the email to says they can not open the attachment........... Anyone have code that can do this? thanks Marie All you ever need to know ...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Formula for computing work time in Excel
Hi, For instance, A1 = 8:20, B1 = 16:30. I need to compute in C1 the following: B1 - A1 - 0.5 hour. What is the formula? If I use formula B1 - A1 - 0:30 I get invalid value. Thanks, -- Alex Vinokur email: alex DOT vinokur AT gmail DOT com http://mathforum.org/library/view/10978.html http://sourceforge.net/users/alexvn =B1-A1-TIME(0,30,0) -- HTH Bob Phillips "Alex Vinokur" <alexvn@big-foot.com> wrote in message news:uGyW$ZgvFHA.2008@TK2MSFTNGP10.phx.gbl... > Hi, > > For instance, A1 = 8:20, B1 = 16:30. > I need to compute in C1 the fo...

Formula won't copy down the column
I am trying to copy a formula down a column. I have done it for years and worked fine. This time, the first result appears in each cell, but if you look at the formula in copied cells, they show the correct formula has changed as I dragged it down the column. So, the formula changes as I drag, but the answer stays the same as the original cell. The data was sent to me by someone else and I am working with it. I have even copied just the delivered data to a new spreadsheet and the same result. Please help It sounds like Calculation in Excel is set to Manual. Try this: From the Ex...