Max loss by month

Hi,

Attached is a sample. I have column B for different dates and column J for Profit and Loss.
How do I find the maximum loss for a particular month? Column B will increase over time until end of year.

In my sample, I have a main data with 2 tabs, one for Feb and one for Mar. In the month tabs, C1 will select the different months I'm looking at.

=MIN((MONTH(Main!B5:INDEX(Main!B:B,MATCH(99^99,Main!B:B)))=MONTH(1&C1))*Main!J5:INDEX(Main!J:J,MATCH(99^99,Main!J:J)))

confirmed with Control+Shift+Enter

Hi darkyam,

what does 99^99 do in the formula? The formula works in my test.xls but when I plugged it into my actual spreadsheet, and after changing the reference columns and rows, I got #NA.

99^99 is simply a very large number. The purpose of the Match is to lookup the last value in the column and it's very unlikely a number used will be larger than that. If the data in your actual spreadsheet is either formatted as text or if there is data underneath it, it won't work. The first problem is easy to fix (and seems more likely): put a 1 in a cell, then copy that cell. Highlight your ranges that you want to convert to numbers and paste special -> multiply.

If it's the latter case, you might have to adjust the ranges manually or, if the table is of a fixed length (i.e., lots of blank rows for now that will be filled in), then you can adjust the range that way.

The issue is the numbers under column J. Since the Match is looking up the last value in J and that's on a lower row than the last value in B, you get differing data ranges. You have three options:
1. Move the data under J somewhere else.
2. Adjust the ranges manually.
3. Change the last Match to read off B:B instead of J:J. Note that any data put under B might then throw off your calculations or result in an error, depending on what was in that data and the corresponding value in J.

I don't see any change to your conditional formatting. It's working for me.

I didn't bother checking the first time whether your data was from formulas or not. For J, since it is a formula, adding 0 or multiplying by 1 makes no difference, since any formula will try to coerce a number stored as text into a number. For the dates, it might have made a difference, but if this is your real spreadsheet, it appears Excel is reading those correctly as numbers.

I suspect that formula will be ok... here the entire column ref. is used to create a range and that element should function without issue.
(note the *may* in the warning - if in doubt test)

However, what I would say is the formula is a little odd - unless all the Months match the criteria the result would always be 0 (assuming J > 0 - not checked)

You might find you actually want to use:

=MIN(IF(MONTH(Main!B5:INDEX(Main!B:B,MATCH(99^99,Main!B:B)))=MONTH(1&C1),Main!J5:INDEX(Main!J:J,MATCH(99^99,Main!B:B)))
confirmed with CTRL + SHIFT + ENTER

note I also reverted the final match range to B so as to ensure both ranges share the same dimensions irrespective of content in J [edit: this is a point made by darkyam in an earlier post]