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
