SOLVED

Hi - requiring assistance, much appreciated!

%3CLINGO-SUB%20id%3D%22lingo-sub-3339720%22%20slang%3D%22en-US%22%3EHi%20-%20requiring%20assistance%2C%20much%20appreciated!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339720%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20(N)%20with%20some%20sums%20of%20the%20totals%20of%20a%20few%20other%20columns%20(O%2C%20P%2C%20Q)%20that%20contains%20V%20Lookups.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20cells%20in%20column%20N%20add%20up%20to%20the%20respective%20sums%20in%20columns%20o%2C%20p%2C%20and%20q%20but%20other%20cells%20in%20column%20N%20amount%20to%20a%20%23N%2FA%20figure%20(as%20not%20all%20cells%20in%20columns%20o%2C%20p%2C%20and%20q)%20return%20a%20value%20from%20other%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20go%20to%20sort%20by%20largest%20number%20on%20column%20N%20the%20%23N%2FA%20appears%20first%2C%20with%20the%20largest%20numbers%20underneath%20it...%20How%20can%20I%20fix%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance.%3C%2FP%3E%3CP%3EA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3339720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339778%22%20slang%3D%22en-US%22%3ERe%3A%20Hi%20-%20requiring%20assistance%2C%20much%20appreciated!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1341687%22%20target%3D%22_blank%22%3E%40HelpandImproveAP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20is%20always%20true%20in%20Excel%2C%20there%20are%20many%20routes%20from%20Point%20A%20to%20Point%20B.%20%3CEM%3EHow%20can%20you%20fix%20the%20situation%20so%20that%20%23NA%20doesn't%20appear%20at%20the%20top%20of%20the%20sort%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%2C%20and%20only%20one%20way%2C%20would%20be%20to%20have%20whatever%20the%20formula%20is%20in%20column%20N%20wrapped%20in%20an%20IFERROR%20function%2C%20like%20this%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(SUM(O2%2CP2%2CQ2)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhich%20will%20cause%20a%20zero%20to%20appear%20instead%20of%20%23NA.%20That's%20one%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20better%20way%20(IMHO)%20would%20be%20to%20fix%20the%20formulas%20in%20columns%20O%2C%20P%20%26amp%3B%20Q%20so%20they're%20returning%20a%20value%20(possibly%20zero)%20rather%20than%20whatever%20it%20is%20that%20they're%20returning.%20In%20general%2C%20you%20want%20to%20fix%20a%20problem%20as%20close%20to%20the%20source%20as%20possible%2C%20rather%20than%20just%20putting%20a%20bandaid%20on%20a%20gaping%20sore%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20all%20has%20to%20do%20with%20what%20would%20be%20delivering%20a%20result%20with%20the%20highest%20integrity%2Freliability.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339949%22%20slang%3D%22en-US%22%3ERe%3A%20Hi%20-%20requiring%20assistance%2C%20much%20appreciated!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339949%22%20slang%3D%22en-US%22%3EYou're%20amazing%20thank%20you!%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20come%20up%20with%20a%20workaround%20to%20wrap%20columns%20o%2Cp%2Cq.%20Appreciated!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3339955%22%20slang%3D%22en-US%22%3ERe%3A%20Hi%20-%20requiring%20assistance%2C%20much%20appreciated!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3339955%22%20slang%3D%22en-US%22%3EYou're%20welcome.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@HelpandImproveAP 

 

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.