Array Formula and Text

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
Hello all,

I have the following array formula:
Code:
={MAX(IF(PO!$AJ$13:$AJ$10000=$T410,IF(PO!$BO$13:$BO$10000=$U$3,PO!$BQ$13:$BQ$10000)))}
which works great.

What I'd like to do is check if any of the entries in sheet PO, column BQ (that match the two criteria) are in text format, and if so do some work on that.

Basically, if a due date is this year, the week number (e.g. we are now in week 48) is used and so I can work out the latest (or MAX) number using the above. However, if the due date is next year, it is entered as text such as 02/12 for week 2. Such as:
Code:
=IF(AND(ISTEXT(BQ13),RIGHT(BQ13,2)=12[COLOR=blue]),...MAX of these fields...,MAX of the numbers[/COLOR])
but as part of the array, similar to above.

I'm not even sure if this is possible, but I'm sure you guys will let me know either way - I look forward to your help. Much appreciated in advance.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

=RIGHT(MAX(IF(PO!$AJ$13:$AJ$10000=$T410,IF(PO!$BO$13:$BO$10000=$U$3,IF(ISTEXT(PO!$BQ$13:$BQ$10000),(RIGHT(PO!$BQ$13:$BQ$10000,2)&LEFT(PO!$BQ$13:$BQ$10000,2))+0,("11"&PO!$BQ$13:$BQ$10000)+0)))),2)
 

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
So fast! thank you Andrew - I'm going to try this right now.

You're a star, as always.
 

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
Taking my cue from your suggestion, I have added a column to the sheet PO, creating a new serial number.

=IF(ISTEXT(BQ13),VALUE(RIGHT(BQ13,2)&LEFT(BQ13,2)),VALUE(RIGHT(YEAR(NOW()),2)&TEXT(BQ13,"00")))

I can then find the MAX of that and convert back into the correct format. Not nearly as elegant...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top