#NUM! Error in 'LARGE' Function

[masud8956]

Hi everyone!

Got a peculiar problem here.

I am getting a #NUM ! error while using LARGE(array,k) function. In certain columns it is returning values alright but in some it shows the error. All the columns where I am trying this formula are identical. I have checked for dissimilarities in cell formatting but that is not the cause. All cells are in 'General' format. Changing those in 'Number' format did not help.

What should I check for? Anyone please!

Regards.

 

[RoryA]

You'd get a #NUM error if there weren't enough numeric values in the column based on the k value. Note that checking or changing cell format does not in any way affect the cell contents. Do your numbers change appearance when you alter the cell formats? If not, they are stored as text, which could explain your error.

 

[Peter_SSs]

I am getting a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error while using LARGE(array,k) function

Another way you can get that results is if 'array' already contains a #NUM ! error (could be hidden with conditional formatting).

What does =COUNT(array) return in relation to k in =LARGE(array,k)?

 

[masud8956]

@RoryA and @Peter_SSs,

Thanks to both of you for pointing out probable reasons. Actually I messed it up as I put a blanked cell as 'k' value.

So I tried the following formula instead:

=IFERROR(LARGE(IF(ISNUMBER($H$23:$H$112),$H$23:$H$112),ROW($H$23)),"")

It worked fine for me as I have random blanked cells in the range. This formula (extracted through Google search) allows me to put it into any cell of my range.

Thanks again for your support and quick replies!

Regards

 

[joeu2004]

=IFERROR(LARGE(IF(ISNUMBER($H$23:$H$112),$H$23:$H$112),ROW($H$23)),"")
It worked fine for me

Well, IFERROR might mask the #NUM error.

But I find it hard to believe that ROW($H$23) is what you really want if there is no error.

ROW($H$23) always returns 23. If you want 23, write 23.

Otherwise, how is that related to $H$23, which is the start of the data (by coincidence?)?

If your data were in (or moved to) $H$10023:$H$10112, would you really want ROW($H$10023), which returns 10023?!

 

[masud8956]

.....If your data were in (or moved to) $H$10023:$H$10112, would you really want ROW($H$10023), which returns 10023?!

Hi @joeu2004,

Thanks for joining the discussion!

I don't think I have understood your question fully! (That's because I am not an expert in excel)

But I understood the fact that the solution I posted #4 is not the best one may be though it is working for now....I might face problem if I move the data as you mentioned.

Situation is this:

1. My data range is $H$23:$H$112. The 90 cells auto populate depending on dates corresponding to each row.
2. $H$112 populates first and then the column keeps being populated to upward direction. Thereby $H$23 gets populated at last.
3. It could be that the whole range is not populated yet because 'date' criteria not met or it could be such that the lower 80 rows are populated but the top 10 rows are yet to be.
4. So, I was struggling to decide what to put as 'k' in my =LARGE($H$23:$H$112,k) formula. I tried H23,24,25.....H112 but the formula returned #NUM ! error.

Most likely there is a very simple and elegant solution but I could not find it. May be you could enlighten me.

Regards!

 

[joeu2004]

1. My data range is $H$23:$H$112. The 90 cells auto populate depending on dates corresponding to each row.
2. $H$112 populates first and then the column keeps being populated to upward direction. Thereby $H$23 gets populated at last.
3. It could be that the whole range is not populated yet because 'date' criteria not met or it could be such that the lower 80 rows are populated but the top 10 rows are yet to be.
4. So, I was struggling to decide what to put as 'k' in my =LARGE($H$23:$H$112,k) formula. I tried H23,24,25.....H112 but the formula returned #NUM ! error.

Most likely there is a very simple and elegant solution but I could not find it. May be you could enlighten me.

I'm not sure that anyone can because I do not see an explanation of the criteria that you intend to use for choosing "k".

And it is unclear to me how that relates to how much data is in the range, much less the order in which that data is populated.

It is also not clear to me why you use $H$23:$H$112 instead of simply H23:H112. Do you intend to copy the LARGE() formula? If so, will you copy it across a row, down a column, or both? And how would your choice of "k" change for each copy, if it changes at all?

(You do write: ``All the columns where I am trying this formula are identical``. That might suggest you copy the formula across a row. But I don't know what you mean by "are identical": identical in row numbers (from 23 to 112)? In that case, perhaps the range should be H$23:H$112.

I suspect that you do not need to use LARGE() at all. And LARGE() might be wrong to use, depending on what you are really trying to do.

It would be helpful if you described in English (not Excel terms) what you are trying to determine. For example:

1. The largest numeric value in the range? Use MAX(H23:H112)
2. The smallest(!) numeric value in the range? Use MIN(H23:H112)
2. The last number value in the range? Use LOOKUP(1E+300,H23:H112)
3. The first numeric value in the range? Array-enter (press ctrl+shift+Enter) INDEX(H23:H112,MATCH(TRUE,ISNUMBER(H23:H112),0),1)
4. Something else altogether? What?

FYI, some people prefer to write 9.99999999999999E+307 instead of 1E+300. It is true that 9.9...9E+307 is the largest value that we can enter manually. But neither value is the largest value that Excel can calculate, which is about 1.79769313486232E+308. And in practice, either value is probably "large enough". So I choose to use something that is easy to write and remember: 1E+300 for me; perhaps even 1E+100 for you.

 

[masud8956]

@joeu2004,

Thanks for all the tips! I will keep them noted for future works. For my case, Option 1 was enough and I have unnecessarily complicated it trying to use LARGE function for this. Sorry for that.

Thanks a lot!