• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get rid off some symbols permanently in my excel sheet?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear all Excel Experts,

I have an Excel sheet which is connected to the NSE webpage. The issue i'm facing is i just wanna get rid off of the dashes "-" permanently and instead i want zero "0" as values were ever dashes "-" are coming. Once this is done then i can easily find the average of the Call and Put IV's.

Do help me out in solving the issue. The working file is attached along with this message.

Thanking You,

With Regards,

Sonjoe Joseph.
 

Attachments

  • OC.xlsx
    21.4 KB · Views: 13
Hi SONJOE,

You can find and replace dashes "-" with zero 0 or filter out dashes in your formula only like

=AVERAGEIF(P4: P102,"<>-",P4: P102)

Also, i don't thing you need to remove dashes for calculation, dashes is already filter out in formula without doing anyting


SONJOE JOSEPH said:
Dear all Excel Experts,

I have an Excel sheet which is connected to the NSE webpage. The issue i'm facing is i just wanna get rid off of the dashes "-" permanently and instead i want zero "0" as values were ever dashes "-" are coming. Once this is done then i can easily find the average of the Call and Put IV's.

Do help me out in solving the issue. The working file is attached along with this message.

Thanking You,

With Regards,

Sonjoe Joseph.
Click to expand...
 
Last edited by a moderator:
xlstime said:
Hi SONJOE,

You can find and replace dashes "-" with zero 0 or filter out dashes in your formula only like

=AVERAGEIF(P4:p102,"<>-",P4:p102)

Also, i don't thing you need to remove dashes for calculation, dashes is already filter out in formula without doing anyting
Click to expand...

Dear xlstime,

Well this is working fine. But in practical i can't think of an average IV over the 30 figure. In the working file i have worked out the IV's by using your formula and u will see both the IV's near to the 30 mark figure. Further down i have worked the actual IV of both CE and PE which are near to the 20's. In practical its right. So can we find out the average of IV's whenever the IV's are in continuous down. Just refer the cells Z7 and AA7 for any clarification were u can see the selection i have given.

Hoping for a solution....

Regards,

Sonjoe Joseph.

Attached working file
 

Attachments

  • OC.xlsx
    22.3 KB · Views: 9
If you want the average of IV where IV > 30
=AVERAGEIFS(S4:S102,S4:S102,"<>-",S4:S102,">30")
 
Select anywhere in the Table
Ctrl+A
Ctrl+H
Replace -
With 0
Replace all
 
bosco_yip said:
Hi Joseph,

1] Please refer to the attached file.

2] Formulas are in cell AA1 and AB1 respectively.

Regards
Bosco
Click to expand...

Dear Bosco,


Well this is just cool But Bosco i didn't understand the formula its very lengthy. Can you explain the logic behind the code.

Regards,

Sonjoe Joseph.
 
Hi Joseph,

Let me give you some advice,When the DELTA less than 50,Take the quotes from options call,When the DELTA more than 50 Take the quotes from options put.
There is no such thing as an average, in SKEW.

David
 
bines53 said:
Hi Joseph,

Let me give you some advice,When the DELTA less than 50,Take the quotes from options call,When the DELTA more than 50 Take the quotes from options put.
There is no such thing as an average, in SKEW.

David
Click to expand...

But were is the delta.....just explain???
 
Hi Joseph,

Give me the data,
1.Interest of the period,
2.days until expiration of options
3.What is the current index.
You can add a percentage of dividends ,If it exists.

David
 
SONJOE JOSEPH said:
Dear Bosco,


Well this is just cool But Bosco i didn't understand the formula its very lengthy. Can you explain the logic behind the code.

Regards,

Sonjoe Joseph.
Click to expand...
Hi Joseph,

The "CE IV" formula in AA1 :

{=AVERAGE(INDEX(E4:E102,MATCH(2,1/(E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0))="-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102="-",0)-1))}

1] The red color highlighted part return : 21.29, of which is the cell reference E60

2] The blue color highlighted part return : 18.94, of which is the cell reference E78

3] become-- >> {=AVERAGE(E60:E78)}

4] result -->> 16.56

Regards
Bosco
 
N
bosco_yip said:
Hi Joseph,

The "CE IV" formula in AA1 :

{=AVERAGE(INDEX(E4:E102,MATCH(2,1/(E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0))="-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102="-",0)-1))}

1] The red color highlighted part return : 21.29, of which is the cell reference E60

2] The blue color highlighted part return : 18.94, of which is the cell reference E78

3] become-- >> {=AVERAGE(E60:E78)}

4] result -->> 16.56

Regards
Bosco
Click to expand...

Dear Bosco,

I tried working on the same file with the formula which u given in CE IV in the cell AA4. But when i did the formula is coming itself and no values are appearing. Can u tell me were i went wrong my friend. Still i'm not clear in the formula. I'm not an expert in excel so it would be nice if you can explain the formula each step to the layman user.

Looking forward for your msg. Attaching the working file.

Regards,

Sonjoe Joseph.
 

Attachments

  • OC.xlsx
    22.7 KB · Views: 6
SONJOE JOSEPH said:
N


Dear Bosco,

I tried working on the same file with the formula which u given in CE IV in the cell AA4. But when i did the formula is coming itself and no values are appearing. Can u tell me were i went wrong my friend. Still i'm not clear in the formula. I'm not an expert in excel so it would be nice if you can explain the formula each step to the layman user.

Looking forward for your msg. Attaching the working file.

Regards,

Sonjoe Joseph.
Click to expand...

Hi,

Your formula in AA4 :

=AVERAGE(INDEX(E4:E102,MATCH(2,1/E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)) = "-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102 = "-",0)-1))

The formula missing a " ( " as per highlighted :

=AVERAGE(INDEX(E4:E102,MATCH(2,1/(E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)) = "-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102 = "-",0)-1))

Regards
 
bosco_yip said:
Hi,

Your formula in AA4 :

=AVERAGE(INDEX(E4:E102,MATCH(2,1/E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)) = "-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102 = "-",0)-1))

The formula missing a " ( " as per highlighted :

=AVERAGE(INDEX(E4:E102,MATCH(2,1/(E4:INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)) = "-"))+1):INDEX(INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102,MATCH(TRUE,INDEX(E4:E102,MATCH($Z$1,$L$4:$L$102,0)):E102 = "-",0)-1))

Regards
Click to expand...


Dear Bosco,

Still not working i even put the bracket but still get the result "N/A". Do check it attaching the file.

Regards,

Sonjoe Joseph.
 

Attachments

  • OC.xlsx
    22.2 KB · Views: 4
SONJOE JOSEPH said:
Dear Bosco,

Still not working i even put the bracket but still get the result "N/A". Do check it attaching the file.

Regards,

Sonjoe Joseph.
Click to expand...

Hi Joseph,

Your mentioned formula in cell AA4 is an array formula.

Please follow the following steps to complete the array enter instead of normal enter :

1. Select cell AA4 in which to enter the array formula.

2. Enter the formula : =AVERAGE(INDEX(……))

3. Press Ctrl+Shift+Enter 3 keystrokes together.

Excel will auto insert the formula between { }

and become : {=AVERAGE(INDEX(……))}

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

Regards
Bosco
 
You original asked to replace "dashes" with "zeros", why not use find and replace to accomplish what you asked for.
 
bosco_yip said:
Hi Joseph,

Your mentioned formula in cell AA4 is an array formula.

Please follow the following steps to complete the array enter instead of normal enter :

1. Select cell AA4 in which to enter the array formula.

2. Enter the formula : =AVERAGE(INDEX(……))

3. Press Ctrl+Shift+Enter 3 keystrokes together.

Excel will auto insert the formula between { }

and become : {=AVERAGE(INDEX(……))}

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

Regards
Bosco
Click to expand...
bosco_yip said:
Hi Joseph,

Your mentioned formula in cell AA4 is an array formula.

Please follow the following steps to complete the array enter instead of normal enter :

1. Select cell AA4 in which to enter the array formula.

2. Enter the formula : =AVERAGE(INDEX(……))

3. Press Ctrl+Shift+Enter 3 keystrokes together.

Excel will auto insert the formula between { }

and become : {=AVERAGE(INDEX(……))}

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

Regards
Bosco
Click to expand...

Dear Bosco,

Now its working perfectly fine. But still i didn't understand this formula logic. Anyway thanks for your help.

Regards,

Sonjoe Joseph.
 
bosco_yip said:
Hi Joseph,

Your mentioned formula in cell AA4 is an array formula.

Please follow the following steps to complete the array enter instead of normal enter :

1. Select cell AA4 in which to enter the array formula.

2. Enter the formula : =AVERAGE(INDEX(……))

3. Press Ctrl+Shift+Enter 3 keystrokes together.

Excel will auto insert the formula between { }

and become : {=AVERAGE(INDEX(……))}

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

Regards
Bosco
Click to expand...


Dear Bosco,

Facing some issue with the PE IV the values are not showing while its showing asd DIV. Don't know what the issue. Do check from your side.

Regards,

Sonjoe Joseph
 

Attachments

  • OC.xlsx
    23 KB · Views: 1
bosco_yip said:
Hi Joseph,

Your mentioned formula in cell AA4 is an array formula.

Please follow the following steps to complete the array enter instead of normal enter :

1. Select cell AA4 in which to enter the array formula.

2. Enter the formula : =AVERAGE(INDEX(……))

3. Press Ctrl+Shift+Enter 3 keystrokes together.

Excel will auto insert the formula between { }

and become : {=AVERAGE(INDEX(……))}

Note: Manually typing braces around a formula will not convert it into an array formula — you must press Ctrl+Shift+Enter to create an array formula.

Regards
Bosco
Click to expand...

Dear Bosco,

Just waiting for your reply. Just facing some issues with regard to the PE IV since the values are showing as "DIV". The working file is already attached in my earlier message.

Regards,

Sonjoe Joseph.
 
Hi SONJOE,

You can you IFERROR to handle this error

SONJOE JOSEPH said:
Dear Bosco,

Just waiting for your reply. Just facing some issues with regard to the PE IV since the values are showing as "DIV". The working file is already attached in my earlier message.

Regards,

Sonjoe Joseph.
Click to expand...
 

Attachments

  • OC (3).xlsx
    23.7 KB · Views: 5
SONJOE JOSEPH said:
Dear Bosco,

Just waiting for your reply. Just facing some issues with regard to the PE IV since the values are showing as "DIV". The working file is already attached in my earlier message.

Regards,

Sonjoe Joseph.
Click to expand...

Hi Joseph,

Sorry in unable to give you an early reply, my computer was spoiled and shall going to repairing for a week.

Glad to notice that your problem has solved.

Regards
Bosco
 
You must log in or register to reply here.
Back
Top