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

need formula

ashfaqbsayed

ashfaqbsayed

New Member
In Row o column 6,7,8 i need a formula which should satisfy condition to give points 250,200 and 150 respectively for a sales person with highest sales in hours that is sum of qualified (m6,m7,m8) and sum of CPH(n6,n7,n8,). If m6, is highest it should give 250 points in row o column 6, If m7, is second highest it should give 200 points in row o column 7, If m8, is third highest it should give 150 points in row o column 8. If m6,m7,m8, gets tie formula should check for sum of CPH in row n6,n7, and n8 highest CPH should get 250, second highest CPH should get 200 and third highest CPH should get 150 respectivly. If m6,m7,m8, gets tie formula should check for sum of CPH in row n6,n7, and n8, and even CPH get tie for first position both should get 250 points and third position will get 150 points, for first position formula should check for m6,m7,m8 and n6,n7,n8 for second position formula should check m6,m7,m8, and m9 and n6,n7,n8, and n9 and for third position formula should check for m6,m7,m8,m9,m10 and n6,n7,n8,n9, and n10.

Any help will be appreciated.
i just have hope with chandoo.org.
 

Attachments

  • for chandoo.com.xlsx
    559.1 KB · Views: 9
Hi Sayed ,

I am sure you will get your answer , but I think some clarifications might be required.

However , the first issue is that when I download your file , and try to open it , I get an error message ; can you download it from the link you have posted , and see if it works for you ?

Narayan
 
Hi Sayed ,

Can you clarify a few points ?

Your worksheet has formulae in the following ranges viz. K6:K9 , O6:O9 , S6:S9 ,.... ; do you want correct formulae in these same ranges , based on the logic you have given ?

If this is so , can you clarify the logic in more detail ?

1. First we look at the data in the ranges I6:I9 , M6:M9 , Q6:Q9 ,.... ; in each of these ranges , there are two possibilities viz. either there are distinct values in each of the 4 cells e.g. I6 , I7 , I8 and I9 all have different values in the order I6 > I7 > I8 > I9. Or , there are 2 or more cells which have the same value , and in the extreme case , all 4 cells may have the same value.

2. If all the 4 cells have different values , then the corresponding cells in the ranges K6:K9 , O6:O9 , S6:S9 ,...., are assigned the values 250 , 200 , 150 and 0 in the same order i.e. K6 will have 250 , K7 will have 200 , K8 will have 150 and K9 will have 0.

3. If two or more cells have the same value , then the corresponding values in the ranges J6:J9 , N6:N9 , R6:R9 ,.... are used to decide the resulting outputs ; here again , the same two possibilities exist ; you have mentioned that if there is a tie in this range also , then all cells having the same value will get the same assignment ; but the values you have in the range K6:K9 and O6:O9 do not bear this out ; according to your logic , the values in K6:K9 will be : 200 , 250 , 150 , 0. Similarly , the values in O6:O9 will be : 200 , 200 , 250 , 0. Can you clarify / confirm ?

Narayan
 
Hi Narayan,

Thank for your reply
Formula in the following ranges viz. K6:K10 , O6:O10 , S6:S10 ,.... ; Yes want correct formula in these above ranges , based on the logic i have given.

Yes sir, you got that very clear, that's what i want and one more thing if it is "0" in any of cell I6:I10, M6:M10, or Q6:Q10 it should display answer as "0" in cell K6:K9 , O6:O9 , S6:S9 ,...., are assigned the values 250 , 200 , 0 and 0 in the same order i.e. K6 will have 250 , K7 will have 200 , K8 will have 0 and K9 will have 0.

I thing we can also we formula Large.

1 comp NTMA_8_112 2 comp SBM2_5_6
Data Data
AgentName Sum of Qualifieds Sum of CPH AgentName Sum of Qualifieds Sum of CPH
Average 28 0.47 Average 28 0.47
ANNIE 12 4 200 ROY 8 2 250
FRANCIS 12 6 250 PHILIP 8 2 250
PETER 0 0 0 PETER 0 0 0
ADROY 0 0 0 REMO 0 0 0
SNYDER 0 0 BROCK 0 0
 
Hi Sayed ,

Can you try this in K6 ?

=LARGE({250,200,150,0,0},SUMPRODUCT(--(I$6:I$10+J$6:J$10%>I6+J6%))+1)

Copy this down.

Narayan
 
Hi Narayan,

Perfect Sir, its working every where, K6,K7,K8,K9 and K10..... on and on and on...
can i get your mobile no...
i wish if i can speak with you....

Thank u so much for resolving my issue and i have two more issues to solve if you can help me that will be great ....
i don't know which part of India your from, may be Aandra Pradesh... but are guys are "AWESOME"

I want a macros which can send a email from email client base and i'm using Zimbra, that means not from outlook, and email should be send in html format that is color and font should remain the same, it should pick up the range for eg: c1 to q56 it should copy paste and send it ...

can we do this Sir Narayan ?

Thanks again and waiting for your response.
 
Hi Sayed ,

Thanks for the feedback ; my problem is that I do not have time to spare ; if it is some requirement which can be resolved over a week , then I can certainly do it , but if it is urgent , then I am sorry I cannot spare the time.

This forum has several others who can help you out if you give all details. Please do that , and let us see.

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