Microsoft Excel Q&A

[derty]

if you want a cell to remain a constant while dragging formulas down rows or across columns you just add $ signs into the row.column number.

i.e. A2 would be $A$2. This locks that variable to the cell A2.

You can also just place one $ sign in there too to allow one of the variables to change with the drag.
i.e. $A2 will force the value to remain in column A but vary down the rows

or A$2 will force the value to remain in row 2 but vary along the columns.

Hopefully that made sense

 

[Aurum]

I think I understand what you mean now. If A1 contained 100 you want A2 to contain 100/1, A3 = 100/2 etc.

If so then A2 should contain =A$1/(ROW(A2)-ROW(A$1))

Mike.

 

[nizar]

From your description the first formula should go in B2 and should be =B$1/$A2. Or, I have misunderstood what you mean.

Mike.

Your a legend!
Thanks Mike.

 

[nizar]

OK one more question, how about If i want to rank cells in ascending order downwards along a column but i want the values along with their names.

Eg.

43.89 BHP
219.90 RIO
2.38 FMG
13.90 MQG
31.90 ANZ

But I want them rank so FMG 2.38 is on top and RIO at the bottom.
The actual list is much larger!

Thanks.

 

[Aurum]

Highlight the area you want sorting including all relevant columns. Goto Data->Sort and follow the obvious dialog.

Mike.

 

[Aurum]

Or, if you are saying that a single cell contains both the price and epic then you can still do it but it's a little more involved. Let me know if that is the case.

Mike.

 

[nizar]

Too easy.
Thanks again.

 

[rub92me]

Thanks. That seems to work, but it means all work to date needs to be reinput into the new format...arrrggg...back to it!

Nick, do you mean that you have an existing collection of scatter graphs saved in the old format that you want to convert to a new format? If so, then you probably want to use some simple automation like a macro that converts the format in your scatter graph from the old to the new to save you some steps in the manual conversion process.

 

[michael_selway]

To all the gun Excel users, what is the best way to learn?

Did you guys do courses or were a couple of books enough?

You learn most of the Excel stuff through work & colleagues

Btw do you guys all know how to use Pivot Tables?

thx

MS

 

[singlefished]

if you want a cell to remain a constant while dragging formulas down rows or across columns you just add $ signs into the row.column number.

i.e. A2 would be $A$2. This locks that variable to the cell A2.

You can also just place one $ sign in there too to allow one of the variables to change with the drag.
i.e. $A2 will force the value to remain in column A but vary down the rows

or A$2 will force the value to remain in row 2 but vary along the columns.

Hopefully that made sense

Just press the "F4" button once you've clicked the cell you're referencing will cycle through the different $ locking scenarios...

ie: if you want to reference cell A1, then start your formula as normal, click the cell you want to reference (A1 for example) then press "F4" button once to lock both row and columns ($A$1), "F4" again to lock the row only (A$1), "F4" again to lock the column only ($A1) and "F4" again to free up the from the locking scenarios (A1)....

You can also click the text A1 within the formula (which shows up in the formula input entry box across the top/full width of the screen) and the "F4" successive presses will work similarly.... beats constantly typing in $$$$$$$$$ signs

Cheers....

 

[Aurum]

A question, how many posters would be interested in a macro that returned share prices from the internet. I'm thinking that you could type =GetPrice("BHP","Bid") and it would return the bid price. It would be 20 minutes delayed but for most people this wouldn't be a problem. I currently have a macro that does this but it is customised to my requirements. I could rewrite this to make it more user friendly if people would find it useful. Let me know.

Mike.

 

[nizar]

OK another question.
I'll try to explain exactly what Im trying to do.

Ive got the full NASQAD list which is about 3200 tickers on one worksheet with the symbol, stock name and market cap.

On another worksheet I have the list of the NASDAQ financials, and another NASDAQ biotechs and another NASDAQ industrials, etc, etc, for all the different sectors.

Now all these worksheets have on them is stock name and symbol. I need the market caps from the original worksheet with the whole NASDAQ.

So, Im thinking, since its easy to reference to other sheets, I can copy this full list into Sheet 2 of each of the sector worksheets.

Actually, hold on, i'll just do that. I'll make one worksheet with everything, the full list and then a sheet for the each of the sectors.

Now, how would I tell excel, for example, for stock AAA, to look at the next worksheet in the cell directly adjacent to AAA and just put that value in ??

Thanks guys, you've been a great help.

 

[jackson8]

OK another question.
I'll try to explain exactly what Im trying to do.

Ive got the full NASQAD list which is about 3200 tickers on one worksheet with the symbol, stock name and market cap.

On another worksheet I have the list of the NASDAQ financials, and another NASDAQ biotechs and another NASDAQ industrials, etc, etc, for all the different sectors.

Now all these worksheets have on them is stock name and symbol. I need the market caps from the original worksheet with the whole NASDAQ.

So, Im thinking, since its easy to reference to other sheets, I can copy this full list into Sheet 2 of each of the sector worksheets.

Actually, hold on, i'll just do that. I'll make one worksheet with everything, the full list and then a sheet for the each of the sectors.

Now, how would I tell excel, for example, for stock AAA, to look at the next worksheet in the cell directly adjacent to AAA and just put that value in ??

Thanks guys, you've been a great help.

try copying the cell that you want to duplicate
then paste special into other cell and paste link

 

[michael_selway]

OK another question.
I'll try to explain exactly what Im trying to do.

Ive got the full NASQAD list which is about 3200 tickers on one worksheet with the symbol, stock name and market cap.

On another worksheet I have the list of the NASDAQ financials, and another NASDAQ biotechs and another NASDAQ industrials, etc, etc, for all the different sectors.

Now all these worksheets have on them is stock name and symbol. I need the market caps from the original worksheet with the whole NASDAQ.

So, Im thinking, since its easy to reference to other sheets, I can copy this full list into Sheet 2 of each of the sector worksheets.

Actually, hold on, i'll just do that. I'll make one worksheet with everything, the full list and then a sheet for the each of the sectors.

Now, how would I tell excel, for example, for stock AAA, to look at the next worksheet in the cell directly adjacent to AAA and just put that value in ??

Thanks guys, you've been a great help.

=vlookup (cell stock AAA is in, table range in next worksheet, column no. in table, false)

That should work, love the vlookup's and the if statements!

Thanks

MS

 

[nizar]

MS and others please see attached.

 

[michael_selway]

MS and others please see attached.

Hi Nizar, yep should use VLOOKUP, an if you see below column C in sheet "Biotechs" the yellow ones are working

However it appears you havent updated the full list in work sheet "Full Nasdaq List", thus some of them are coming up as "N/A"

Let us know any questions

thx

MS

 

[nizar]

MS,

Thanks for your help.
But im still not getting it.

In the formula, what does A6 mean?
Why C284?

And what does the 3 refer to?

I will email you the full thing.
Thanks.

 

[michael_selway]

MS,

Thanks for your help.
But im still not getting it.

=vlookup (cell you want to look up, table range in next worksheet, column no. in table, false)
=VLOOKUP(B6,'Full Nasdaq list'!A6:C284,3,FALSE)

In the formula, what does A6 mean?
Why C284? 'Full Nasdaq list'!A6:C284 is the table range on the next sheet that has all the information

And what does the 3 refer to? Coumn Three in the Table above

I will email you the full thing.
Thanks.

Hi Nizar, please see my response above in bold

Also emailed completed file, bascially copied all the same forumla in the yellow cells etc

Let us know if this is what you want it to look like ultimately

Thanks

MS

 

[Uncle Festivus]

A question, how many posters would be interested in a macro that returned share prices from the internet. I'm thinking that you could type =GetPrice("BHP","Bid") and it would return the bid price. It would be 20 minutes delayed but for most people this wouldn't be a problem. I currently have a macro that does this but it is customised to my requirements. I could rewrite this to make it more user friendly if people would find it useful. Let me know.

Mike.

A bit off topic, but I started out trying to do this too but then found Google spreadsheets, which can do the same, although pretty basic.

=GoogleFinance("asx:bhp", "price")

 

[MS+Tradesim]

Question:

I have a column of data that contains 1, -1 or 0. A new value is added to the bottom of the list every X seconds/minutes. Say the column populates A1:Axxx

At the moment I am using this to return the value of the lowest populated cell in the list:
=INDEX(Sheet2!A1:A1,MATCH(9.99999999999999E+307,Sheet2!A1:A1))

Say the list is:

1
0
0
-1
0
1
0
-1

Then the formula returns "-1". However, what I would like to do is have the formula return the value only if <> 0, and if = 0 then leave the previous result.

So in the above example say that after the last -1, two more zeros are added, then I want it to keep returning the bottom most value that is not 0.

Alternatively, I would like a formula to copy all the values <> 0 into a new column.

Thanks in advance for any help, and in the meantime I'll keep looking for a solution.