Nesting an If factor and using the range name

I am taking a course for advanced excel and I need help with a
question>
I am working on a spread sheet for real estate.  I am supposed to set
up a formula using NESector and CornerLot classifying them as shown
below.
1, 2 or 3.
Can any one help?????
NESector    CornerLot        Classification
Yes             Yes                      1
Yes              No                       2
No                No                       3

0
pdressel (4)
3/12/2006 9:31:56 PM
excel 39879 articles. 2 followers. Follow

2 Replies
404 Views

Similar Articles

[PageSpeed] 34

Assuming the NESector is in A1 and the first row of "Yes"s is A2 and B2;
The formula needed in C1 is =IF(A2="Yes", IF(B2="Yes", 1, 2), 3)
But what is needed with NES = No and Corner = Yes ?
=IF(A2="Yes", IF(B2="Yes", 1, 2), IF(B2="No", 3, "What to do?")
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Silly Patty" <pdressel@shaw.ca> wrote in message 
news:1142199116.799063.184680@v46g2000cwv.googlegroups.com...
>I am taking a course for advanced excel and I need help with a
> question>
> I am working on a spread sheet for real estate.  I am supposed to set
> up a formula using NESector and CornerLot classifying them as shown
> below.
> 1, 2 or 3.
> Can any one help?????
> NESector    CornerLot        Classification
> Yes             Yes                      1
> Yes              No                       2
> No                No                       3
> 


0
bliengme5824 (3040)
3/12/2006 10:01:36 PM
One way:

=IF(AND(A2="yes",B2="yes"),1,IF(AND(A2="yes",B2="no"),2,IF(AND(A2="no",B2="no"),3,"error")))

or

=IF(COUNTIF(A2:B2,"yes")=2,1,IF(COUNTIF(A2:B2,"yes")=1,2,3))

Depends on what you want to do if NESector could be "No" and CornerLot could 
be "Yes".  And what do you want to do if the cell values are anything other 
than "Yes" and "No" ?

Regards

Trevor


"Silly Patty" <pdressel@shaw.ca> wrote in message 
news:1142199116.799063.184680@v46g2000cwv.googlegroups.com...
>I am taking a course for advanced excel and I need help with a
> question>
> I am working on a spread sheet for real estate.  I am supposed to set
> up a formula using NESector and CornerLot classifying them as shown
> below.
> 1, 2 or 3.
> Can any one help?????
> NESector    CornerLot        Classification
> Yes             Yes                      1
> Yes              No                       2
> No                No                       3
> 


0
Trevor9259 (673)
3/12/2006 10:09:02 PM
Reply:

Similar Artilces:

Range problem in Pivot table
This is the problem I have with few excel sheets but not with all. When I select a range of cells including the Column headers and go for Data - > Pivot table and Pivot Chart report ... and click on Next , I can see "Database" in the Range field rather than the range (example : $A$1:$B$7). Could someone help. Hi Ravi, You have not mentioned which version of excel you are using. One possiblity might be that the range you are selecting, already has the name defined as "Database". Sometimes excel, itself defines names such as "data" or "database&quo...

Use exprofre.exe with Citrix
I am migrating from exchange 5.5 to exchange 2003 and consolidating sites. For the users that have been moved cross administrative groups I need to run exprofre.exe when they first logon to fix their outlook profiles. This works fine for "fat" users but this doesn't work for "thin" users who work through citrix. If there is already one user working in outlook on the citrix server the exprofre.exe utility will not run because outlook is open... Does anyone of you know a solution to this? Thanks, Stijn In terminal type software issues such as these can be resolved ...

Restoring a mailbox using W2k Backup
Hi, I have accidentally deleted a mailbox from my Exchange 5.5 server(running W2K server) and need restore it from backup. I am trying to follow the instructions in Article 163713. I have installed Exchange Server 5.5 on another Windows 2000 server and have created a new site with the same site and organization name as the original server. I go into Windows 2000 backup and try to restore it but I cannot select the other server to restore to... I can only select the original server. Am I doing somthing wrong??? Can you please get back to me urgently on this? Thanks in advance. Kind Rega...

Match Account ID from combining street names and numbers
In one sheet, have a column with a street number (A): 115 in Column B, have a street name: Northwest. In workbook 2, have an additional street number column(C), and a street name column(D), and a 5th column with an account number, column (E) I need to find the account number in workbook 2 for a combination of street number,and street name found on the given row from the first workbook. There can be many street numbers on the same street, but only the combination of the desired number and called for street should return a value. Desired combinations are always found on the same row ...

copy ranges
Hi, Is there a way (maybe a macro) to copy non adjacent ranges from book1, sheet1 to book2, sheet1? Both books are opened. the ranges are: B14:E64 G14:FE64 FH14:FQ64 Copy and paste one at a time. If you need a macro, record one when you do it manually. Khalil Handal wrote: > > Hi, > Is there a way (maybe a macro) to copy non adjacent ranges from book1, > sheet1 to book2, sheet1? Both books are opened. > > the ranges are: > B14:E64 > G14:FE64 > FH14:FQ64 -- Dave Peterson Thanks Dave, I was thinking there might be another way than doing it one at a time. ...

The ability to set the naming convention for EFT files.
Some banks have limits on the length of the file name that can be submitted for EFT. With the new naming convention the file names are all to long. Can you devise a method for setting the namin converntion. ---------------- 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 and then click "I Agree" in the message pane. http://www....

Locking cels using a UDF
I'm not entirely sure if this is possible or not... but here we go. I'm looking to lock down two cells in a row after data is input into the cells... for the entire spreadsheet... or at least 2000 rows. I think the easiest way would be to have a function since I could fill down and the two cells would change relative to the position of the function. Basically, I'm looking to lock cells A1:B1 and so on all the way down the spreadsheet. I've seen some subroutines to lock cells, but I don't know how to use them in the spreadsheet, and I'm having a heck of a time trying...

"Balance account" used to automatically clear downloaded transactions but stopped doing so
This month when I went to balance my checking account, whose transactions are imported from a download file, the balance window came up with E marked for all downloaded transactions and I had to manually check them all cleared. Previously downloaded/imported transactions up to the closing statement were already cleared by Money. What do I have to change to return to that situation? Also...had an error in the statement opening balance, so restored my previous backup and the balance went o.k. However on line update is stuck in progress. There was a message about having to reset up online se...

Use query results to copy files?
Hi, I have a query that returns results like: Name: ID: Picture ID File Name: Bob 1234 bobs_id_pic.jpg Tina 5678 tinas_id_pic.jpg .... Is there a way to use these results to (automatically) copy all the id_pics to another folder (assuming all ID pics are in the same folder)? Thanks! Yes, but it's not a trivial task. You would have to open a recordset on the query results and then use the file system object to do the copying. If there aren't too many, it will be quicker to do it manually. -Dorian "Gary" wrote: > Hi, I have a qu...

How do I change my business name in Money 2007?
I installed Money 2007 and initially put in a business name that I'm trying to change now. I click on Business, and I go to Account settings and I change the name there. This changes it everywhere except for on the invoices that I print. The template's placeholder for <<company name>> is still putting what I had originally put in there. Where do I change this!? Any information would be greatly appreciated. Thanks! How about in the Invoice Designer that gets installed along with M2007? -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http...

name ranges #2
Hi, Sheet2 has the formula: =VLOOKUP($B$3,Sheet1!$B$2:$D$15,2,FALSE) in cell c8 How can I use a range name (names-1 for example) that refers to: Sheet1 range B2:B15 in a way that i have a small arrow beside cell B3 that show all the names in the range (names-1)? Please give us more detail, this question is not clear. What does VLOOKUP have to do with the range names and what do you mean by the names in the range names-1. And what do you mean by a small arrow which shows the names? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Khalil Handal"...

pasting row to another workbook prompts because same defined name exists in new book
I have a set of weekly spreadsheets, all the same except for the dat that is entered onto them. What we ned to do is cut and paste lines from last weeks workbook int this weeks one. The problem is I have set up data validation on one o the columns based on a list of names, and so now it prompts ever singl time we try to paste into the new workbook, because the same define name 'tradelist' is in the new sheet. is there any way I can stop this prompt somehow because tis driving u mad and taking up time clicking yes or not (when it makes no differenc if we click yes OR no because its e...

Name
Hi, I need to use Name to define 3 discount rates. This needs to be done so that when the user of the spreadsheet enters "discount 1" it gives a value of 10%, "Discount 2" gives a value of 12% and "Discount 3" gives a value of 17.5%. Using Name I have managed to get it so that they enter for example " =Discount_1 " it gives a value of 10% but I want it so that when they enter "Discount 1" they get 10%. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly f...

using countif function to add only a half of a number
I would like to use the countif function to count a letter a a half ".5" instead of as a whole "1." The application that it would be used for is on a payroll workbook. i.e. "P" would eaqul a whole personal day whereas a "HP" would be a half of a personal day. From there I would like to add all of the "P's" and "HP's" in one Cell. this would be like 4.5 days used compared with 4 days used (P,P,P,P, HP)=4.5 Thanks, Brandon One way =SUMPRODUCT(COUNTIF(A1:A100,{"P";"HP"}),{1;0.5}) where your P/HP day...

Connection string, avoiding the use of ODBC
I have an application I deploy that makes a number of internal SQL calls for all sorts of purposes. In order to isolate the connection, I used a global CONST, cnn, that reads: "ODBC;Database=testing;UID=;PWD=;DSN=SQL Server" The problem with this approach is that it requires me to set up a SQL Server DNS on every new machine. Since the hardware guys are there at odd hours, this invariably leaves new machines without a link in the morning. Reading over the excellent wiki article on MDAC suggests that this isn't really the way to do this anyway, and that I should be using ...

Calculate max value in specific range
Hi all, I've got a table with specific date values (start & end dates) like this: 3/23/00 Start 3/26/00 End etc. note: the length of days will vary from time tot time. -- I also have a table with all date values and corresponding numbers, like this: Column: A B Row: 1 3/23/00 1101.16 2 3/24/00 1106.16 3 3/25/00 1143.07 (=MAX) 4 3/26/00 1137.54 -- How can I calculate the max value from all those corresponding numbers between a Start- & End date? (1143.07 in this case) I've already tried the Vlookup and Max function but that only refers to 1 value (as...

using current directory in the file path
I need a macro to import data from an Access database into a spreadsheet. The spreadsheet and database will be in the same directory but the name of the directory will be different for each use. I have recorded a macro which does this but the name of the directory is fixed. I need to have the macro look in the current directory. A snippet of the maco is : With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=c:\transfer\W5.mdb;DefaultDir=c:\transfer;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _ ), Arra...

Remove the opportunity name field from form
I try to remove the opportunity name field from the opporunity form. I have set the attribute properties to 'no constraint', but I still get a lock Icon before the field on the form. Any ideas what might be locking this (it will be something standard, because we never used this field) and what we can do about it? Regards, Freek The name field is the Primary Attribute of the opportunity and therefore it is locked. You can see that when you open the Opportunity in the customizations area and view the second tab "Primary Attribute". I'm not sure but I don't thi...

Multiple domain name and multiple SMTP virtual server
Presently, my Exchange 2003 server holds mailboxes of four internet domain names. I have only one default smtp virtual server. Everything works fine. I have set "Send a copy of NDR" to a local mailbox. Therefore all non-existing user's mails of ALL domain reaches to this single mailbox. Is it possible to designate a separate mailbox for each domain? I found an article in Microsoft site, explaining to create separate smtp virtual server for each domain. Should I have to create smtp virtual server only for this purpose? Is there any workaround? TIA J Justin In order to creat...

What can i use to read the transaction log files?
If I open the e00XXXXX.log files using notepad they are encrypted is some way. Is there a tool I can use to be able to read the log file? No, and I would recommend you don't open them in anything, what exactly are you looking for? -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "llewell" <llewell@discussions.microsoft.com> wrote in message news:E75FC280-0F6D-4183-977C-1D6157E5C27F@microsoft.com... > If I open the e00XXXXX.log files using notepad they are encrypted is some > way. Is there a tool I can use to be able to read ...

Chart Axis naming question
When you set up a chart in Excel, you can format the axis' size, decimal point, etc. Is there anyway that you can add text into the axis title instead of it being just the numerical value? So, for instance, if my line chart has the Y axis with a scale of 1, 2, 3, 4, 5, is there anyway I can format the access to say 1 - Expert, 2 - Advanced, etc??? i know that I can move the chart into Powerpoint and put a text box down the axis, but I was hoping there may be a way do just do that in Excel. Thanks!! Hi, Have a look at Jon's page on creating arbitrary axis scale. http://pelti...

how to copy&paste range with formulas which are without frozen cells ($) ?
hi, I've created a monster - huge reporting file, it takes lot of inpu data and play with it in many ways in many workseets to create m monthly 16page Production control and logistics report(6 divisions european curriences, fx rates, delivery performance, and lot more) a its very big I can coordinate cell links anymore. My problem is: i have range with formulas, and result of those formula im useing in God knows how many other formulas etc. I want to chang those cells slightly so everything will suck new results, but i want t keep also old calculation somewhere(next to it) if: -I copy a...

Using a query as base for report
Hello, I have created a table where I run a query picking the columns I want in the query. From the query, I create a report. My question is this: Whenever I open the report, I want it to show only the open items. Example: My query has 10 columns, when ALL the fields are populated, the record is complete and does not need to be in the report (i.e. open items report). If the 10 fields are NOT all completed, it's still considered opened and will be on the report when it is run. I think I'm thinking too much about it and making it sound harder to myself. Can you please...

Using Intelligent Message Filtering
How can I set up some domains or emails that must not to block by IMF? SCL rating => 8. For example, I need receive all messages from newsletter@somedomain.com. I'll try to set Global Settings-Message Delivery-Connection Filtering-Exception add newsletter@news.somedomain.com (or *@*.somedomain.com ), but this has no effect: messages from newsletter@somedomain.com move to Archive folder anyway. Additionally, Connection Filtering used (masks 127.0.0.3, ...5, ...7, ...9). Plz, help me! I'm really need this feature. ...

INSERT INTO Table, values derived in code from separating a name
Hi, I have a field called [Contact Name] which is the result of a combo box. On not in list the user is asked if the name they enter is too be added to the list. The name is separated into two strings "str1l" and "str2" in code form a module as the function is called up from the not in List on the Combo box. str1 is FIRST_NAME str2 is LAST_NAME The fields in tblCUSTCONTACTS are "FIRST_NAME" and "LAST_NAME" Now I wish to INSERT into the tblCUSTCONTACTS the two strings. strsql = "INSERT INTO [tblCUSTCONTACTS] " _ ...