Serving the Quantitative Finance Community

  • 1
  • 5
  • 6
  • 7
  • 8
  • 9
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

May 2nd, 2013, 3:13 pm

QuoteOriginally posted by: outrunQuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: outrunI just found out about the BAHTTEXT() function. What's going on?? Who did that?Someone just Thai-ing up loose ends?I was actually looking for this one instead:FUN HATEXT(int n) if n<1 return '' else return 'Ha' + HATEXT(n-1)HATEXT(MAXINT)
 
User avatar
bearish
Posts: 5188
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

May 3rd, 2013, 12:38 am

QuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: outrunI just found out about the BAHTTEXT() function. What's going on?? Who did that?Someone just Thai-ing up loose ends?I have no idea who did it (not to mention why), but pasting some random results into Google Translate suggests that it works right.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

May 7th, 2013, 6:55 pm

I have just noticed the following.CTRL+C to copy a range. And RETURN to paste (Easier than CTRL+V).Sometimes, simple things ....
 
User avatar
bluetrin
Posts: 2
Joined: September 9th, 2005, 6:41 am

Excel tricks

May 8th, 2013, 8:12 am

I do not know many people who know this trick, type in this array formula:{=A1:INDEX(A1:E8,5,3)}It will return A1:C5. Using this, you can get rid of most of the calls to OFFSET in spreadsheet, and because this is not volatile as Excel can determine better the dependencies, this will prevent complicated spreadsheets to go haywire.
Last edited by bluetrin on May 7th, 2013, 10:00 pm, edited 1 time in total.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

July 22nd, 2013, 11:44 am

I lack vocabulary to express my question. But let me try ....I have thousand cells containing (relative?) reference to cells in another sheet.I need to 'block' these references (absolute reference?), that is when I insert a row at the top of the "other" sheet, I wish the content of my cells that point to it doesn't change.When I deal with a single cell, I use the F4 key trick (this adds a $ sign).How can I do the same for several cells at a time?Thank you.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

July 24th, 2013, 5:04 am

QuoteOriginally posted by: outrunlike this?=INDIRECT("OtherSheet!A1")thank you for replying outrun.
 
User avatar
bearish
Posts: 5188
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

July 24th, 2013, 10:10 am

QuoteOriginally posted by: edouardI lack vocabulary to express my question. But let me try ....I have thousand cells containing (relative?) reference to cells in another sheet.I need to 'block' these references (absolute reference?), that is when I insert a row at the top of the "other" sheet, I wish the content of my cells that point to it doesn't change.When I deal with a single cell, I use the F4 key trick (this adds a $ sign).How can I do the same for several cells at a time?Thank you.If the sheets are in the same workbook, Excel should automatically adjust the formulas for you when you insert a row. This works the same way whether the references are absolute or relative.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

July 29th, 2013, 6:27 am

I just read about the built-in ConvertFormula function.It can add the $ sign in several cells at a time.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

November 12th, 2013, 9:28 am

Is there a straight way to dynamically change chart legends?Say may data are in A2:A20.I want the legend to read "My data Sum is [sum]"I made several attempts such as: ="My data Sum is "&sum(A2:A20).I searched a bit on the internet, but the solutions proposed rely on a cell containing (in my case) the sum of the data.I'm not sure what I'm writing is clear. Please, tell me.
 
User avatar
cemil

Excel tricks

November 12th, 2013, 1:45 pm

QuoteOriginally posted by: edouardIs there a straight way to dynamically change chart legends?Say may data are in A2:A20.I want the legend to read "My data Sum is [sum]"I made several attempts such as: ="My data Sum is "&sum(A2:A20).I searched a bit on the internet, but the solutions proposed rely on a cell containing (in my case) the sum of the data.I'm not sure what I'm writing is clear. Please, tell me.Click on your title and write "="link cell adress""For exemple, you can write "=sheet1!$a$1" and on the cell a1 you can write what you want to see on your title.
Last edited by cemil on November 11th, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

January 14th, 2014, 2:22 pm

i have fornight data (2 data for each month) to put in a spreadsheet.are there fancy things Excel is able of when it comes to deal with dates such as "1H Jan-14"???