Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 5

post #61 of 527
5/5/09 at 1:51pm
Thread Starter 
Quote:
Originally Posted by Dewey View Post
Wow, I got this to work, and I gawk in amazement. But I cannot get it to work if the data is transposed.

Say the data is 1 column of 10 numbers, cells A3 to A12, and I want the average of the 3 highest numbers. What do I enter for xxxx, below:

{=AVERAGE(LARGE($A3:$A12,ROW(xxxx)))}

A$1:A$3 if I understand correctly.
Reply
Reply

Featured Stories on Styleforum

Follow Styleforum on Facebook for the latest news and our random ramblings
Antonio Liverano, Florentine tailor
The official thrift/discount store bragging thread
post #62 of 527
5/5/09 at 1:54pm
Thread Starter 
Depending on how you want to drag it, you need to watch those dollar signs...
Reply
Reply
post #63 of 527
5/5/09 at 1:57pm
Is there a way to get Excel to continuously update information automatically from an external data source (e.g. latest prices for the day?)
Reply
Reply
post #64 of 527
5/5/09 at 2:03pm
Quote:
Originally Posted by Milhouse View Post
Is there a way to get Excel to continuously update information automatically from an external data source (e.g. latest prices for the day?)
both capiq and bloomberg have plugins for excel
Reply
Reply
post #65 of 527
5/5/09 at 2:18pm
Quote:
Originally Posted by Milhouse View Post
Is there a way to get Excel to continuously update information automatically from an external data source (e.g. latest prices for the day?)

Quote:
Originally Posted by haganah View Post
both capiq and bloomberg have plugins for excel

If you don't have CapitalIQ, Factset or Bloomberg and simply want to use an internet connection to retrieve stock prices, you can use the "smart tag" function of xls. I believe you can also download a "Stock Quotes" add-in

Quote:
Get a stock quote

Make sure smart tags (smart tags: Data recognized and labeled as a particular type. For example, a person's name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.) are turned on.

Click AutoCorrect options on the Tools menu.
Click the Smart Tags tab, and select the Label data with smart tags check box.
Type a recognized U.S. financial symbol (for example, MSFT) in a cell. Type the symbol in capital letters.
Click outside of the cell.
Move your mouse cursor over the purple triangle in the lower-right of the cell, and then click the arrow next to Smart Tag Actions to see a list of options.
Click Insert refreshable stock price.
In the Insert Stock Price dialog box, choose whether to insert the stock price on a new worksheet or in a specific area of your current worksheet.
The data that is returned may fill a large portion of your worksheet.
Reply
Reply
post #66 of 527
5/5/09 at 3:38pm
Thread Starter 
Here's a useful one in case anyone's bored: Say you're using VLOOKUPs to look up different numbers in different columns and you want to sum them, like this: =VLOOKUP(A1,A3:A30,2,FALSE)+VLOOKUP(A1,A3:A30,3,FALSE) Or average them: =AVERAGE(VLOOKUP(A1,A3:A30,2,FALSE),VLOOKUP(A1,A3:A30,3,FALSE)) A neat trick is to use an array formula with one VLOOKUP. The above are equivalent to these, respectively (remember to hold CTRL+SHIFT when you press ENTER to get the outer curly brackets. Do not type them. You do have to type the inner ones): {=SUM(VLOOKUP(A1,A3:A30,{2,3},FALSE)} {=AVERAGE(VLOOKUP(A1,A3:A30,{2,3},FALSE)}
Reply
Reply
post #67 of 527
5/5/09 at 3:39pm
Thread Starter 
Quote:
Originally Posted by gdl203 View Post
If you don't have CapitalIQ, Factset or Bloomberg and simply want to use an internet connection to retrieve stock prices, you can use the "smart tag" function of xls. I believe you can also download a "Stock Quotes" add-in

Very cool. Didn't know about this.
Reply
Reply
post #68 of 527
5/5/09 at 11:54pm
gonna ask what's probably a simple question for some of you..

i'm analyzing children's spontaneous speech and need to delete all lines that aren't the target's speech... each line of speech begins with a tag like *CHI for the child, *MAD for madre-->mother, etc.. any way to do this?
Reply
Reply
post #69 of 527
5/6/09 at 2:21am
Thread Starter 
Quote:
Originally Posted by bach View Post
gonna ask what's probably a simple question for some of you..

i'm analyzing children's spontaneous speech and need to delete all lines that aren't the target's speech... each line of speech begins with a tag like *CHI for the child, *MAD for madre-->mother, etc.. any way to do this?

What do you mean by "target"? If you have a list of tags of lines that you want to keep, and delete the rest of the lines, yes you can do that, but I'm not sure if that helps because I don't quite understand the problem. A screenshot of the data would help.
Reply
Reply
post #70 of 527
5/6/09 at 2:22am
Quote:
Originally Posted by CunningSmeagol View Post
What do you mean by "target"? If you have a list of tags of lines that you want to keep, and delete the rest of the lines, yes you can do that, but I'm not sure if that helps because I don't quite understand the problem. A screenshot of the data would help.

the target utterances, i.e. lines spoken by the child.
Reply
Reply
post #71 of 527
5/6/09 at 4:15am
any easy pivot table lesson ?
Reply
Reply
post #72 of 527
5/6/09 at 9:01am
Quote:
Originally Posted by ibleedwhite View Post
any easy pivot table lesson ?

+1
Reply
Reply
post #73 of 527
5/6/09 at 9:13am
google
Reply
Reply
post #74 of 527
5/6/09 at 11:27am
Thread Starter 
Quote:
Originally Posted by bach View Post
the target utterances, i.e. lines spoken by the child. http://localhostr.com/files/587be3/Picture+1.png
I will get back to you before 1. I have an exam and am currently on the train to it. /iPhone
Reply
Reply
post #75 of 527
5/6/09 at 11:58am
Thread Starter 
Quote:
Originally Posted by bach View Post
the target utterances, i.e. lines spoken by the child.

Okay check it out. Enter the following in any cell and drag down. Make the modifications I describe below to suit your data ranges.

{=OFFSET(B$1,SMALL(IF($A$3:$A$14="target",ROW($A$3:$A$14),1000000),ROW(A1))-1,0)}

B$1 is the top cell of the column that holds the child's speech. By that I mean row one of the worksheet, not your data range. Change the letter if your data is in a different column.

$A$3:$A$14 is the range of the tags. "target" is the tag you want to look at. You'd probably replace "target" with "*CHI:" Make sure you include the quotation marks because this tells excel you're specifying a text string and not an actual command of any kind.

1000000 is just a large number that is easy to remember.

Keep ROW(A1) and -1 and 0 as is.

Enter with Ctrl+Shift+Enter to get the curly brackets. Do not type them.

Always cross check to make sure the formula worked. Use evaluate formula in the formula auditing menu to see how it works.

Let me know if you get it to work or not.
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat

Featured Stories on Styleforum

Follow Styleforum on Facebook for the latest news and our random ramblings
Antonio Liverano, Florentine tailor
The official thrift/discount store bragging thread
Styleforum › Forums › General › General Chat › The Excel Questions Thread