Forum Discussion
Hi - requiring assistance, much appreciated!
Hi,
I have a column (N) with some sums of the totals of a few other columns (O, P, Q) that contains V Lookups.
Some cells in column N add up to the respective sums in columns o, p, and q but other cells in column N amount to a #N/A figure (as not all cells in columns o, p, and q) return a value from other columns.
When I go to sort by largest number on column N the #N/A appears first, with the largest numbers underneath it... How can I fix this?
Many thanks in advance.
A
As is always true in Excel, there are many routes from Point A to Point B. How can you fix the situation so that #NA doesn't appear at the top of the sort?
One way, and only one way, would be to have whatever the formula is in column N wrapped in an IFERROR function, like this
=IFERROR(SUM(O2,P2,Q2),0)
which will cause a zero to appear instead of #NA. That's one way.
A better way (IMHO) would be to fix the formulas in columns O, P & Q so they're returning a value (possibly zero) rather than whatever it is that they're returning. In general, you want to fix a problem as close to the source as possible, rather than just putting a bandaid on a gaping sore,
It all has to do with what would be delivering a result with the highest integrity/reliability.
3 Replies
As is always true in Excel, there are many routes from Point A to Point B. How can you fix the situation so that #NA doesn't appear at the top of the sort?
One way, and only one way, would be to have whatever the formula is in column N wrapped in an IFERROR function, like this
=IFERROR(SUM(O2,P2,Q2),0)
which will cause a zero to appear instead of #NA. That's one way.
A better way (IMHO) would be to fix the formulas in columns O, P & Q so they're returning a value (possibly zero) rather than whatever it is that they're returning. In general, you want to fix a problem as close to the source as possible, rather than just putting a bandaid on a gaping sore,
It all has to do with what would be delivering a result with the highest integrity/reliability.
- You're amazing thank you!
I've come up with a workaround to wrap columns o,p,q. Appreciated!- You're welcome.
