SOLVED

LARGE Fxn formula and #NUM! errors

Copper Contributor

LARGE Fxn formula and #NUM! errors

Hi

=LARGE(M3:BE3,{1,2,3,4,5}) is pulling out the 5 top values I need to calculate high point awards.

But if less than 5 values then get #NUM! error and SUM then doesn't work.

I tried to use IFERROR to add blanks but doing it wrong.

Help

3 Replies
best response confirmed by Nemera (Copper Contributor)
Solution

Re: LARGE Fxn formula and #NUM! errors

You can use the IFERROR function along with an array formula to handle situations where there are fewer than 5 values in the range.

Here is an example formula:

=IFERROR(LARGE(M3:BE3,{1,2,3,4,5}),"")

This formula will return an empty string ("") in case of an error, such as when there are not enough values in the range. You can then use this formula in your subsequent calculations without causing errors.

Remember to enter this formula as an array formula since you are using an array constant in the LARGE function. To do this, after typing the formula, instead of pressing Enter, press Ctrl + Shift + Enter. If done correctly, Excel will surround your formula with curly braces {} to indicate that it's an array formula. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Re: LARGE Fxn formula and #NUM! errors

Thanks. I didn't know about the CTRL Shift Enter. That worked perfectly

Re: LARGE Fxn formula and #NUM! errors

yw
1 best response

Accepted Solutions
best response confirmed by Nemera (Copper Contributor)
Solution

Re: LARGE Fxn formula and #NUM! errors

You can use the IFERROR function along with an array formula to handle situations where there are fewer than 5 values in the range.

Here is an example formula:

=IFERROR(LARGE(M3:BE3,{1,2,3,4,5}),"")

This formula will return an empty string ("") in case of an error, such as when there are not enough values in the range. You can then use this formula in your subsequent calculations without causing errors.

Remember to enter this formula as an array formula since you are using an array constant in the LARGE function. To do this, after typing the formula, instead of pressing Enter, press Ctrl + Shift + Enter. If done correctly, Excel will surround your formula with curly braces {} to indicate that it's an array formula. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!