• 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

Hi pradhishnair,


I think i got it!!! :)


Code: 
=SUMPRODUCT(IFERROR((((LEFT(Start,3)=LEFT($J27,3))*(Qtty)*($K27-(Start)))/((End)-(Start))),IF((LEFT(Start,3)=LEFT($J27,3)),--(Qtty),"")))


...Where following stands for "Named Ranges": 

[pre]Start:  $F$27:$F$218
End:    $G$27:$G$218
Qtty:   $C$27:$C$218
[/pre]

...and see this file: http://dl.dropbox.com/u/60644346/Fresh%20Sample%20Sheet_Chandoo_Resolved.xlsx


...hopefully I have understood the problem correctly this will finally solve the problem.


@SirJB7: Once again paid by no. of characters! Lolz ;)


Regards,

Faseeh
 
Hi, pradhisnair!

Well, I see that Faseeh arrived with a new approach. In order to minimize resources use, I'll wait and see what happens. I wish he had found the answer.

Regards!


@Faseeh

Hi!

I hope you succeed (so as I can rest for a while, let's say weekend).

Regards!

PS: don't forget my 15% over your 0,15€/char you get paid...
 
@ faseeh:


it did give me correct result for the first case. but not for subsequent ones.


Another manual testing is done for 2nd case and is updated please do have a look


https://docs.google.com/spreadsheet/ccc?key=0AgDJ-TLtQDaydFhJTnl2UF9LU0NpeGJiNTMtbGpzUWc


@ SirJB7:

Problem still persists.. kindly do have a look...
 
Hi pradhishnair,


I am not clear regarding your 'second case', i don't think you mentioned "a second case" in your lastly uploaded file? :) If there is still any other testing criteria i would request you to please enlist all of them at once, It will be easier for any one to give you a perfect solution! BTW formula is working just perfect for the lastly mentioned rounded off values. (Yesterday i tested it randomly). So there is nothing wrong with the solution if we stick to what had been requirement till last worksheet. Am I wrong? :)


Regards,

Faseeh
 
You also missed Entry No. 05 in Methodology no. 02, It should have been 1,2,5,6 instead of just 1,2,6. Is that correct?


Faseeeh
 
A partly modified formula seems to work (with an error of less then 1%), you may give it a try:


=SUMPRODUCT(IFERROR(((((Start>=$D$3)*(Start<=$D$4))*(Qtty)*(((ROUNDDOWN(Start/1000,0)+1)*1000)-(Start)))/((End)-(Start))),IF(((Start>=$D$3)*(Start<=$D$4)),--(Qtty),"")))


and the file: http://dl.dropbox.com/u/60644346/Fresh%20Sample%20Sheet_Chandoo_Resolved_2.xlsx


Kindly see the sample sheet, the value you have been calculating and mentioning in your example sheet has some problem.


Regards,

Faseeh
 
@Faseeh

Hi!

It's all yours, buddy. The credit, of course :)

And if with such formulas you're not getting paid by char (at 0.15€/char with my 15% included) I don't know why do you write so looong formulas.

Regards!

PS: All yours. And not only the credit :)
 
Hi pradhishnair,


I rechecked and found not even 1% error, that was an over sight while i was creating samples for you!! See this one:


http://dl.dropbox.com/u/60644346/Fresh%20Sample%20Sheet_Chandoo_Resolved_Full%20%26%20Final.xlsx


@SirJB7,


What i get paid 15% is always yours!! :), this time i really forced to revise why i write so long! The gentleman is more interested in getting problem solved by you, in my opinion it is solved. :p


Regards,

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