• 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.

Problem with Division Calculation when sheet updates every 5 min?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear All Excel Experts,

Good Morning to you all and how are you? Today morning i have been facing a simple division calculation when the sheet is updated every 5 mins from the website. In the attached file the "Sheet 1" is linked to the nseindia.com website were the table will be updates every 5 mins. In "Sheet 2" i want to calculate the Put Call Ratio (PCR) which is nothing but the Total Put OI / Total Call OI. The problem here is when the "Sheet 1" updates every 5 mins the "Total of the OI" changes every time. Once the change happens the resulting PCR will be an error. So everytime i had to keep on changing to the cells to get the correct calculation.

How can this be solved any ideas from the Excel experts here.

Regards,

Sonjoe Joseph.

Working file attached along with this message.
 

Attachments

  • pcr.xlsx
    24.7 KB · Views: 12
vletm said:
SONJOE JOSEPH
Your 'text' per 'text' would work better with next:
=SUBSTITUTE(Sheet1!V124,",","")/SUBSTITUTE(Sheet1!B124,",","")
Click to expand...

Dear Vletm,

Even though i have applied your formula today morning when the sheet was updated i have been facing problems and the PCR was showing no value. Please kindly update the formula so that i can solve the issue.

Regards,

Sonjoe Joseph
 
vletm said:
SONJOE JOSEPH
What kind of problem do You facing?
It's challenge to do ... something if You cannot tell more!
Click to expand...

Dear Vletm,

If you open the file PCR you will see in Sheet 1 OI of both Call and put which are in cells B124 & V124. If the values was coming constant is these same cells its very easy for me to calculate the PCR. The problem i'm facing is that the Sheet 1 is already linked to the nseindia.com website after every updation some times the cells keep on changing and the end result u will get the PCR value as na.

How can we solve this issue.

Regards,

Sonjoe Joseph
 
SONJOE JOSEPH
Sheet1 cells B124 & V124 values are in TEXT-format
PCR='text'/'text' do not work!
'My solution' tries to change TEXT to numbers.
Here it works...
 
vletm said:
SONJOE JOSEPH
Sheet1 cells B124 & V124 values are in TEXT-format
PCR='text'/'text' do not work!
'My solution' tries to change TEXT to numbers.
Here it works...
Click to expand...

Well today i had a problem in the morning....were i got no values for PCR. Anyway if the problem arrives tomorrow..i will take a screen shot and will send it to you.

Regards,

Sonjoe Joseph
 
SONJOE JOSEPH
Note1)
Did or not get idea of that 'text' challenge?
If not then test Yourself to add formulas as below.

Screen Shot 2017-05-31 at 10.28.59.png
Screen Shot 2017-05-31 at 10.28.26.png
Note2)
Is B124's real value ~34000 or ~34000000?
Here, if there are two "," in value,
it makes that challenge!
>> If possible to ask to 'update/get' values WITHOUT thousand any separator
and as numbers ... NOT TEXTs!

Note3) As well values as I123 '0.10' makes same kind of challenge; here.
All of those are also 'texts'... not useful for further use!

>>> My opinion is that any Screenshot do not help, but at least something... <<<
 
Hi, SONJOE JOSEPH!

Give a try to this formula, just in case the update changes the number of rows:
=INDIRECTO("Sheet1!V"&COINCIDIR("Total";Sheet1!A:A;0))/INDIRECTO("Sheet1!B"&COINCIDIR("Total";Sheet1!A:A;0)) -----> in English: =INDIRECT("Sheet1!V"&MATCH("Total",Sheet1!A:A,0))/INDIRECT("Sheet1!B"&MATCH("Total",Sheet1!A:A,0))

In any case check the regional settings of your computer to be Decimal Point is Point and Digit Separator is Comma.

Regards!
 
SirJB7 said:
Hi, SONJOE JOSEPH!

Give a try to this formula, just in case the update changes the number of rows:
=INDIRECTO("Sheet1!V"&COINCIDIR("Total";Sheet1!A:A;0))/INDIRECTO("Sheet1!B"&COINCIDIR("Total";Sheet1!A:A;0)) -----> in English: =INDIRECT("Sheet1!V"&MATCH("Total",Sheet1!A:A,0))/INDIRECT("Sheet1!B"&MATCH("Total",Sheet1!A:A,0))

In any case check the regional settings of your computer to be Decimal Point is Point and Digit Separator is Comma.

Regards!
Click to expand...

Dear SirJB7,

I'm using Excel 2007. It doesn't have functions named "INDIRECTO" & "COINCIDIR". But it has a function called "INDIRECT". So what to do just guide me.

Regards,

Sonjoe Joseph
 
vletm said:
SONJOE JOSEPH
Note1)
Did or not get idea of that 'text' challenge?
If not then test Yourself to add formulas as below.

View attachment 42172
View attachment 42171
Note2)
Is B124's real value ~34000 or ~34000000?
Here, if there are two "," in value,
it makes that challenge!
>> If possible to ask to 'update/get' values WITHOUT thousand any separator
and as numbers ... NOT TEXTs!

Note3) As well values as I123 '0.10' makes same kind of challenge; here.
All of those are also 'texts'... not useful for further use!

>>> My opinion is that any Screenshot do not help, but at least something... <<<
Click to expand...

Hai,

Today morning also facing the same error on PCR. It's showing no value. I didn't understand the solution what u have provided.

Regards,

Sonjoe Joseph.
 
SONJOE JOSEPH
As I have tried to tell You many times..
Your those values are NOT NUMBERS!
Those values LOOKS LIKE NUMBERS but act like TEXT.
So far formula TEXT / TEXT cannot solve - never!
My photoshots tried to show that
if You try to add ONE to those cells then NO WORK!
Did You try those?
That my 'substitute'-solution works (here).
Did You try that?
With that, =V124/B124 gives an answer; numbers!
Now, I gotta go, but later
... maybe more
... after You have reread, tested 'my previous answers'.
 
SirJB7 said:
-----> in English: =INDIRECT("Sheet1!V"&MATCH("Total",Sheet1!A:A,0))/INDIRECT("Sheet1!B"&MATCH("Total",Sheet1!A:A,0))
Click to expand...
SONJOE JOSEPH said:
I'm using Excel 2007. It doesn't have functions named "INDIRECTO" & "COINCIDIR". But it has a function called "INDIRECT".
Click to expand...
Hi, SONJOE JOSEPH!
My Excel language is Spanish so I always post formulas in both that language and English translation, just in case of any typo error.
So use the English formula that's preceded by "----> in English:". If formulas obtained at forums or by Google searchs are in English and your Excel language differs you can always enter it as follows:
a) select the related cell as normally from Excel
b) press Alt-F11 to display the Visual Basic interfase
c) if not visible press Ctrl-G to display immediate window
d) at immediate window type:
ActiveCell.Formula="<formula here, starting by = sign>"
e) check it works at selected cell
Regards!
 
You must log in or register to reply here.
Back
Top