SERVING THE QUANTITATIVE FINANCE COMMUNITY

• 1
• 4
• 5
• 6
• 7
• 8

Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

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)

bearish
Posts: 5425
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

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.

tagoma
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

I have just noticed the following.CTRL+C to copy a range. And RETURN to paste (Easier than CTRL+V).Sometimes, simple things ....

bluetrin
Posts: 292
Joined: September 9th, 2005, 6:41 am

### Excel tricks

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.

tagoma
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

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. tagoma Posts: 18351 Joined: February 21st, 2010, 12:58 pm ### Excel tricks QuoteOriginally posted by: outrunlike this?=INDIRECT("OtherSheet!A1")thank you for replying outrun. bearish Posts: 5425 Joined: February 3rd, 2011, 2:19 pm ### Excel tricks 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.

tagoma
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

I just read about the built-in ConvertFormula function.It can add the $sign in several cells at a time. tagoma Posts: 18351 Joined: February 21st, 2010, 12:58 pm ### Excel tricks 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. cemil Posts: 221 Joined: September 16th, 2005, 7:44 am ### Excel tricks 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.

tagoma
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

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"???