Formula to Determine Ratios Within another Group of Cells

[joezeppy]

Hi,

So far, I've had great success finding difficult answers here. THANK YOU! This one is a challenge for me. Hoping someone can help.

I have a group of products with 13 or so price bands (bands are within various ranges). There are about 800 products in the worksheet.

For various reasons, I need to know what ratio of prices are in each band in relation to the others.

Example:

Worksheet One

I have 800 product names in column A.
I have their prices in column B.
All prices are within certain Ranges (Bands) e.g.,. from $35 to $49, then $50 to $149, etc.,. (there are 6 Bands/Price Ranges).

Worksheet Two

In another worksheet, I need to track what percent of the total number of products fall within each band in relation to each other. For various reasons, there is a goal to be able to monitor these ratios on an ongoing basis in relation to each other. Another way of stating it is: What percent of all the product prices listed fall within Band A? What percent of all product prices listed in Worksheet One fall withing Band B? Etc,.,?

I'd like the formula to update as I change / set prices.

Band Band Ratio Goal Price Range
A 16% $35 - $49
B 16% $50 - $149
C 18% $150 - $249
D 20% $250 - $499
E 10% $500 - $999
F 5% $1,000 - $1,999

TIA

 

[dUBBINS]

=countif(b1:b100,"$35 - $49")/counta(b1:b100)*100&"% $35 - $49"

 

[FDibbins]

dUBBINS, I have to ask - is your last name Ubbins, or Dubbins?
Just curious because my last name is Dibbins

 

[joezeppy]

=countif(b1:b100,"$35 - $49")/counta(b1:b100)*100&"% $35 - $49"

Not seeming to be working... Maybe I did something wrong?

I have this in the cell since the prices are in a separate worksheet.

=COUNTIF('Active Sales Sheet'!E3:E815,"$35 - $49")/COUNTA('Active Sales Sheet'!E3:E815)*100&"% $35 - $49"

I have about 50 out of the 800 that are in that range so it should show something like 06%.

The result is "0% $35 - $49" in one of the cells.

Any thoughts?

Thx,
Joe

P.S. In essence, for each cell (about 13 cells stacked up), I will be asking,

"Out of all the prices listed in range 'Active Sales Sheet'!E3:E815, how many of those prices are within $x - $y ? and what percent is that of the total 812 entries? "

Those are the questions I'll be asking to display for each Price Band one cell on top of the other. If it works right I'll be able to sum up the 13 price band percents and get 100% at the bottom.

Any help is appreciated.

 

[joezeppy]

This is really the essence of what I'm trying to solve:

"Out of all the prices listed in range 'Active Sales Sheet'!E3:E815, how many of those prices are within $x - $y ? and what percent is that of the total 812 entries? "

 

[mikerickson]

This is one formulation. Note the way that the bands are defined (by columns F and G rather than by E)

Unknown
	A	B	C	D	E	F	G	H
1	Items	Prices
2	Oranges	238.21		A	$35 - $49	35	49	16.67%
3	Apples	329.96		B	$50 - $149	50	149	8.33%
4	Fish	467.63		C	$150 - $249	150	249	16.67%
5	Goats	1424.85		D	$250 - $499	250	499	33.33%
6	Hamsters	93.05		E	$500 - $999	500	999	8.33%
7	Nails	592.56		F	$1,000 - $1,999	1000	1999	16.67%
8	2X4's	395.65
9	2X6's	217.94
10	Birdcages	47.88
11	Puppies	1276.52
12	Cats	36.82
13	Pears	425.79
Sheet1

Cell Formulas
Range	Formula
H2	=COUNTIFS(Sheet1!B:B,">="&F2,Sheet1!B:B,"<"&G2)/COUNT(Sheet1!B:B)

 

[joezeppy]

Hi Mike, Thanks for this. It seemed like it worked once but for some reason I can't seem to get it to work consistently (I'm sure my fault; still trying). If anyone has ideas feel free to chime in. I do think what Mike has proposed could work. I just have to get it right.

Also, if anyone thinks the approach that dUBBINS proposed could work with any edits please let me know your thoughts. I"m sure I'll get there. Thanks guys.

 

[joezeppy]

Does anyone have another idea or suggestion? Maybe my settings are off in Excel; not updating? I don't know why it's not working.

Thanks regardless.

 

[joezeppy]

Or one more idea. What would that look like if i used the same worksheet? Maybe I'm not expressing correctly with the long sheet names?

Any thoughts appreciated. I know there's a way to make this work.

Thanks again.

 

[mikerickson]

The code I posted was taken from a single sheet situation.

1) Are the inequalities the way that you want them?
2) Are any of the prices in text format rather than numbers?

You say it sometimes doesn't work, but don't specify what is wrong.