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
