Blank "" as number - Table sorting problem

Question

Hi all - I need help figuring out how to leave cells blank yet excel should treat it as a number. 

I have a table columns D:G is where numbers will be entered, and H {=IF(D5:G5>0,SUM(D5:G5),"")}, my problem with this is that the sort on column H is "Sort A to Z" instead of "Sort largest to smallest" - because excel refers to "" as text, (I tested this with =ISTEXT) so as long as most rows are not filled in with numbers excel will mistake that column to be text.

I could change the formula to, 0), and then in the custom format hide the zeros, but my problem is, if the row is filled in but the total equals to zero it will not show the zero, and I do want the zero in that situation. 

I can think of three possible ideas, but not sure how and if its possible to do. 

1. To leave formula as it, but change the sort to as if it was numbers and setup excel should not change it back.
2. Change the formula to, 0 and figure out a way how to differentiate if the row equals zero or its empty.
3. Re-write the formula so it's blank as a number?

Any help would be greatly appreciated.

Thanks

Answer 1

Just as an example - Create a table D:F numbers will be entered, and G {=IF(D24:F24>0,SUM(D24:F24),"")}    then format as table. 

Now if most of the table rows are filled it with numbers - then the G sort will show correctly "Largest to smallest", but if most of the tables rows are still empty - the sort in G will show "A to Z". The reason for that is cause the empty cells in G , excels reads them as "text" (do a =ISTEXT test). Now I got a table of 500 rows and i want to be able to sort as soon as a few rows are filled in.

Any work around?

Answer 2

Please describe how you are creating this worksheet. I just tried to recreate the problem and was not able to do it.

I entered some random numbers, including spaces and text char in the entries. I tried setting the cell format to numbers and text. Still no change.

All I can suggest is take a look at custom sorts. Maybe you can force the type of sort you want that way.

Answer 3

I'm new to these forums wasn't aware of that. So far even that thread didnt resolve it YET...

Answer 4

I notice that you've posted the same question in another forum in this thread:

http://www.mrexcel.com/forum/showthread.php?t=574153

You'll find that most of the top Excel talent visit the popular forums. Consequently, you really don't gain much additional exposure by posting the same question to multiple forums. However, if you feel the need to multi-post, it's polite to post a link to the other forum(s) so we can check the progress in them.  That way we'll know to stop spending any more time on solved issues. It appears that you are well on your way to a solution at your post at the above link.