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)