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

Another Brain Teaser

P

pradhishnair

New Member
Please help me solve these...

the problem i am experiencing is described in detail in the sheet...


https://docs.google.com/open?id=0BwDJ-TLtQDayMGJWV2JrNXA5QWs
 
Please help me with this.. i have got a dead line...


Google doc format:

https://docs.google.com/spreadsheet/ccc?key=0AgDJ-TLtQDaydDZYRzVjdDNLRW5BWlBJbk1nTjg1NVE
 
Hi pradhishnair,


For changing these values:
Code: 
266+000	270+000 to [code]266000	270000 you can use this formula :


[code]=SUBSTITUTE(E21,"+","")
& =SUBSTITUTE(F21,"+","")[/code]

...enter it in G21 & H21 and drag down.


In order to find Quantity in F10 & F11 enter this formula with ctrl+shift+enter & drag down:


=INDEX($D$21:$D$214,MATCH(C10&D10,$G$21:$G$218&$H$21:$H$218,0),0)[/code]


Regarding rest of your requirement please elaborate a little bit more! Please elaborate formula for finding overlapping values, i assume this must be an array formula.


Regards,

Faseeh
 
Pradhishnair,


I can't see any Circular reference errors ?


In Columns G & H I would just wrap the formulas in a Value( ) function

eg:

G21: =VALUE(LEFT(E21,(FIND("+",E21)-1))&RIGHT(E21,(FIND("+",E21)-1)))

H21: =VALUE(LEFT(F21,(FIND("+",F21)-1))&RIGHT(F21,(FIND("+",F21)-1)))


Cell K21 should be array entered

Select K21

Press F2

Press Ctrl Shift Enter

Copy K21

Paste below to the bottom of the data in Column K


Column K will then show you the entry number that overlaps with that row
 
Hi, Pradhishnair!

As Hui said there are no circular reference messages. But I do get a lot of #¡REF! results in column C and #¡VALUE! in column K from second link.


Where does you get the circular ref error? BTW, how do you know you are getting a circular reference without knowing where it is? It's displayed at left bottom of the screen, or a pop-up prompts when modifying a cell.


Regards!
 
Dear Faseeh,


Thnx for ur time and efforts . But the formula did not help me. The results were still treated as texts making the same non-responsive against mathematical function being performed on them. However Hui Sir's formula did gave me the perfect results i desired.

as for elaboration for overlap formula the credit again goes to Hui sir.

he has explained the same in one of the forensics post

here is the link: http://chandoo.org/wp/2012/01/17/formula-forensics-no-009-2/
 
Hi, pradhishnair!

I'm still wondering about circular references, I'm still navigating in circles without any reference.

Regards!
 
Hello SirJB7,


a screenshot of the error message is here http://i50.tinypic.com/2sb93ls.jpg


Although my main question still remains unanswered.


A fresh sample sheet with elaboration of my problem is here. https://docs.google.com/spreadsheet/ccc?key=0AgDJ-TLtQDaydGoyUEdtLWlGZ2RlNXRtYWYxYjJMMUE


Please Help with this problem.. There are ten of thousands of row of data.. And i have a deadline pretty near.. as of now i an doing the math manually.

A help would be greatly appreciated..
 
Hi pradhishnair,


Nice to see you, Well i think you are right about your comments on SUBSTITUTE() function but i think i had something else in my mind.


My Index() Match() formula for fetching rates will still work have u given it a try??


I have tried to work out the file once again and there is a sheet in it besides the one that you are after for fixing the issues "What i meant to say!!!" Have a look at both of them!


PS: The solution in "What i meant to say" has a prerequisite, the data must be entered once again to apply custom formatting, if you have already got that data in sheets, go with Hui's solution, even then formula for rate is working. Just link it to column F & G.


The File:

http://dl.dropbox.com/u/60644346/Copy%20of%20Fresh%20Sample%20Sheet_Chandoo.xlsx


Thanks,

Faseeh
 
Hi Faseeh,


Thnx for the post...


I think i did not make my question clear to you... Yes the index function in column F you suggested is sure giving me results.. But my requirement is to get data in column L

Which is the sum of all those results derived from index function for each frequency of 1000 mtrs.


I will try to explain the methodology, hope this will make things clear.


Step 1: Scan Range F26:G217 hence will be called 'original data'

Step 2: Find lowest value, Round down to the nearest 1000 value

Step 3: Find Highest Value, Round up to the nearest 1000 value.

Step 4: Generate A list of value From lowest 1000 value to highest 1000 value with a frequency of 1000


Step 1 to Step 4 is much simple to do the conventional way, and is the same is populated in columns J & K (J26:K103)


Noe the real Problem: To find values in column L

Step 4: Check if value E26 and F26 is coming under value of J6 and K6. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


Step 5: Check if value E27 and F27 is coming under value of J6 and K6. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


Step 6 Check if value E28 and F28 is coming under value of J6 and K6. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


And so on Till the last values of 'original Data'


Step 7: Add all the stored data, Display in L26


Step 8: Check if value E26 and F26 is coming under value of J7 and K7. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


Step 5: Check if value E27 and F27 is coming under value of J7 and K7. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


Step 6 Check if value E28 and F28 is coming under value of J7 and K67. if yes take corresponding quantity. Equally divide the value for every 1000 mtrs and take corresponding value. Store in memory


And so on Till the last values of 'original Data'


Step 7: Add all the stored data, Display in L27


and so on..


Hope i have made the problem clear.

Plz help with it..
 
Hi pradhishnair,


Few Questions:


All these with reference to you last uploaded sheet:


1. Where will you enter the data?? I mean you will enter data somewhere on the sheet that will be scanned for in the "original data"?? Is it correct.


2. In your entire post you are referring to J6 & K6 but there is nothing in these cells in your sample sheets!! :)


3. In your sheet you have been calculating chain-age between 266-000 to 270-000 this means that you will add up 266-000, 267-000 to 270-000 ??? You will consider only starting point when finding overlap??


It will be easier if you take up an example referring to the book you have uploaded.


Thanks

Faseeh
 
Hi, pradhishnair!


1) Circular reference.


Now with the new uploaded file, I get your damned circular reference. It's in cell H217 with the formula:

=+MIN(IF((((G$217:$G218<=(G217-0,01))*(H$217:$H218>=(F217+0,01)))>0),(A$217:$A218)))

... which references to itself.


Note the difference with previous cells in column H:

=+MIN(IF((((F217:$F$217<=(G216-0,01))*(G217:$G$217>=(F216+0,01)))>0),(A217:$A$217)))


According to this, you should replace H217 by:

=+MIN(IF((((F217:$F$218<=(G217-0,01))*(G218:$G$218>=(F217+0,01)))>0),(A218:$A$218)))

... or eliminate H217 formula, I don't know what does it do, but if it looks for all values of following rows thru end, here you're at the very end, so the same formula isn't applicable.


And adjust all previous range H formulas to this last one. This will avoid the circ ref.


2) Error #¡VALUE! in H column.


Once fixed the circular reference, I don't understand your formula in H column. Can you please explain what do you want to calculate exactly there? Please write down an example with numbers of H26 (first cell in column with that formula).


Regards!
 
Thnx sirJB7,


that solved the circular reference error mystery.

As of the data in Column H. it is not the actual problem is it is a formula to exactly locate the overlapping data suggested by Hui Sir http://chandoo.org/wp/2012/01/17/formula-forensics-no-009-2/. I actually forgot to do the CSE command before uploading it.


But the actual problem is still unattended. Could you please give it some of ur time.


@ Faseeh:


I think you are refereing to the wrong sheet... here is the link for the correct one. https://docs.google.com/spreadsheet/ccc?key=0AgDJ-TLtQDaydGoyUEdtLWlGZ2RlNXRtYWYxYjJMMUE

i have enabled editing for the sheet.


As for your queries please find the answers as follow:

The actual data i received is A22:B217

which then i processed in range C22:H217.

I need to Get the results as in J25:L103.

Now J25:K103 is the lower limit and upper limit of the data in B26:B217 divided equally for every increment of 1000 mtrs starting from the lower limit till up to Upper limit.

To get J25:J103 one is that i can go manually through all the steps that i explained for each interval of 1000 mtrs starting from 266+000 up to 341+000 and calculate the proportional quantity for each 1000 mtrs interval. Or i can formulate the whole process. and get the ans in one click. This is where i need the help of u guys.. Coz processing the whole data kinda becomes difficult since i get tens of thousand of rows of data every week.

Hope the problem is clear to you all.


Please help...
 
Hi, pradhisnair!


The formula for column L is:

=SUMAPRODUCTO(((F$26:F$217)>=$J26)*((G$26:G$217)<$K26)*SI.ERROR((C$26:C$217)/((G$26:G$217-F$26:F$217)/1000);(C$26:C$217))) -----> in english: =SUMPRODUCT(((F$26:F$217)>=$J26)*((G$26:G$217)<$K26)*IFERROR((C$26:C$217)/((G$26:G$217-F$26:F$217)/1000),(C$26:C$217)))


You've made a mistake in the example provided at top of page: the formulas C11:D11 are wrong, so is the calculated value too. Check resulting output with the formula of this post.


Next time, please try to check your examples more carefully, it's harder to check where is the problem and more indeed with such amount of data in such a strange format (000+000). It will help people who might be trying to aid you.


Just advise if any issue.


Regards!
 
Hi, pradhishnair!


I don't understand what did you mean with your last post. I just pointed that it's not an easy workbook at a first glance and if you provide an example it should be correct, otherwise people won't find an easy solution and will have to analyze it in detail... which it isn't the main idea.


The control of the data is beyond the scope of this topic, I think.


Regards!
 
Hello SirJB7,


the formula is not giving me the correct answers.

the uploaded sheet has been modified as per your suggestion.Some mistakes have been corrected too.. I understand that it is always better to keep it simple. Amendments are made and saved.. looking forward for a solution.


Thanks upfront!!
 
Hi, pradhishnair!


Shouldn't you have included Sr. No. 1-2 and 5-10 instead of only 1-2 and 5 for calculating range 266+000 thru 270+000?


Could you please write down here the related calculation for cell L26, if it's wrong your post? If not, please kindly explain me again which are the rows involved in this cell result, as I wouldn't be fully understanding how your model works.


I'll manually check again the first value of L26 cell and get back to you.


Regards!
 
@ SirJB7,


No sir in the whole range F27:G218 only the following are falling between interval 266000 and 267000


1. Sr. No: 1 from 266000 to 270000

2. Sr. No: 2 from 266530 to 271000, and

3. Sr. No: 5 from 266420 to 266420.


rest all are either a value equal to/before 266000 or equal to/after 267000.


To further elaborate:

Sr. No 1 266000 to 267000, the whole 1000 mtrs (i.e 267000 - 266000) is falling under the criteria


In Sr. No 2 266530 to 271000, only 470 mtrs is falling under the criteria (267000-266530)and hence the total quantity for 266530 to 267000 is derived on pro-rata basis


Sr. No 5 is an exception (rare case, but believe me some ppl sent such data to hide the actual situation by not giving clear idea as to from where to where is the quantity coming from by just giving a single entry.)

Here i assume that that the whole quantity is coming under the interval provided the single entry (in this case 266420)is coming under the interval (in this case 266000 to 267000)
 
Hi, pradhisnnair!


Sorry but I don't understand, I'm now more confused.

Interval for row 26 as stated in columns J:K is 266+000-270+000 not 266+000-267+000.

So between 266+000-270+000 I think that rows 1-2 and 5-10 fall into that range.

Am I missing something?


Regards!
 
Oh damn me.. another mistake in the sample sheet...

It should had been 266000 to 267000, 267000 to 268000 and so on..


my bad..

may be the pressure is making me do such blunders.. i apologies.. hope my repeated mistakes wont make you lose interest in the topic..

looking forward for the solution.


Thanks upfront.


NB: the mistake is rectified.
 
Hi, pradhisnair!


Do this:

a) fix the worksheet as needed

b) replace top example for correct for row 26

c) upload it again and post the link


I'm leaving now, but I'll try to see it later, surely for tomorrow.


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