Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Min Month

Hello,

How could I show a MIN for the month in the attached?

example:  (copy paste)
Date      8/1/2016      8/2/2016      8/3/2016      8/4/2016      8/5/2016
Ratio      0.9166          0.8706          0.8217          2.9937          2.3481
Min Month:                              
this extends out many days.
MinMonthDebtCash.xlsx
Avatar of D Patel
D Patel
Flag of India image

Use the following formula to find out the same :

(Only when range is fixed)
=INDEX($B$3:$KV$3,MATCH(MIN(B4:KV4),B4:KV4,0))

or

(Use when ranges are dynamic)
=INDEX($3:$3,MATCH(MIN($4:$4),$4:$4,0))
Avatar of pdvsa

ASKER

HI Patel, that doesnt seem to be correct.  For August, the MIN is 71%

let me know what you think..
Ohh,

You mean, You want monthwise minimum value. Right?
Avatar of pdvsa

ASKER

Yes, that is correct
I have picked the minimum out of full range.
Avatar of pdvsa

ASKER

how about per month?
Avatar of pdvsa

ASKER

helper cell is OK.
Avatar of Glenn Ray
Use this array function (enter with [Shift]+[Ctrl]+[Enter] in cell B5 and then copy across to the right
=MIN(OFFSET($A$3,1,MATCH(MONTH(B3),MONTH($B$3:$KV$3),0),1,DAY(EOMONTH(B3,0))))

This will show the minimum "debt to cash" value for that month only.  Note that only works if a month isn't repeated again (ex. 8/2017).

Regards,
Glenn
EE-MinMonthDebtCash.xlsx
File after fetching Date value from the @Glenn Ray's Excel Sheet
EE-MinMonthDebtCash.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

ASKER

nice.  I need to ask a follow up though.  will post another quesiton