Excel Formula Help

[SouthSwans]

Hey guys and girls, I'm doing a project and need help with excel formulas.

For example if I type '150' into cell A1 I want cell A2 to say 'SouthSwans'. Does anyone know the code for this cause I need to do about a hundred formulas exactly like this.

I don't know how to exactly write it in formula, but if I did it using formulas I know (it doesnt work this way BTW) it would be something like this.

=IF(A1=150,A2=SouthSwans),(A1=151,A2=BigFooty),(A1=152,A2=Sydney) etc etc

Not sure how to make this work so if someone knows I would love help.

Cheers, SS

 

[TheMase]

Hey guys and girls, I'm doing a project and need help with excel formulas.

For example if I type '150' into cell A1 I want cell A2 to say 'SouthSwans'. Does anyone know the code for this cause I need to do about a hundred formulas exactly like this.

I don't know how to exactly write it in formula, but if I did it using formulas I know (it doesnt work this way BTW) it would be something like this.

=IF(A1=150,A2=SouthSwans),(A1=151,A2=BigFooty),(A1=152,A2=Sydney) etc etc

Not sure how to make this work so if someone knows I would love help.

Cheers, SS

=IF(A1=150, "SouthSwans", IF(A1=151, "Bigfooty", "0"))

A1=Number in cell to trigger Value in A2
"SouthSwans" / "Bigfooty" value that will be displayed. Must be in ""
"0" Value that will be displayed if none are correct. This can be displayed as "" for no display.

Must close all opened ( thererfore the amount of open ( must equal the amount of ) at the end of the function.

I hope that makes sense.

 

[TheMase]

=IF(A1=150, "SouthSwans", IF(A1=151, "Bigfooty", "0"))

Adding Sydney.

=IF(A1=150, "SouthSwans", IF(A1=151, "Bigfooty", IF(A1=152, "Sydney", "0")))

 

[spitlizard]

Or replace the last "0" with "" if you don't want anything to show if the conditions are not met.
=IF(A1=150,"SouthSwans",IF(A1=151,"Bigfooty",IF(A1=152,"Sydney", "")))

 

[Simon_Nesbit]

I've always used the IF command too, but seeing as he is talking in #'s in the 150+ range, would a LOOKUP function table be more appropriate? (IF is limited to 5 or so statements isn't it?)

I can't recall how lookup worked now, but I don't think it was that complex.

 

[sniper77]

If you have a fair few variable, I suggest you plant a table of your Numbers and Names at the bottom of your spreadsheet and produce a VLOOKUP statement to automatically populate the name.

ie. Your table could be

ColumnA ColumnB
150 SouthSwans
151 Bigfooty
152 Etc
153 Etc

and then in your data entry area, have 2 fields, one for the number (150) and the next field performs an automatic VLOOKUP. Statement would be

=vlookup(A1,A36000:B37000,2,FALSE)

That will return the Column B data. It will be easier to use if in future you add more reference field, you just add below the last line.

Hope this helps!!

 

[SouthSwans]

Thanks everyone for your help, wouldn'tve been able to do it otherwise. Cheers guys

 

[spitlizard]

If you need more Excel tips, try this site.

http://www.mrexcel.com/

 

[MushroomRocket]

tools -> data analysis -> regression/ anything else will work perfectly.

 

[cyrilfan87]

I also have a question regarding Excel. I figured I may as well use this thread, it seems like you got your answer so I hope you don't mind mate.

I am trying to find the expected return for a portfolio and also the standard deviation of the portfolio. I have found the expected return as it is just the weight of each asset multiplied with its return.

I have also found the standard deviation for individual stocks , just having trouble with the standard deviation for the portfolio as a whole. For example:

Weighting
Coles 33.33%
Woolworth 33.33%
Property 33.33%

Past return
Coles 10%
Woolies 12%
Property 7%

Therefore expected return of this portfolio would be 1/3*15 + 1/3*12 + 1/3*9 = 12%

To find the Sd of the portfolio do I do this:
=((10%-12%)*33.33%) + ((12%-12%)*33.33%) + ((7%-12%)*33.33%)

and then find the square root of that??

Not sure if i'm on the right track or not. Any tips would be great. I have the formula I just can't get my head around it.

 

[cyrilfan87]

Hang on I think I've got it

Would it go - Variance:

=((33.33%*(10%-12%)^2)+((33.33%*((12%-12%)^2)+(33.33%*(7%-12%)^2))

And then the square root of that would give me the standard deviation for the portfolio?

 

[the_fresh]

If you need more Excel tips, try this site.

http://www.mrexcel.com/

Best forum ever for help.

I signed up about 2 years ago, needed help with a formula where I have 2 drop down lists that will find a result ie. Race Track in one, Dist in the other and it finds the result.

I posted it and 5 minute later had a guy working on it, within an hour I had it working.