Excel "IF" functions

Hello,
I am trying to create an "IF" formula to indicate an "X" in a cell if the 
following criteria is met:

10% of entity's Total Assets(C11) and .05% total variance (between two 
periods) and any variances over $500K

Thanks!
0
Utf
2/22/2010 8:09:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
544 Views

Similar Articles

[PageSpeed] 49

An example would have helped a lot.

What do we compare 10% of total assets to?
How do you calculate variance?
Do you meant .05% variance, or 5% variance?
When you say 500K, do you mean 500000?
When you say "and" do you mean *all* criteria must be met, or any one of 
them?

Regards,
Fred


"Katrina" <Katrina@discussions.microsoft.com> wrote in message 
news:E849C382-807A-4544-8A1B-BB186F5DFC2C@microsoft.com...
> Hello,
> I am trying to create an "IF" formula to indicate an "X" in a cell if the
> following criteria is met:
>
> 10% of entity's Total Assets(C11) and .05% total variance (between two
> periods) and any variances over $500K
>
> Thanks! 

0
Fred
2/22/2010 11:45:43 PM
You nee to use an OR function inside the IF

=if(OR(Condition 1, Condition 2, condition 3, condition 4),true,false)


I'm not sure exactly what you want but it would be something like this
with the two number being in cells B11 and C11

=if(OR(abs(C11-B11) > (.1 * C11), B11/C11 < .95, B11/C11 > 1.05 ,
abs(B11-C11) > 500,000,"X","")


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181548

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
2/22/2010 11:50:18 PM
Reply:

Similar Artilces:

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 the money) If K>F then return as follows: (F-K)/F and "otm" (% that option is ou of the money) Thank you. Jenu -- Message posted from http://www.ExcelForum.com Hi try =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 http://www.support.microsoft.com/ 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 sugestions? -- kyle "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 > sugestions? 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. -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Eleanor" <anonymous@discussions.microsoft.com> wrote in message news:CD9A6D01-EA2F-4031-B6C6-99E2E2DD0EE3@microsoft.com... > 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. ------=_NextPart_000_0062_01C3F6CA.F29BA860 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 Hi 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. Andy. "addme99" <addme99@discussions.microsoft.com> wrote in message news:8B30E9F6-22EA-4C49-9075-9EF7FD370122@microsoft.com... > How do i return to results of an Excel IF statement to a different > worksheet ...

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: http://cpearson.com/excel/MultipleMonitors.aspx dotflower wrote: > > 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? -- Dave Peterson 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 1 -5.15 1 6.00 1 -3,50 2 2.20 2 -3.40 2 2.40 3 6.40 3 -7.20 3 -1.80 SO then on another sheet i have Ref Positive Total Negativ...

Nested If Function to be converted into a user-defined custom function
Dear Experts: 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 Excel 2003. 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. Regards, Andreas ------------------------------------------------------------------------------ Below formula (nested IF-Function...

Nested If Functions #5
Thanks again. C -- cefp ----------------------------------------------------------------------- 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 completed" - 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
hi everyone, 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 file.... I have to hide cells this way since this is a Crystal Ball template... any ideas appreciated Thanks 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. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dirtboy" <dirtboy@discussions.microsoft.com> wrote in message news:F33BD88A-D5B0-4C48-8F2B-46BC271D70A8@microsoft.com... > how can I hyperlink an excel cell to a specific outlook > contact? ...

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. Any ideas. Look here: http://www.j-walk.com/ss/excel/usertips/tip080.htm -- Kind regards, Niek Otten 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" <ceroork@discussions.microsoft.com> wrote in message news:226D0DF6-07BA-43BA-8280-325148DABC71@microsoft.com... > See http://www.j-walk.com/ss/excel/index.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "ceroork" <ceroork@discussions.microsoft.com> wrote in message news:226D0DF6-07BA-43BA-8280-325148DABC71@microsoft.com... > ...

can i tell if an excel file on my PC has been "tempered" with?
Dear all, 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 wish. 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
Hi, 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. Thank You 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 One way: D2: =AND(COUNTIF(A2,"*Alcohol*"), COUNTIF(A2,"*Drugs*"), COUNTIF(C3,"*anger*")) or D2: =IF(AND(COUNTIF(A2,"*Alcohol*"), COUNTIF(A2,"*Drugs*"), COUNTIF(C3,"*anger*")),TRUE,"") In article <099001c55180$96dff2d0$a401280a@phx.gbl>, "Nick Read" <anon...

IF function 05-01-10
Hi there, I am trying to project FY sales based on open orders,shipped orders and forecast. 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 original data). I have ended up with a sheet that looks like below Open Orders 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 Shipped Orders 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 working. 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): 15 100 20 190 25 290 30 400 35 520 etc. etc. I tried this: =if(a1=4,lookup(a2,b1:b14,c1c14),if(a1=6,lookup (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? Hi for a VBA solution see: http://www.rondebruin.nl/copy5.htm -- Regards Frank Kabel Frankfurt, Germany "Carpie" <anonymous@discussions.microsoft.com> schrieb im Newsbeitra...