Writing complicated If statement in Excel
I am trying to write the following in excel, I know how to do it in VB
but was told that you can also write it in excel and I would like t
know how, would appreciate all your help. It is an option pricin
model and all I want to say is:
For K and F, if K=F then return "atm"
If K<F then return as follows: (K-F)/F and "itm" (% that option is i
If K>F then return as follows: (F-K)/F and "otm" (% that option is ou
of the money)
Message posted from http://www.ExcelForum.com
=IF(K1=F1,"atm",IF(K1<F1),TEX...can you tell me the difference between excel 2000 and excel 2003
can you tell me the difference between excel 2000 and excel 2003
Search the Microsoft Knowledge base for new features in XP and in 2003
for more complete information.
The biggest difference that I have noticed on a daily basis is auto
recovery. If there is a crash, Excel XP and 2003 will attempt to allow
you to recover the workbook that you were working on.
If you do probability, statistics, or simulations, then many algorithms
are greatly improved.
You have more flexibility in what you protect on a worksheet (for
instance, you can protect formulas...If then Statements in Excel
I want to create a formula that evaluates an adjacent cell for a yes
condition, if that condition is met, I want to sum to other cells, any
"Kyle" <Kyle@discussions.microsoft.com> wrote:
> I want to create a formula that evaluates an adjacent cell for a yes
> condition, if that condition is met, I want to sum to other cells, any
You are more likely to get useful responses if you provide more specifics
(cell names; formulas that you tried, but they do not work for you; etc).
Otherwise, the useful of a response is the ...difference between Excel 2000 and Excel XP, and Excel 2002
I would like to know what the differences are between Excel 2000 and Excel XP, and Excel 2002
Go to John Walkenbach's site www.j-wal.com
Lots of info there.
remove CAPS in e-mail address
"Eleanor" <firstname.lastname@example.org> wrote in message
> I would like to know what the differences are between Excel 2000 and Excel
XP, and Excel 2002
This is a multi-part message in MIME format.
Content-Type: multipart/alt...Return Excel IF statement to a different worksheet?
How do i return to results of an Excel IF statement to a different worksheet
You can only return results into the cell in which the formula is. In order
to do what you want, you'll need to put the IF formula into the cell in
which you want to return the result.
"addme99" <email@example.com> wrote in message
> How do i return to results of an Excel IF statement to a different
...Beautifying Excel #2
I have created an application which outputs it's information into
Excel so the client can manipulate the information easily. However,
they've moved the goalposts a bit and now want it to 'look cool'.
I usually work with websites so that usually wouldn't be a problem.
However, could anyone point me in the direction of any resources on
making Excel ... erm ... sexy! I appreciate that I can probably change
the fonts and colours, maybe include graphs and images here and there,
but I wondered if there were any galleries anywhere of good examples.
Specifically he wants an interf...How to view 2 Excel worksheets from different workbooks same time
I have two monitors. I would like to open and view 2 different worksheets
from 2 different workbooks at the same time. Is this possible with Excel?
I would open excel (a single instance).
Then use File|open to open both workbooks.
Then I'd look at Chip Pearson's notes:
> I have two monitors. I would like to open and view 2 different worksheets
> from 2 different workbooks at the same time. Is this possible with Excel?
i have 3 monitors an i usually open 3 instances of e...SUMIF function?
I'm tring to get excel to do a conditional sum based on the following.
I have two columns, the first containing a reference number, and th
second an ammount which is either positive or negative.
In another sheet I have the reference number and beside it i want
cell to calculte the sum of all the sells with that ref number and tha
contain a positive amount, see the example below.
Ref # Ammount
SO then on another sheet i have
Ref Positive Total Negativ...Nested If Function to be converted into a user-defined custom function
below formula (nested if formula) WORKS FINE in EXCEL 2007, but it is
not working in 2003 for known reasons (number of conditions exceeding
the limit) .
I now would like to create a user-defined custom function (UDF) in
I know how to operate the VBA Editor but I got no idea how this nested
If-Formula translates into a VBA-code.
Could somebody please help me. Help is much appreciated. Thank you
very much in advance.
Below formula (nested IF-Function...Nested If Functions #5
cefpe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1357
View this thread: http://www.excelforum.com/showthread.php?threadid=26722
...Need help on IF function........
Hi guys. I have a survey which users must answer Y/N (5 questions in
cell L8 to L12).
- If all 5 are "N", cell L15 will show "Not tested".
- If 1 question or more are "Y", cell L15 will show "tested".
- If any questions are left blank, cell L15 will show "survey not
- Even 1 question or more are "Y", but survey is not completed, cell
L15 will still show "survey not completed".
How can this be achieved? Can u guys enlighten? Also, i'm a total
newbie in vba and excel functions. Where would be a good place to...Excel not warning if another user has the workbook open
Normally Excel will warn users on my network if another user has already
opened the workbook that they are trying to open.
There are two or three workbooks that Excel stopped warning the user's about
other users having them opened.
I have checked "Read Only Recommended" under Tools -> Options -> Security
Tab, which has solved the problem, ie users
once again receive the warning if someone else has already opened the file
however they are now also warned when opening the
file that they should preferably open the file as Read Only.
How do I configure these wo..."cannot shift objects off sheet" Excel message
Hi-first off if you write please only use one 't' on net
in my email address...(flash.net)
I am trying to hide some calculation cells, and all unused
rows below the cells, in a spreadsheet, but setting the
height to zero....when I select the rows to hide (e.g. 100-
65536) I get the message in the subject box. When I do
this in 'chunks', my 100kb file gets turned into a 2 MB
I have to hide cells this way since this is a Crystal Ball
any ideas appreciated
JB in TX
On Tue, 19 Aug 2003 14:13:54 -0700, "JB in Texas"
<jbrown6@flash...Excel 2003 formats different that excel 2002
Row heights in a worksheet created and used in Excel 2002 are not the same
now that I have opened the worksheet in a new install of Excel 2003. Why has
this happened and what can I do to default back to the excel 2002 setup
saving me the time of reformatting all of my row heights again in several
sheets in numerous files?
...How do I modify a database in Excel 2007?
I can't find a way to add to the range of cells once I've created the
database. Can't find a way to delete the database without deleting the
contents of all the cells in it.
When you say "delete the database", what do you mean other than "deleting
the contents of all the cells in it"? Perhaps I don't understand your
meaning of the word "database". Or maybe it's the word "delete". When you
say "I can't find a way to add to the range of cells once I've created the
database", exactly what do you want to...hyperlink an excel cell to a specific location wthin application f
how can I hyperlink an excel cell to a specific outlook contact?
I don't believe this is possible.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"dirtboy" <firstname.lastname@example.org> wrote in message
> how can I hyperlink an excel cell to a specific outlook
...Excel If Statements
I am trying to create a conditional statement with 17 IFs. I started needing
to do 11 Ifs. I got over the Excel limit of 7 by identifying a cell in the
last element of the first string of 4 IFs in which there was a further string
with 7 IFs. However I now need to expand this to 17 IFs. I tried the same
technique of creating a third string with 6 more IFs and identifying this in
the last condition of the second string. It didn't work.
Microsoft MVP - Excel
"Peter" <P...DATEDIF Function Should Be More Accesible
The DATEDIF function should be accessible through the Paste Function button.
It isn't listed in the Date &Time list or even under ALL. It's a very
usefull function, why would they hide it? There is help, but you have to
learn it exists from another source first.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader...What are the differences between Excel 2000 and Excel 2002
If you have Excel 2002, then from Help you can find What's New.
Rob van Gelder - http://www.vangelder.co.nz/excel
"ceroork" <email@example.com> wrote in message
Microsoft MVP - Excel
"ceroork" <firstname.lastname@example.org> wrote in message
...can i tell if an excel file on my PC has been "tempered" with?
I am wondering if there is a smarter way to tell whether a
specific file(e.g. Excel, Word or email) has
been "tempered" with. The only thing I know is to check
the File Properties to see when it was last modified, but
then I realize by changing the computer clock, one can
virtully set those date fields to be any time/date you
Is there another way to tell whether the file was indeed
the one saved/modifed on the date it says Or by the person
it says was the author?
A related question goes to how to tell whether the
attachment of email has been revised and r...Linking Excel columns in two different excel programs
I have a few excel programs that require the same column of information. I
would like to be able to somehow link the column in the different programs so
when I add data to the column in one program, excel automatically adds it to
the column in the other programs. When I say program, I mean spreadsheets but
these spreadsheets are apart of different excel documents or projects. Is it
possible to link the columns? Also, is it possible to have about 6 rows in
one spreadsheet to automatically display in another sheet without cut and
paste? Any help would be great.
Launch He...Excel If Function #2
Can you let me know how I write an IF formula in an
excel that will return a TRUE value in the cell D2 if the
cell A2 contains the word Alcohol and cell A2 contains
the word Drugs and cell C3 contains the word anger.
Thanks Nick Read
D2: =AND(COUNTIF(A2,"*Alcohol*"), COUNTIF(A2,"*Drugs*"),
D2: =IF(AND(COUNTIF(A2,"*Alcohol*"), COUNTIF(A2,"*Drugs*"),
In article <email@example.com>,
"Nick Read" <anon...IF function 05-01-10
I am trying to project FY sales based on open orders,shipped orders and
I have pulled together the 3 lots of data and used SumProduct to calculate
totals for each product by FY months (for this i have used Vlookup against
I have ended up with a sheet that looks like below
Columns C-W Row 3 have the Product Names
Column B Row 4 down to 15 have Oct,Nov ending with Sep (this is text)
C4 to W15 have the totals
Row 16 is the totals of each product
Columns C-W Row 19 have the Product Names
Column B Row 20 down ...Nested If statements and lookup function
I have two cells that I need to check before I can lookup
a value. I thought I did it right with nested if's and
then lookup within the 'true' part of the if, but it's not
Here's what I got:
1) Check the first cell (a1) for 4,6,8,10 or 12;
2) Then check the second cell (a2) for
15,20,25,30,35,40,45,50,55,60,65,70,75 or 80;
3) Then based on the second cell's value (a2) go lookup
the appropriate value in a table (b1:c14):
35 520 etc. etc.
I tried this:
(a2,b1:b14,c1c14),i...Need a "GetIF" function
I have 10 separate spreadsheets spread across 10 different locations. They are all structured the same and I need to pull certain data from each one of them. Basically if column A equals "value", then I would like to have the whole row of information returned (to yet another spreadsheet that would be a summary spreadsheet for the info I need). Is there a function that would do this for me?
for a VBA solution see:
"Carpie" <firstname.lastname@example.org> schrieb im Newsbeitra...