Thx Gaurav,
best regards,
Dilipandey
On 6/29/10, GAURAV JAIN <jaingaura...@gmail.com> wrote:
> Hi Dilip,
>
> Discription is nice one.
>
> Cheers,
> Gaurav Jain
>
> On Sat, Jun 26, 2010 at 11:15 PM, Dilip Pandey <dilipan...@gmail.com> wrote:
>
>> Hi Swapnil,
>>
>> Below is a little explanation of the formula which I had used:-
>> Hope you will like it :)
>>
>> SUMPRODUCT(--($A$2:$A$483=A2),--(D2<$D$2:$D$483))+1
>>
>> Above formula will also work if you enter like below:-
>> SUMPRODUCT(-($A$2:$A$483=A2),-(D2<$D$2:$D$483))+1
>>
>> But ideally you should use -- (two times minus sign), which results in
>> mathematical funda i.e., - x - = +
>>
>> So if you evaluate the function SUMPRODUCT(($A$2:$A$483=A2)), you will
>> see that there are two TRUEs you are getting and by applying only one
>> minus (-) sign in the formula SUMPRODUCT(-($A$2:$A$483=A2), you will
>> get -2 and if you apply two minus sign in the formula
>> SUMPRODUCT(--($A$2:$A$483=A2), you will +2 or 2 ( positive numbers
>> have + sign as default)
>>
>> Let me start step by step:-
>> SumProduct is kind of Array functionality, here it considers the range
>> wherein data in column A is equal to A2 which is "Class1" and then
>> corresponding to it considers the range where the data in Column D is
>> greater then D2 "234".
>> So if you consider data only related with Class 1 (filter the data on
>> Class 1 in column A), you will see that there is no data row which is
>> greater than 234 and there is one data row which is greater than 9.
>> Now if you add 1 to both these results i.e., adding 1 to None (1+0 =
>> 1) and adding 1 to 1 (1+1=2), you will get the desired Ranking and
>> this is why I have added 1 at the end of each formula.
>>
>> This is the main reason why I love SumProduct function :)
>>
>> Alternatively, you can also use below array formula with sum
>> functionality which can deliver same results:-
>>
>> {=SUM(IF(($A$2:$A$483=A2)*(D2<$D$2:$D$483),1,0))+1}
>>
>> Now, I leave this to you to learn and understand :)
>>
>> --
>> Thanks & Regards,
>>
>> DILIP KUMAR PANDEY
>> MBA-HR,B.Com(Hons),BCA
>> Mobile: +91 9810929744
>> dilipan...@gmail.com
>> dilipan...@yahoo.com
>> New Delhi - 62, India
>>
>>
>>
>>
>> On 6/26/10, Swapnil Palande <swapnilp1...@gmail.com> wrote:
>> > Hi Dilip,
>> >
>> > This is excellent solution. But I am not able to understand it. Can you
>> > explain it.
>> >
>> > Regards,
>> >
>> > Swapnil.
>> >
>> > On Sat, Jun 26, 2010 at 4:52 PM, Dilip Pandey <dilipan...@gmail.com>
>> wrote:
>> >
>> >> Thanks Dave...!!!
>> >>
>> >> --
>> >> Thanks & Regards,
>> >>
>> >> DILIP KUMAR PANDEY
>> >> MBA-HR,B.Com(Hons),BCA
>> >> Mobile: +91 9810929744
>> >> dilipan...@gmail.com
>> >> dilipan...@yahoo.com
>> >> New Delhi - 62, India
>> >>
>> >>
>> >> On 6/26/10, Dave Bonallack <davebonall...@hotmail.com> wrote:
>> >> >
>> >> > Hi Dilip,Excellent solution!Regards - Dave
>> >> >
>> >> > Date: Fri, 25 Jun 2010 16:48:57 +0530
>> >> > Subject: Re: $$Excel-Macros$$ Urgent Help needed regarding using
>> >> > macro
>> >> > From: dilipan...@gmail.com
>> >> > To: excel-macros@googlegroups.com
>> >> > CC: mathan4s...@gmail.com
>> >> >
>> >> > Hi Mathan,
>> >> >
>> >> > Attached file has been solved per your requirements. Please let me
>> know
>> >> if
>> >> > it solves your problem else get back with your queries.
>> >> >
>> >> > --
>> >> > Thanks & Regards,
>> >> >
>> >> > DILIP KUMAR PANDEY
>> >> > MBA-HR,B.Com(Hons),BCA
>> >> > Mobile: +91 9810929744
>> >> > dilipan...@gmail.com
>> >> > dilipan...@yahoo.com
>> >> >
>> >> > New Delhi - 62, India
>> >> >
>> >> >
>> >> > On Thu, Jun 24, 2010 at 9:14 PM, Mathan <mathan4s...@gmail.com>
>> wrote:
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Hi,
>> >> >
>> >> > In attached file I need ranking based on 'Points' column but we have
>> to
>> >> > consider Coulmn 'A' i.e.) class.
>> >> >
>> >> > Please help me...
>> >> >
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Regards,
>> >> > M4S....
>> >> >
>> >> > --
>> >> >
>> >>
>> ----------------------------------------------------------------------------------
>> >> > Some important links for excel users:
>> >> > 1. Follow us on TWITTER for tips tricks and links :
>> >> > http://twitter.com/exceldailytip
>> >> >
>> >> > 2. Join our LinkedIN group @
>> http://www.linkedin.com/groups?gid=1871310
>> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> >
>> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >
>> >> >
>> >> > To post to this group, send email to excel-macros@googlegroups.com
>> >> >
>> >> > <><><><><><><><><><><><><><><><><><><><><><>
>> >> >
>> >> > HELP US GROW !!
>> >> >
>> >> > We reach over 7000 subscribers worldwide and receive many nice notes
>> >> about
>> >> > the learning and support from the group.Let friends and co-workers
>> know
>> >> they
>> >> > can subscribe to group at
>> >> > http://groups.google.com/group/excel-macros/subscribe
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> >
>> >>
>> ----------------------------------------------------------------------------------
>> >> >
>> >> > Some important links for excel users:
>> >> >
>> >> > 1. Follow us on TWITTER for tips tricks and links :
>> >> > http://twitter.com/exceldailytip
>> >> >
>> >> > 2. Join our LinkedIN group @
>> http://www.linkedin.com/groups?gid=1871310
>> >> >
>> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> >
>> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> >
>> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >
>> >> >
>> >> >
>> >> > To post to this group, send email to excel-macros@googlegroups.com
>> >> >
>> >> >
>> >> >
>> >> > <><><><><><><><><><><><><><><><><><><><><><>
>> >> >
>> >> > HELP US GROW !!
>> >> >
>> >> >
>> >> >
>> >> > We reach over 7000 subscribers worldwide and receive many nice notes
>> >> about
>> >> > the learning and support from the group.Let friends and co-workers
>> know
>> >> they
>> >> > can subscribe to group at
>> >> > http://groups.google.com/group/excel-macros/subscribe
>> >> >
>> >> > _________________________________________________________________
>> >> > View photos of singles in your area! Looking for a hot date?
>> >> > http://clk.atdmt.com/NMN/go/150855801/direct/01/
>> >> >
>> >> > --
>> >> >
>> >>
>> ----------------------------------------------------------------------------------
>> >> > Some important links for excel users:
>> >> > 1. Follow us on TWITTER for tips tricks and links :
>> >> > http://twitter.com/exceldailytip
>> >> > 2. Join our LinkedIN group @
>> http://www.linkedin.com/groups?gid=1871310
>> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >
>> >> > To post to this group, send email to excel-macros@googlegroups.com
>> >> >
>> >> > <><><><><><><><><><><><><><><><><><><><><><>
>> >> > HELP US GROW !!
>> >> >
>> >> > We reach over 7000 subscribers worldwide and receive many nice notes
>> >> about
>> >> > the learning and support from the group.Let friends and co-workers
>> know
>> >> they
>> >> > can subscribe to group at
>> >> > http://groups.google.com/group/excel-macros/subscribe
>> >> >
>> >>
>> >> --
>> >>
>> >>
>> ----------------------------------------------------------------------------------
>> >> Some important links for excel users:
>> >> 1. Follow us on TWITTER for tips tricks and links :
>> >> http://twitter.com/exceldailytip
>> >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> >> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >>
>> >> To post to this group, send email to excel-macros@googlegroups.com
>> >>
>> >> <><><><><><><><><><><><><><><><><><><><><><>
>> >> HELP US GROW !!
>> >>
>> >> We reach over 7000 subscribers worldwide and receive many nice notes
>> about
>> >> the learning and support from the group.Let friends and co-workers know
>> >> they
>> >> can subscribe to group at
>> >> http://groups.google.com/group/excel-macros/subscribe
>> >>
>> >
>> > --
>> >
>> ----------------------------------------------------------------------------------
>> > Some important links for excel users:
>> > 1. Follow us on TWITTER for tips tricks and links :
>> > http://twitter.com/exceldailytip
>> > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >
>> > To post to this group, send email to excel-macros@googlegroups.com
>> >
>> > <><><><><><><><><><><><><><><><><><><><><><>
>> > HELP US GROW !!
>> >
>> > We reach over 7000 subscribers worldwide and receive many nice notes
>> about
>> > the learning and support from the group.Let friends and co-workers know
>> they
>> > can subscribe to group at
>> > http://groups.google.com/group/excel-macros/subscribe
>> >
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 7000 subscribers worldwide and receive many nice notes about
>> the learning and support from the group.Let friends and co-workers know
>> they
>> can subscribe to group at
>> http://groups.google.com/group/excel-macros/subscribe
>>
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
Sent from my mobile device
Thanks & Regards,
DILIP KUMAR PANDEY
MBA-HR,B.Com(Hons),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 62, India
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
