Blank "" as number - Table sorting problem

[aaa89]

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

 

[aaa89]

Its more then just a preference - because the sum menu for custom Number filters and text filters are very different. I need the number filters.

The COUNTA doesn't give me what I want. I want the cell BLANK if any of the numbers were not entered, it should ONLY calculate after all data is filled in. I can do this =IF(COUNTBLANK(E10:H10)>0,"",SUM(E10:H10)) but it does not help.
Ill explain again - giving you all formulas of my actual sheet,

If you know anything about trading, a trade consists of a entry, exit, target prices, and lot size (shares amount). Trade can be LONG (entry price is greater than stop, target greater than entry) or SHORT is just the opposite (entry less than stop, target is less than entry.

K: select SHORT / LONG
L: Entry
M: Stop
N: Target
O: share size
All of the above can not be a negative number, its a price to buy or sell.

Now R to AB does various type of calculations - Rather then typing it all in all give you one or two examples and you should get the idea.
I did not want any of the columns R to AB do any calculations unless all necessary of the above are filled out.
R = Risk amount (difference between entry and stop price)
=IF(AND(L7:M7>0,K7="LONG"),(L7-M7),IF(AND(L7:M7>0,K7="SHORT"),M7-L7,""))
S = Total risk (risk amount times share size)
=IF(AND(L7>0,M7>0,O7>0),O7*R7,"")
U = Profit or Loss (difference between Entry and Exit) =IF(AND(L7>0,P7>0,K7="LONG"),P7-L7,IF(AND(L7>0,P7>0,K7="SHORT"),L7-P7,0))
V = Total Profit or loss (which can be negative)
=IF(AND(L7:P7>0,K7>""),U7*O7,"").
Then i have some that depend on the resulting cells example
AB - net profit or loss - =IF(V7="","",V7-AA7) [V was the profit AA is trade fees]
Many more, all the same IF AND>0 or >"".

(the next sheet I have total profit and avg profit or loss etc. Also all built only in the are greater then 0 etc.)

Now if I use the "" to leave blank I get "Sort A to Z" with text filters, if I use 0 I get "largest to smallest with numbers filters".
If all 500 rows are filled out its working fine cause they are all numbers, But if most of the table is not filled in yet it reads the "" as text.

Now I would use 0 and hide it, but if the trade is filled in and it DOES equal ZERO i want the zero displayed.

Hope now Im clear enough what I'm trying to accomplish.
Thanks so much for your time.

 

[CWatts]

That does clarify. Basically, you want to override Excel's default assumption that it is a text field due to the "" possibility. Rather you want to force it to be recognized as a number.

If that is a possibility, I have yet to find a way to do that either due to my own experience or searching the web for alternatives.

I think you're stuck with having zeros. You can can set the zero's to be conditionally hidden if your first five must-be-filled-in cells are blank. But they will still be factored in with your number filters and will be treated as zeroes.

If you can work with that, let me know and we can elaborate, but if not, I think you'll either need someone with more excel expertise than me, if changing that is even a possiblity.
Sorry.

 

[aaa89]

I was thinking I'm doing such a simple thing... Yet its so hard to get it done. This problem will be with any unfilled numbers table....

If I go with 0 - will all zeros be included on the 2nd sheets totals and averages? Thats gona mess up all that data....

Im fine either way, I just want to accomplish these 3 things. Blanks for when the row is not filled in, Zeros for when it is filled in but equals zero, yet not included the "blank cells zeros" (filled in i do want) on the other sheet.

So far, no has jumped in on how to work around it, so I am kind of stuck on how to force excel to recognize "" as numbers.

SO whatever work around you got is very appriciated and helpful.
No I'm think posible to go with your '-1000000000' (ill put 1 billion, never gona get to that...) ? or its no difference?
THanks again

 

[CWatts]

Maybe you can make an Include/Omit column in one or both of your tables. For example:

=IF(AND(OR(K2="LONG",K2="SHORT"),L2>0,M2>0,N2>0,O2>0),"Include","Omit")

Then change all your formulas to return zeros instead of "". That way you can filter out the "Omit" rows and perofrm the number filters instead of the text filters.

 

[aaa89]

Pardon me I completely lost you... Never used Omit/Include before.

Where would I put that in?

 

[CWatts]

It's not something that's built into excel. Add another column in your table, and put that formula in it. Then go to the drop down in that colum heading and and remove the check mark from 'Omit" to filter it out.

 

[aaa89]

I'll take your idea - with a twist.

If I change "" to 0, then I'll have all my data on 2nd sheet messed up cuase it will include all zeros, unless I re-write that formulas to =AverageIF(sheet:1 all-my-must fill in cells >0, - That will require excel to do alot of extra work...

I just relized I can leave it to "", go to any of the columns while is has the "Sort A to Z" uncheck 'Blanks', then all the other columns will get me the "number filters". As soon as I clear the filter it will change back.

Ill just add a column with numbers 1,2,3 - then uncheck blanks - this will hide all blanks and the other columns will adjust to number filters.

Thanks so much for your ideas! You rock!

BTW - its the first time I wrote such Arrays and IF's AND's are they done well?

I would stlll appreciate if anyone can come up with a solution - of sorting it as numbers without having to hide the blanks first, and leaving them at the bottom.

It amazes me why such a simple task should require such work around. It comes down to a basic question "How do we get number filters, on an unfilled table when trying to leave cells blank."

 

[CWatts]

For what you're trying to do all what you're doing is fine.

Excel has a few quirky things that take a ton more work than they should. (Such as this, and pivot tables.) Glad you found a way to lilmp through it. Good luck going forward.

 

[aaa89]

I don't know excel so good, so I was sure I'm missing something BASIC...
you gave me great work around ideas!
Thanks again for your time!
God bless you!