Max loss by month

[Lewis Koh]

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.

[darkyam]

Please Login or Register  to view this content.

confirmed with Control+Shift+Enter

[Lewis Koh]

Hi, thanks! It works!!

[Lewis Koh]

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.

[darkyam]

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.

[Lewis Koh]

Hi Darkyam,

Yup, you are right. There are data below my under the Column J after the P&L.

By doing the multiply paste special, is it the same as changing all my IF to 1*IF in all the P&L under column J? I did try both ways but I still get #NA. I also notice my conditional formatting are gone. Did I do it wrongly?

I have attached my sample here with the suggested solution.

[darkyam]

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.

[Lewis Koh]

alright!!! It works! I used method 3!. Thanks!! :-)

[Lewis Koh]

Hi,

I'm not sure if I should open a new thread or not. It's a follow up here. The old excel that I am using, when saving in excel, it says there is incompatibility issue with the cell that contains

=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)))

The message given is

"Some array formulas in this workbook refer to an entire column. In earlier versions of Excel, these formulas may be converted to #NUM! errors when they are recalculated."

How should I change it to make it compatible?

[DonkeyOte]

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:

Please Login or Register  to view this content.

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]

[Lewis Koh]

Hi,

Thanks. It's working now :-0

[Lewis Koh]

oh sorry. I haven't save. the zero was solved but the compatibility error still occurs in excel 2007. Attached is my error report.

[DonkeyOte]

@Lewis Koh - repeating a point made in an earlier post:

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)

I draw your attention to the word "may".

Does Feb!E5 work ? Yes.

[Lewis Koh]

Hi, yes FebE5 works. I understood the "may". So the compat error is inevitable?

[DonkeyOte]

It is as implied just a warning - and is generated simply because within the Array Excel sees an entire column reference (B:B) - it does not try to establish context of reference hence "may".

The formula here is backwards compatible as the resulting precedent ranges will never refer to columns in their entirety (start point is row 5)

[Lewis Koh]

Hi, thanks. I guess I'll just ignore it then.