Forum Discussion
Solved
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
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!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
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!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Thanks. I didn't know about the CTRL Shift Enter. That worked perfectly
