Find MAX number in range formatted at TEXT

[RobbieC]

Hi there, I know that the answer to this is staring me in the face, but I can't get my brain into gear this morning

I have a range (I182:I186) which is reflecting dates from other sheets (date formatted 20180116). This range is formatted as TEXT as the reference cells are also formatted as TEXT (generated via vba) - I cannot change this...

so I have a range which looks like:

20180110
20180101
20180108
20180116
20180109

I thought I could use:

=MAX(I182:I186) // this cell is formatted as GENERAL

to simply return the largest number, but it just returns 0, but should be returning 20180116

What am I doing wrong? My brain hurts

 

[FormR]

Hi, you could try this instead:

=AGGREGATE(14,6,0+I182:I186,1)

What am I doing wrong?

Max() ignores text values.

 

[AlanY]

try this, enter with Ctrl-Shift-Enter

=MAX((I182:I186)*1)

 

[RobbieC]

FormR, you are a STAR! Works perfectly!

What is happening here? I've never used AGGREGATE before

 

[FormR]

What is happening here? I've never used AGGREGATE before

Hi - a couple of notes:
1. Aggregate() with a function num of 14 can handle array processing without the need for CSE
2. In Excel when we attempt to perform an arithmetic operation on text, if it can, Excel will convert that text to a number and then perform the calculation

So we add a zero to each cell in the range with 0+I182:I186 which converts the text to a number without changing it to a different number. Then the arguments (14, 6) used in Aggregate() tell that function to get the largest k value (specificed in the last paramter) and to ignore any errors (just in case there is some text in the range that can't be converted to a number)

 

[RobbieC]

Just bumped into another problem with the same cells...

I want to use my 'result cell' as part of a VLOOKUP on the same range... is it not working because of the text formatting?

=VLOOKUP(AGGREGATE(14,6,0+I182:I186,1), I182:K186, 3)

it just returns #N/A (this cell is formatted GENERAL)

 

[FormR]

Try the amendments in red to convert the numerical result of the aggregate function back to text.

=VLOOKUP([COLOR=#ff0000][B]""&[/B][/COLOR]AGGREGATE(14,6,0+I182:I186,1), I182:K186, 3)

 

[RobbieC]

Awesome!!! You are the man!

Thanks FormR

 

[RobbieC]

Hi there FormR, sorry to bother you again with this problem, but I have just noticed that there is an error somewhere and I cannot find it for the life of me...

I have simulated the problem in a seperate workbook and uploaded it to my server: http://www.bluecustard.co.uk/AGGREGATE issue.xlsm

The top left box contains the rawdates (formatted as text) and beneath is a copy for 'copy & paste'

It seems to work fine when date 1 or 2 are lower values (earlier dates), but as soon as they are higher, the result of the VLOOKUP defaults to 5, even though the aggregate value is showing correctly....

Can you see where I am going wrong? this has bee driving me bonkers. I've treble-checked the formatting of all the boxes and I cannot see anything out of place

I really appreciate your help FormR

Regards

Rob

 

[FormR]

Can you see where I am going wrong?

Hi, for an exact match you need to set the "Range_Lookup" paramater of the VLOOKUP() to False or Zero - i.e. change the D11 formula to:

=VLOOKUP(D9,D4:F8, 3,0)