Filter/sort data to give highest value from each day

[Guest]

Hi everyone,
I have 144 values for each day (readings taken every 10 minutes) and I want
to graph only the highest readings from each because I have almost 50000
readings and that's ridiculous on a chart!
Is there some way I can sort the data to give this information, or is there
a formula/function I can use to get Excel to do this search for me and return
only the highest value for each day?
Thanks in advance
Cootha

 

[Guest]

- use "=large()" function to get the top highest readings

*** Please do rate ***

 

[Guest]

Thanks Naveen for your response.
Not sure I made my question very clear. I want to return the highest number
for each day of the year (so I am hoping to get 1 January to 31 December in
one column, and the highest number listed for each of these days in the other
column) - is this is possible of course.
Cootha

 

[Guest]

Believe you're looking to extract conditional maximums ..

Assuming real dates running down in col A, corresponding values in col B,
from row1 to say, row100, eg:

01-Aug-06 61
01-Aug-06 62
01-Aug-06 69
02-Aug-06 52
02-Aug-06 58
02-Aug-06 72
02-Aug-06 57
etc

Note: Dates in col A can be in any order, need not be sorted. But they must
be real dates recognized by Excel.

List the unique dates in say, D1 down, eg:

01-Aug-06
02-Aug-06
etc

Then place in E1's formula bar, array-enter by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=MAX(IF($A$1:$A$100=D1,$B$1:$B$100))
Copy E1 down

Col E will return the max values from col B for the dates listed in col D
Use cols D & E for your downstream charting needs

Adapt the ranges to suit the extents of your actual data before you copy
down E1. Due to the large ranges involved (50,000?), it would be appropriate
to set the calc mode to Manual first (via Tools > Options > Calculation tab).
When the formula fills are complete, just press F9 to calc/recalc col E.

 

[Guest]

Pl see my response ..

 

[Guest]

Max,
You are wonderful, and it kind of worked, but I am not getting the maximum
number for each day. Most of the maximum numbers should be over 1000 (and
they are in the list), but my highest number in the results list is not this.
The numbers i get are not even in the list!
Help again!
Cootha

 

[Guest]

Max, now working. My problem was the date column also includes the time. not
sure how to get rid of that besides doing it manually, but it is working.
Thanks a heap!
Cootha

 

[Biff]

Here's another one:

Your list of raw data is A1:B50000, column A are the dates.

D1365 are the unique daily dates:

D1 = 1/1/2006
D2 = 1/2/2006
D3 = 1/3/2006
...
D365 = 12/31/2006

Enter this formula in E1 and copy down:

=SUMPRODUCT(MAX((A$1:A$50000=D1)*(B$1:B$50000)))

Biff

 

[Guest]

A couple of possibilities ..

a. Did you array-enter the formula correctly before you copied down the col?
You should see curly braces { } inserted by Excel if the formula is confirmed
correctly (we don't type in these braces). In E1's formula bar, it should
look like this:
{=MAX(IF($A$1:$A$100=D1,$B$1:$B$100))}

b. Did you adapt the formula to suit correctly? The ranges for cols A and B
would be identical, eg: =MAX(IF($A$1:$A$50000=D1,$B$1:$B$50000)). Could you
copy n paste the actual formula here that you're using over there?

c. Assuming the above 2 are correctly applied, then it could be a data
consistency issue, ie not all dates in col A are real dates recognized by
Excel, and/or, not all values in col B are real numbers.

Try selecting the source date col A only, click Data > Text to Columns.
Click Next > Next to proceed to step 3. In step 3, check "Date" under "Column
Data Format", then select the appropriate date format, eg: MDY. Click Finish.
This should suffice to convert all "dates" to real dates recognized by Excel.
Then if necessary, select col B (values), click Data > Text to Columns, then
just click Finish. This should also suffice to convert all text numbers in
the source col B to real numbers.

 

[Guest]

Yes,
paste the following array-formula

=MAX((Sheet1!A2:A61=A1)*(Sheet1!B2:B61))

replace "Sheet1!A2:A61" with your date range from raw data
replace "A1" with frist cell of 1-Jan to 31-Jan
replace "Sheet1!B2:B61" with your numbers range from raw data


NOTE: array formulas are entered by typing formula and pressing
CTRL+SHIFT+Enter - instead of simply Enter.

*** Please do rate ***

 

[Guest]

Glad to hear that .. I've posted another response earlier - prematurely it
seems [g] - pl disregard that response

.. My problem was the date column also includes the time
not sure how to get rid of that besides doing it manually

If the above is the case in your source "dates" as-is,
we could use INT(..), viz try instead, array-entered in E1:
=MAX(IF(INT($A$1:$A$50000)=D1,$B$1:$B$50000))
Copy E1 down

(No need to manually work on the source "dates" in col A)

---