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]

It is actually Nowlin. Dubbins is a nickname I got from a friend long ago, and then I started using it for gaming, and things like this forum. But you got a sweet name. Ford Dibbins. I dig it. Thanks for all your help over the years. I learn from you on a lot of other people's posts.

 

[joezeppy]

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

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

It works! Please accept my apologies. I made a mistake or two trying to enter the formula using my own sheet.

It definitely is working now and will be really helpful to me as I move along.

Thank you very much!!!

Joe

P.S. If you see this and have a minute to answer, what is the purpose of the "&" ampersands in that formula? That's a part of it that I didn't understand. I'd like to know just for my knowledge and edification .



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

 

[mikerickson]

If you want a condition of greater than 35, the criteria argument should be the string ">35".
The ampersands are to build the appropiate criteria strings.

 

[joezeppy]

If you want a condition of greater than 35, the criteria argument should be the string ">35".
The ampersands are to build the appropiate criteria strings.

Thanks once again. Very helpful!