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
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try (even if month is repeated)

=MIN(IF((DATE(YEAR($B$3:$KV$3),MONTH($B3:$KV$3)+1,0)=EOMONTH(B3,0))*($B$4:$KV$4),($B$4:$KV$4)))

Open in new window

Regards
EE-MinMonthDebtCashV1.xlsx
0
 
D PatelD Patel, Software EngineerCommented:
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))
0
 
pdvsaProject financeAuthor Commented:
HI Patel, that doesnt seem to be correct.  For August, the MIN is 71%

let me know what you think..
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
D PatelD Patel, Software EngineerCommented:
Ohh,

You mean, You want monthwise minimum value. Right?
0
 
pdvsaProject financeAuthor Commented:
Yes, that is correct
0
 
D PatelD Patel, Software EngineerCommented:
I have picked the minimum out of full range.
0
 
pdvsaProject financeAuthor Commented:
how about per month?
0
 
pdvsaProject financeAuthor Commented:
helper cell is OK.
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
 
D PatelD Patel, Software EngineerCommented:
File after fetching Date value from the @Glenn Ray's Excel Sheet
EE-MinMonthDebtCash.xlsx
0
 
pdvsaProject financeAuthor Commented:
nice.  I need to ask a follow up though.  will post another quesiton
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.