Determining Quantity and Keeping a Constant

[Hoov]

I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)>=200, <=1000) But, I need to know what it actually should be!

Also, I need to know how to keep one cell a constant in a function that i am
repeating. For example, the function that I want in C5 is "=B5/I4". Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in every
equation, but B will change as I go down the spreadsheet. How can I get this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!

 

[Mike H]

Hi,

To count the numbers in a range

=SUMPRODUCT((C1:C100>=200)*(C1:C100<=1000))

and your second question. Put this in C6 and drag down
=B6/$I$4

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[John]

Hi Hoov
Try this =COUNTIF(C1:C1:C100,">=200")-COUNTIF(C1:C100,">1000")
Second question : =B5/$I$4
Use the dollar sign to make it absolute.
HTH
John

 

[Bernard Liengme]

Q1:
Some examples of formulas (not functions - they are something else) you
could use
(a) =COUNTIF(C1:C100,">=200") - COUNTIF(C1:C100, "<=1000)
(b) =SUMPRODUCT(--(C1:C100>=200),--(C1:C100<=1000))
(c) =COUNTIFS(C1:C100,">=200",C1:C100,"<=1000") ' only in Excel 2007

COUNTIF is explained in Help
SUMPRODUCT is explained in these:
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct

2) =B5/$I$4
You can type the $ sings to make the refercne 'absolute' of with the cursor
on eiter the I or the 4 press F4
Chip explains this topic at
http://www.cpearson.com/excel/relative.aspx

best wishes

 

[Hoov]

Thank you so much Mike, this worked perfectly!

Hi,

To count the numbers in a range

=SUMPRODUCT((C1:C100>=200)*(C1:C100<=1000))

and your second question. Put this in C6 and drag down
=B6/$I$4

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.