Function Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I write a function statement to calculate a range of informatin. IE., a list of populations for cities, and I need to know how many cities have a population between 800,000 and 1,000,000?
 
Hi Carolyn
a little bit more information would be helpful. Bu assuming that in
your worksheet column A stores city names and column B the respective
pupulation tray the following formula:
=SUMPRODUCT((B1:B1000>=1000000)*(B1:B1000>=800000))

Frank
 
=COUNTIF(Range,">=800000")-COUNTIF(Range,">1000000")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Carolyn said:
How can I write a function statement to calculate a range of informatin. IE.,
Click to expand...
a list of populations for cities, and I need to know how many cities have a
population between 800,000 and 1,000,000?
 
Hi Carolyn!

With the populations in column A

One way using COUNTIF:
=COUNTIF(A1:A10,">=800000")-COUNTIF(A1:A10,">1000000")



Or another using implicit if statement structures in SUMPRODUCT

=SUMPRODUCT((A1:A10>=800000)*(A1:A10<=1000000))



Or you may prefer the explicit IF function approach that uses SUM



=SUM(IF(A1:A10>=800000,1,0)*IF(A1:A10<=1000000,1,0))

Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.



Appears as:



{=SUM(IF(A1:A10>=800000,1,0)*IF(A1:A10<=1000000,1,0))}


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Carolyn said:
How can I write a function statement to calculate a range of
Click to expand...
informatin. IE., a list of populations for cities, and I need to know
how many cities have a population between 800,000 and 1,000,000?
 
Back
Top