More Excel Help...If...Then...
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 :banghead: =IF(AND(A1=1, B1="A"), Value 1, ??????????? Am I even close? LOL |
I may be able to help you in matlab.......no good at excel though.
|
=if(and(A1=X, B1=X), Value1, IF(and(A1=Y, B1=X), Value2, IF(and(A1=X, B1=Y), Value3, value4))))
|
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)))) |
Dang beaten to the punch
|
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? |
Use the INDIRECT function. That's it's purpose
|
Quote:
|
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. |
|
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.
|
Quote:
|
Quote:
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. |
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... |
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. |
xxxlair - sent you a PM.
|
All times are GMT -7. The time now is 4:24 PM. |