|
Join Date: Nov 2000
01-30-2012, 4:04 PM
|
Reply
|
OK..I'm trying to put together a spreadsheet that uses an "if then" statement, but it has been so 'ong, I forget how to do one that can have 4 different outcomes?
So lets say A1 can have a value of "1" or "2", then B1 can be "A" or "B"
I want C1 to reflect a certain value if A1 is "1" and B1 is "A", then a different number for A1 being "2" and B1 being "A", Then A1="1" and B1="B" etc...
So lets say
(A1,B1) = "1,A", "1,B", "2,A", or "2,B"
1,A=Value 1
1,B=Value 2
2,A=Value 3
2,B=Value 4
This is as far as I got before my brain failed...lol
=IF(AND(A1=1, B1="A"), Value 1, ???????????
Am I even close? LOL
|
Join Date: Sep 2008
01-30-2012, 4:09 PM
|
Reply
|
I may be able to help you in matlab.......no good at excel though.
|
Join Date: Feb 2003
01-30-2012, 8:00 PM
|
Reply
|
=if(and(A1=X, B1=X), Value1, IF(and(A1=Y, B1=X), Value2, IF(and(A1=X, B1=Y), Value3, value4))))
Last edited by kdr; 01-30-2012 at 8:01 PM.
Reason: forgot ")"
|
Join Date: Apr 2002
01-30-2012, 8:07 PM
|
Reply
|
This is a bit clunky but works as you ask:
=IF(AND(A1=1, B1="A"), 1,IF(AND(A1=1, B1="B"), 2,IF(AND(A1=2, B1="A"), 3,IF(AND(A1=2, B1="B"), 4,0))))
|
Join Date: Apr 2002
01-30-2012, 8:08 PM
|
Reply
|
Dang beaten to the punch
|
Join Date: Nov 2000
01-31-2012, 12:44 PM
|
Reply
|
Thanks guys...I figured it out the way Kirk did it, but it defaults to value 4 if neither A1 or B1 have info in the cells...I'm gonna try Darren's, but have the "false" value say something like "Please select a value for A1 and B1")
Oh....another one I am having problems with is doing a List Data Validation from another worksheet, but I want it to do auto complete? I found one that uses a combo box and some Visual Basic code, but it doesn't work in Excel 2007...any ideas?
|
Join Date: Jul 2009
01-31-2012, 1:20 PM
|
Reply
|
Use the INDIRECT function. That's it's purpose
|
Join Date: Apr 2002
01-31-2012, 2:08 PM
|
Reply
|
Quote:
Originally Posted by depswa
Thanks guys...I figured it out the way Kirk did it, but it defaults to value 4 if neither A1 or B1 have info in the cells...I'm gonna try Darren's, but have the "false" value say something like "Please select a value for A1 and B1")
Oh....another one I am having problems with is doing a List Data Validation from another worksheet, but I want it to do auto complete? I found one that uses a combo box and some Visual Basic code, but it doesn't work in Excel 2007...any ideas?
|
=IF(AND(A1=1, B1="A"), 1,IF(AND(A1=1, B1="B"), 2,IF(AND(A1=2, B1="A"), 3,IF(AND(A1=2, B1="B"), 4, "Please select a value for A1 and B1"))))
|
01-31-2012, 3:48 PM
|
Reply
|
I'm curious, what is everyone modeling in excel? I use excel in real estate development analysis. Modeling was one of the major topics for my masters degree in real estate development, we spent an entire year building models.
This guy is considered the excel guru for modeling real estate problems. He teaches at Columbia and sometimes at MIT and ASU. He was our professor at ASU. Joshua Kahr
He has a bunch of examples that you can check out here.
|
Join Date: Jul 2009
01-31-2012, 8:40 PM
|
Reply
|
|
Join Date: Mar 2002
02-01-2012, 7:31 AM
|
Reply
|
Sam- thanks for the link. I work as an analyst for a life insurance company in WI for commercial mortgage loan origination and asset management. Been looking for a way to get better at my excel skills in regards to RE models. Might have to take one of Kahr's courses that he gives in NY.
|
Join Date: Jul 2009
02-01-2012, 10:54 AM
|
Reply
|
Quote:
Originally Posted by xxxlair
Sam- thanks for the link. I work as an analyst for a life insurance company in WI for commercial mortgage loan origination and asset management. Been looking for a way to get better at my excel skills in regards to RE models. Might have to take one of Kahr's courses that he gives in NY.
|
Youtube - Mr. Excel Guy
|
02-01-2012, 11:11 AM
|
Reply
|
Quote:
Originally Posted by xxxlair
Sam- thanks for the link. I work as an analyst for a life insurance company in WI for commercial mortgage loan origination and asset management. Been looking for a way to get better at my excel skills in regards to RE models. Might have to take one of Kahr's courses that he gives in NY.
|
Are you a member of ULI (Urban Land Institute)? Kahr teaches the Advanced Pro-forma Modeling Using Excel class for them. It is a good class.
Advanced Pro-forma Modeling Using Excel
If you are not a member of ULI and are under 30, I would highly recommend joining. The YLG program at ULI is excellent. This is how I was able to combine logging with real estate. The mentors in the program launched my career.
|
Join Date: Apr 2007
02-01-2012, 11:45 AM
|
Reply
|
xxxlair, American Family?
I'm a commercial mortgage broker in San Diego. If it is AF, we speak to you guys semi-often. No deals yet but soon for sure.
Excel is a beautiful thing...
|
Join Date: Mar 2002
02-01-2012, 7:27 PM
|
Reply
|
Sam- sent you a PM. thanks
Andy- no not AmFam. With CUNA Mutual, which is also in Madison. Who are you with? We use PSRS mainly in SD.
|
Join Date: Apr 2007
02-03-2012, 12:26 PM
|
Reply
|
xxxlair - sent you a PM.
|
|