Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / August 2007

Tip: Looking for answers? Try searching our database.

Get a percentage based on multiple conditions

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Matt - 22 Aug 2007 21:09 GMT
I would like to determine a percentage in Excel that is based on
multiple conditions.  This is for a serious a products on different
displays and the quantity on those displays.  I need to know out of
all the stores surveyed, what percentage of those stores had a lobby
display with more than 150 cases. If I had a small set of data I'm
sure I could just pivot the data a few ways and be done, but I have
over 50,000 rows.  See the sample set of data below for more detail.

Store    Display        Product        Qty
-------    --------------    --------------    ----------
123    Lobby        Soda Cans    50
123    Aisle        Soda Cans    75
123    Perimeter    Soda Cans    75
123    Lobby        2 liter        120
123    Aisle        2 liter        200
123    Perimeter    2 liter        50
123    Lobby        20 oz        20
123    Aisle        20 oz        60
123    Perimeter    20 oz        0
456    Lobby        Soda Cans    100
456    Aisle        Soda Cans    50
456    Perimeter    Soda Cans    50
456    Lobby        2 liter        25
456    Aisle        2 liter        150
456    Perimeter    2 liter        25
456    Lobby        20 oz        20
456    Aisle        20 oz        60
456    Perimeter    20 oz        0
789    Lobby        Soda Cans    40
789    Aisle        Soda Cans    100
789    Perimeter    Soda Cans    20
789    Lobby        2 liter        80
789    Aisle        2 liter        200
789    Perimeter    2 liter        50
789    Lobby        20 oz        40
789    Aisle        20 oz        60
789    Perimeter    20 oz        0

>From this sample data, you would determine that out of the 3 stores
only 2 of them (66%) have a Lobby display with more than 150 cases. In
Excel 2003, how I can I do this? I cannot wrap my head around it.
Thank you in advance for your assistance.
Scott - 23 Aug 2007 00:08 GMT
> I would like to determine a percentage in Excel that is based on
> multiple conditions.  This is for a serious a products on different
[quoted text clipped - 39 lines]
> Excel 2003, how I can I do this? I cannot wrap my head around it.
> Thank you in advance for your assistance.

You might try the following formula:
=COUNTIF(D2:D50000,">=150")/COUNT(D2:D50000)

Scott
Scott - 23 Aug 2007 00:18 GMT
> > I would like to determine a percentage in Excel that is based on
> > multiple conditions.  This is for a serious a products on different
[quoted text clipped - 44 lines]
>
> Scott

Ok, Ignore me.. I didn't read. :-)
Matt - 23 Aug 2007 14:44 GMT
> > > I would like to determine a percentage in Excel that is based on
> > > multiple conditions.  This is for a serious a products on different
[quoted text clipped - 48 lines]
>
> - Show quoted text -

Well, that may help in a small way, it will give me an idea of what
I'm working with, but unfortunately that still isn't the solution.  I
haven't been able to figure out a way to do this...
daxmiller@gmail.com - 24 Aug 2007 10:28 GMT
> I would like to determine a percentage in Excel that is based on
> multiple conditions.  This is for a serious a products on different
[quoted text clipped - 39 lines]
> Excel 2003, how I can I do this? I cannot wrap my head around it.
> Thank you in advance for your assistance.

Enter the following as an array formula i.e. ... Ctrl Shft Enter,
rather than the usual ... Enter:

=COUNT(IF($B2:$B50000="Lobby",IF($D2:$D50000=150,$D2:$D50000))) /
COUNT($D$2:$D$50000)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.