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

 

[CWatts]

Instead of setting it as "", set it to -10000, and use a custom number format like this:

[<-9999]"";-#;0;""

That way, it will sort the value as if it was -10000, so it will be at the end, but it won't display anything.

If you could provode more info as to where in your sort you want these to show up, then we could help you modify it a bit more. Hope that helps.

 

[aaa89]

Thanks so much for your reply. It works, but if you can elaborate more so I'll understand what I just did if i need to modify it a bit.

What does the '-10000' do?

Also the custom format I have not played around to much yet with it, I read that its divided into four sections positive ; negative; zero; text;.
I copied what you typed and it works, but I got no clue what I did...
Would appreciate if you can break it down for me.

End results I want '$0.00' in green for positive, '-$0.00' in red for negative, '$0.00' in blank for zero, combined with this 'trick' of -10000 you mentioned.

I know how to do the basic, like [green]$#,##0;[red]-$#,##0;;@ .
Many thanks!

 

[CWatts]

For what you're trying to do specifically, I think you're better off using regular number formatting for the numbers, and then a conditional format to override the typical number formats.

For example, set your number formats as follows: [Green]$0.00;[Red]-$0.00;$0.00,""

Then create a new conditoinal formatting rule based on the formula: =B2<=-10000

And format the number as: "";"";"";""

That way the cells themselves will have the straight up color coding with no tricks, and the conditional formatting will format everything less than or equal to -10000 as blanks "".

As for how my original number format worked, it basically did it's own condition test, but for what you're looking for, it will be crazy to maintain, and I coudln't figure out how get the two to merge in the number format.

 

[aaa89]

Sorry I lost you... Im not so good with excel...

First will the "-10000" get me wrong results if the value will equal greater negative then that? I have no clue what this "-10000" does, can you please explain it?

Just to make sure I got you correct;
"For example, set your number formats as follows: [Green]$0.00;[Red]-$0.00;$0.00,""
This ill do under the custom format?

"""Then create a new conditoinal formatting rule based on the formula: =B2<=-10000
And format the number as: "";"";"";"""""
Did you mean new rule> Format values where this is true>,then format(within the new rule)> custom as "";"";"";"""""

"""That way the cells themselves will have the straight up color coding with no tricks, and the conditional formatting will format everything less than or equal to -10000 as blanks "".""

Whats the difference if the conditional formatting does the coloring or the custom format? Is it easier for the file?

What would happen if the value does equal less than 10000 (which i might have) would the cell not show the value?

Thanks for your time

 

[CWatts]

Ok. The challenge I'm having is that I have a general understanding of what you want to do, but I don't know the specific methodology of how you want to do it.

I'm operating under this assumption: you have a set of data, that will fluctuate in size, so you're building your worksheet so that it can accommodate a certain number of items. If there are less, then you're wanting blanks displayed, but the blanks are throwing off y our sorting.

You still haven't answered the question of how you want these handled so I've continued guessing. The purpose of the -10000 was to select a number that would never show up in your data set. Now that I know you may have a number in your real data that could be less than -10000, you'll need to chagne it as it would hide that data.

If you were tracking lemonade stands, -10000 wold probably work, but for larger financial info, you'll have to pick -1000000000000000 or something like that.

For my comment of Format your cells as [Green]$0.00;[Red]-$0.00;$0.00,"" i'm referring to highlighting your cells, right click, format cells, and change the number format to custom and paste that info in.

For the "create conditional formatting" yes, I am referring to creating a new rule based on a formula. and in that new rule, only set the number format to the "";"";"";"" but just make sure that your number matches whatever number you've chosen.

There's really no difference between conditional formatting and regular formatting except for the fact that conditional formatting will override normal cell formatting. Instead of trying to cram everything in the custom number format for the color codes as well as the value tests, it's easier to just set up number rules with green/red etc, and then use the conditional format as sort of a blanket statement to override those if a specific condition is met.

Without specifically knowing what you're trying to do with the data, where you want it to show up, and what the ranges are going to be, I can only give you suggestions based on assumptions. Any additional details about your requirements will only help.

 

[aaa89]

Thanks again for your reply!

lol I'm laughing at myself about the "-10000", nice idea!
You guessed very good what I'm trying to do, I'll ad the details! I'm working on a stock portfolio where I'll log trades. So this is this the setup L:Q is where the buying, selling, target, stop, etc. prices will be entered, and R:AB will do some math, calculate profit / loss, risk reward ratio etc.
To avoid getting DIV/0 or that data should start calculating before all trade details are entered I wrote all formulas with IF and AND (if(L5:M5>0, then... ,"") tats where I used the blanks. Now I want to be able to sort by each column as any table, that's where I ran into my issue.

I also have on the next sheet to get the avg gain, total profits etc.
now I have to be careful that the 'empty' cells should not be a vaule that is not showen cause it might mess up the totals/ avg's on the other sheet. (unless when it's formated to be empty it won't be included).
I hope this will help you help me...
thanks again

 

[CWatts]

If you're using excel 2007, have you tried putting your data in "Table" format? Also, have you tried using auto filter?

Whenever I sort cells with formulas and blank results, it always puts them at the end and leaves my cells with values up top so i can't help but wonder why yours is acting so strange.

Could you share any portion of your data, or at least the structure of the table you're trying to work with along with the "rules" for each column?

 

[aaa89]

I'm using excel 2007 and 2010!
I'm not in office now I can post it soon, but 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 in 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.*Let me know if this explains it to re create the problem I'm having, if not I'll post the actual formula I'm using.

 

[CWatts]

It helps a little. To reiterate, is your concern not so much the funcionality, but rather the preference of it saying Largest/Smallest than A/Z.

I'm having difficulty getting your array formula to work as intended and I had thought you said you could have negative numbers. I'd suggest replacing your array formula with =IF(COUNTA(D24:F24)>0,SUM(D24:F24),"") for the time being.

Also, are the values in column G used anywhere in a calculation?