asked on

MIN per Month

I had this question after viewing Formula help - MIN.

Hello Experts,

I need a formula that will show the MIN per month as shown in the excel file.
Please open the attachment.

thank you.
EE_after2.xlsx

Subodh Tiwari (Neeraj)

Try this Array Formula....
In B20

=MIN(IF(MONTH($B$1:$BO$1)=MONTH(B$18),$B$2:$BO$2))

Open in new window

Confirm with Ctrl+Shift+Enter and then copy across.
EE_after2.xlsx

Shums Faruk

Another approach would be with Cntrl+Shift+Enter

=ROUND(SMALL(IF(MONTH(B1:BO1)=MONTH(B18),B2:BO2),1),0)

Open in new window

EE_after2_V2.xlsx

pdvsa

ASKER

HI thanks for th eresponse. I might need to tweak it a bit. If the dates extend over a few years then I think I will run into an issue with MONTH because it will refer to the wrong month. I think I would need to add a YEAR in addition to the MONTH? Not sure if you follow me.

let me know what you think.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

If you transpose them from rows to column, it would be much easier without Formula, Pivot will do the calculation by itself.

Please see attached.
EE_after2_V3.xlsx

pdvsa

ASKER

perfect. YOu are good. thank you once again.

pdvsa

ASKER

shums, thanks for the fyi. I didn't think about that.

Subodh Tiwari (Neeraj)

You're welcome. Glad it worked.
Thanks for the feedback.