HELP: Sorting Ranged Data ?

HELP: Sorting Ranged Data ?

Post by alan » Tue, 22 Dec 1998 04:00:00



Try this array formula:

=SUM(IF(datarange>=min,IF(datarange<max,1,0)))

where datarange is your range of data and min and max are the cells that
represent the lower and upper limits of your sort.

Hold the shift & control key and hit enter, and the formula should appear in
brackets {  }

Alan


>HELP: Sorting Ranged Data ?

>Here's an interesting problem.

>I'am bit stuck with this one.

>Essentially the problems is as follows:

>I have a set of data:

>     Tender values for Substructures:

>     Tender 1 = 20000
>     Tender 2 = 30000
>     Tender 3 = 15000
>     Tender 4 = 18000
>     Tender 5 = 19000
>     Tender 6 = 45000

>What I want to achive is to take the above an sort it into ranges:
>indicating how many tenders fall into each range.

>     0  to 10000           =0
>     10001 to 20000        =4
>     20001 to 30000        =1
>     30001 to 40000        =0
>     40001 to 50000        =1

>and from this draw a graph. With the ranges shown and number of
>occurancies in each range.

>I know what I want, but don't how to achive this in Excel.

>Any help or observations would be greatly appreciated.

>Regards

>Ike



 
 
 

HELP: Sorting Ranged Data ?

Post by George Nicholso » Tue, 22 Dec 1998 04:00:00


General Ike:

Here is one approach:
The following formula assumes the following:
C3:C8 contains the numerical portion of your data, from which you want to
count # of occurances.
A11:A16 contains "Value", 0, 10001, 20001, etc.
B12:B16 contains "To"
C12:C16 contains 10000, 20000, 30000, etc.
D11 contains the label "Count"
Then the following should be entered in D12 & filled down to D16

=COUNTIF(C$3:C$8, ">=" & A12) - COUNTIF(C$3:C$8, ">" & C12)

Select the ranges A11:A16, D11:D16 & start the Chart Wizard for either a
column or line graph.
Under series, delete "Value", leaving only "Count" to be graphed.
Set A11:A16 as the "Category (x) axis labels".

Good Luck,
George


>HELP: Sorting Ranged Data ?

>Here's an interesting problem.

>I'am bit stuck with this one.

>Essentially the problems is as follows:

>I have a set of data:

>     Tender values for Substructures:

>     Tender 1 = 20000
>     Tender 2 = 30000
>     Tender 3 = 15000
>     Tender 4 = 18000
>     Tender 5 = 19000
>     Tender 6 = 45000

>What I want to achive is to take the above an sort it into ranges:
>indicating how many tenders fall into each range.

>     0  to 10000           =0
>     10001 to 20000        =4
>     20001 to 30000        =1
>     30001 to 40000        =0
>     40001 to 50000        =1

>and from this draw a graph. With the ranges shown and number of
>occurancies in each range.

>I know what I want, but don't how to achive this in Excel.

>Any help or observations would be greatly appreciated.

>Regards

>Ike




 
 
 

HELP: Sorting Ranged Data ?

Post by Sandma » Tue, 22 Dec 1998 04:00:00



> HELP: Sorting Ranged Data ?

> Here's an interesting problem.

> I'am bit stuck with this one.

> Essentially the problems is as follows:

> I have a set of data:

>      Tender values for Substructures:

>      Tender 1 = 20000
>      Tender 2 = 30000
>      Tender 3 = 15000
>      Tender 4 = 18000
>      Tender 5 = 19000
>      Tender 6 = 45000

> What I want to achive is to take the above an sort it into ranges:
> indicating how many tenders fall into each range.

>      0  to 10000           =0
>      10001 to 20000        =4
>      20001 to 30000        =1
>      30001 to 40000        =0
>      40001 to 50000        =1

> and from this draw a graph. With the ranges shown and number of
> occurancies in each range.

> I know what I want, but don't how to achive this in Excel.

> Any help or observations would be greatly appreciated.

> Regards

> Ike



Sounds like you need to try
Tools, Data Analysis, Histogram.
* Give it your quotes as the input range
* Define the 'Bins' as 10000, 20000,30000 etc
and away you go.
I think you have to make sure that the option is installed before it
will appear on the menu.
Hope that helps
Sam
 
 
 

1. Data bits, range and addressing range??

can anyone explain what the difference is between number of data bits and
the number of address bits and the addressable range - cause I have to find
out the information for this for the National COP888 and the Motorola HC11
and the Hitachi SH3 processor.  Is there any other name that those terms are
also called in - cause I have the manuals for the processor but those terms
have not come up.

THANKS

2. Problem with network printer

3. HELP TO SORT DATA

4. Hammer drivers?

5. Need Special Data sorting help

6. BIND DNS

7. Help required with sorting data

8. yet another FAT-app question

9. Help: Data Sheet for 6500 series Sonar Ranging module

10. Problems with sorting range

11. DBKit: Data not being sorted - Solved