Extracting Date of Lowest Value from List

[Kay]

Hi - I have a list of daily $Amounts for ea day of the year.

ie --Month-- --Date-- --Amount--

In a summary table, I extract the highest AMOUNT for a particular
month with {=MAX(IF(Month="Dec",'9am'!Amount))}

I extract the DATE of the higest AMOUNT for thyat month, Dec, with
{=MAX(((Month)="Dec")*(Amount=MAX(((Month)="Dec")*Amount))*Date)}

I can successfully get the highest AMOUNT and DATE for a particular
MONTH, but modifying the second formula to get the DATE of the lowest
AMOUNT will not work. It fails because it will return the DATE of the
lowest AMOUNT for the complete year, rather than restricting it to the
month of "Dec".

Can anyone suggest an alternative that will return the DATE of the
lowest AMOUNT for a particular month?

Tks, Kay

 

[Ron Rosenfeld]

Hi - I have a list of daily $Amounts for ea day of the year.

ie --Month-- --Date-- --Amount--

In a summary table, I extract the highest AMOUNT for a particular
month with {=MAX(IF(Month="Dec",'9am'!Amount))}

I extract the DATE of the higest AMOUNT for thyat month, Dec, with
{=MAX(((Month)="Dec")*(Amount=MAX(((Month)="Dec")*Amount))*Date)}

I can successfully get the highest AMOUNT and DATE for a particular
MONTH, but modifying the second formula to get the DATE of the lowest
AMOUNT will not work. It fails because it will return the DATE of the
lowest AMOUNT for the complete year, rather than restricting it to the
month of "Dec".

Can anyone suggest an alternative that will return the DATE of the
lowest AMOUNT for a particular month?

Tks, Kay

I assume that in your DATE column you really have the Day of the month.

Using a similar format to what you have, with the Month in cell E2, try
something like this:

=MAX((Month=E2)*(Amount=MIN(IF(Month=E2,Amount)))*Date)


--ron

 

[Ron Rosenfeld]

I assume that in your DATE column you really have the Day of the month.

Using a similar format to what you have, with the Month in cell E2, try
something like this:

=MAX((Month=E2)*(Amount=MIN(IF(Month=E2,Amount)))*Date)


--ron

That formula should be array-entered (<ctrl><shift><enter>)


--ron

 

[KC]

Brilliant! Thanks so much Ron.

Kay

That formula should be array-entered (<ctrl><shift><enter>)


--ron

 

[Ron Rosenfeld]

Brilliant! Thanks so much Ron.

Kay

You're welcome. Glad it's working and thanks for the feedback.


--ron