WakeWorld

WakeWorld (http://www.wakeworld.com/forum/index.php)
-   Non-Wakeboarding Discussion (http://www.wakeworld.com/forum/forumdisplay.php?f=4387)
-   -   More Excel Help...If...Then... (http://www.wakeworld.com/forum/showthread.php?t=791666)

depswa 01-30-2012 4:04 PM

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

somebodyelse5 01-30-2012 4:09 PM

I may be able to help you in matlab.......no good at excel though.

kdr 01-30-2012 8:00 PM

=if(and(A1=X, B1=X), Value1, IF(and(A1=Y, B1=X), Value2, IF(and(A1=X, B1=Y), Value3, value4))))

ralph 01-30-2012 8:07 PM

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))))

ralph 01-30-2012 8:08 PM

Dang beaten to the punch

depswa 01-31-2012 12:44 PM

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?

mastercraf 01-31-2012 1:20 PM

Use the INDIRECT function. That's it's purpose

ralph 01-31-2012 2:08 PM

Quote:

Originally Posted by depswa (Post 1729104)
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

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.

mastercraf 01-31-2012 8:40 PM

http://www.cpearson.com/excel/indirect.htm

xxxlair 02-01-2012 7:31 AM

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.

jeff_mn 02-01-2012 10:54 AM

Quote:

Originally Posted by xxxlair (Post 1729205)
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

Quote:

Originally Posted by xxxlair (Post 1729205)
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.

ottog1979 02-01-2012 11:45 AM

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...

xxxlair 02-01-2012 7:27 PM

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.

ottog1979 02-03-2012 12:26 PM

xxxlair - sent you a PM.


All times are GMT -7. The time now is 4:24 PM.