Sorting formula cell - not reading as numbers

[aaa89]

Hi all - I set few columns A:1 to Q:1 where I enter data, prices, volume, etc. - R:1 to AB:1 are all formulas to return P&L, % Sales, etc.
All formulas are IF's and AND's... to avoid showing DIV/0 errors etc.
Example =IF(AND(L11:M11>0,K11="buy"),(L11-M11),IF(AND(L11:M11>0,K11="sell"),M11-L11,""))

I then formatted the whole thing as a table. When trying to sort, all columns that I manually enter the data works great - the sort adjusts to text and number (A-Z / Largest to smallest). My problem is that all "formula cells" although they are numbers, when I click to sort I get "Sort A to Z" and "Text filters".
I tried to recreate a similar sheet using a table and same formulas and everything works fine.
I also tried to re-write the formula of one column after I created the table so the formula would read Total P/L]]="","",Table3[[#This Row],[GrossTotal P/L]]-Table3[[#This Row],[ Fees]]), no luck.

I'm not sure where the problem lies, that's why I'm posting the order how I created the sheet.
For some reason excel isn't reading it as numbers.
Any help would be appreciated. I googled but no luck...

 

[taigovinda]

I wonder if an Excel *table* doesn't like the fact that you've mixed strings and numbers. What if in your formula you replace the "" with a zero? I haven't used the "table" format much at all, but hoping having only numbers in that column may help.

 

[aaa89]

Thanks for your reply.
Interesting point - I changed it. It Helped! But I want the cell to be BLANK not "0", how would I work around that?

But this is strang.... I tried reducing the table from $B$6:$AB$406 to $B$6:$AB$15 and left it "" (the way it was) and all works fine, as I pull down the table one more row to include row 16 it changes to "Sort A to Z", as I drag it back to row 15 it fixes and changes back to "sort largest to..."
Any idea?

 

[taigovinda]

Are the top 15 values all numerical? That would explain this behavior, even though there was a possibility they could've been strings but they were not...

If you're not worried about other numbers being exactly zero then you could use a custom format to not show zeroes (right-click the column, format cells->custom and type in #,##0;[Red](#,##0);;@ or whatever you want... the first term is for positive numbers, the second for negative, the third zeroes and the forth text, so this would show positive numbers, negatives in parentheses, zeroes not at all and text like nothing).

 

[aaa89]

Man looks like you got it figured out! MOST of the row untill 15 were filled out, so it was showing correctly as I expanded the table more empty 'strings' came in, and looks like it took over and changed it. Just to test this - I dragged down to fill in the data for the table up to 400 rows - while it was all filled out with number everthing worked - as I started deleted entries bottom up, as soon as I had less entries than filled rows it got messed...
How in the world had you figured this one out...?

Now Ill just need some help with the formatting. Never played with that one before. Im not sure when you meant with "If you're not worried about other numbers being exactly zero" - which numbers?

When I want is - EMPTY untill that rows date is entered, then "$sum" in green for for positive, "-$sum" in reb for negetive (not '($sum)'), and when the total equals zero $0.00 in black.
Thanks so so much! I was going crazy - interesting that I didnt find this is any forums...

 

[aaa89]

I played around with the format settings, its cool. Here is my problem - if I leave the zero value section to ;; it will always leave the cell blank. I want it to display a zero for when the data in that row is entered, but it equals zero - but leave it blank if it's a zero cause the row's data was not entered. That's why I initially used the "" instead of 0. Any idea? Thanks!!

 

[taigovinda]

Looks like the trail might be a bit cold on this one but...

I was wondering if having the zeroes might be an issue, if you were maybe going to take averages of the numbers or something. If you aren't aggregating them but just displaying the numbers, then you could have your non-values be something else. Instead of zero (which you want to show up when it occurs), you could have it be something that will never show up. The most convenient would be if the numbers are never negative. Then you could use something like #,##0;;0;@ which would hide the negatives only. Or you could make it a really huge or really small number; there's a way to make Excel format numbers under/over certain values however you want instead of always sticking to the pos/neg/zero/text thresholds.... I forget how but if negatives don't work then you or I can search for how to do it. Custom number format custom thresholds.

 

[aaa89]

I am taking averages from these columns and setting them to any 'value' would mess up my other sheet. I need blanks but no values.

I think im trying to do something so simple... not sure why excels makes it so hard.... this problem would happen with any table not filled in, if you want to apply the numbers filters - and leave it blank, cause a value will mess it other data.