Extracting Date of Lowest Value from List

  • Thread starter Thread starter Kay
  • Start date Start date
K

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
 
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
Click to expand...

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
 
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
Click to expand...

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


--ron
 
Back
Top