A bit more on Icon Sets – answers to Biff’s and Harlan’s questions

"that is not how I suspect that most people will use Icon Sets in the wild"

Me neither. Have you SEEN what goes on out there? I expect much worse. And if what I've seen with charts is any guide, they'll soon be asking for Icon Sets with 3D effects.

- Jon Peltier

Here's one I've always wondered:  Why is conditional formatting handled as such an extrinsic part of the formula building process?  Numbers can be manually formatted via the TEXT() fuction, why can't character formatting (beyond the basic colors that can be set as part of a text format)?

Once cells could hold mixed character formats it seemed natural to add a FORMAT(text,format) function.  This would make it possible to do conditional formatting of, say, words within dynamic text formulas.

A1: Cow

A2: "The "&format(A1,"bold")&" jumped over the moon."

Now, if I wanted to go crazy I'd say there should also be a way to query another cell's format (the format directly applied to the cell) and use that as input into the FORMAT() function.  This way you could formulaically ensure formats stay consistent by saying:

B2: =FORMAT(B1*10,GETFORMAT(B1))

Obviously GETFORMAT would return a numeric value with more complexity than a single text string; some use of a bitmask would make sense and allow formatting to be constructed formulaically.  This would offer an easy way (human-readable text string) and a flexible way (some complicated number) to do the same thing.

This all seems so useful, and fits so well within the Excel operational model, that I'm certain it was considered and rejected.

Why?

Formulaic formatting would be an incredible help.  

"I expect much worse."

I agree.

I like Bryan's idea.

<<

in re Bryan's idea,

Excel's TEXT function returns strings. In a way, it's happenstance that it's related to formatting. But getting back to the fact that TEXT returns strings, TEXT affects the cell by changing its PLAIN TEXT contents. The mooted FORMAT functions wouldn't affect the cell's plain text contents. So it could screw up recalculation if implemented as a function. In other words, Excel would need to 'recalc' formatting for cell Z88 containing the formula

="foo"&FORMAT("bar",X99)

when X99 changes, but the Z88's Value property would be unchanged, so other cells depending on Z88 shouldn't be recalced. In other words, you'd need both value and formatting recalc trees. Not pretty. And it'd get much uglier if you're also using udfs to fetch formatting info from cells. Then recalculated formatting could affect recalculated values, which in turn could affect recalculated values, . . ., i.e., you could generate nasty circular recalc loops.

Also ambiguous. Would FORMAT always return strings, like TEXT does? If so, it couldn't be used to format values that should be treated as numbers. That means no different colors or font properties for, say, currency symbol or units. If not, then which wins, the FORMAT function's format or the cell's formatting set using the menu/ribbon?

An alternative would be adding a display interpretation layer. It'd only work with formulas' results as text, but those plain text results could include standard HTML formatting tags, and the added display interpretation layer would format based on those formatting tags. This would handle everything as text strings, so numbers would still be one format (the cell's) applies to the entire displayed result, but that could be addressed by doing all calculations in one set of worksheets, and display exhibits or reports on other worksheets using formatting tags concatenated with cell values from the calculation worksheets.

to Colin,

While Bryan's idea has problems, multiple conditions isn't one of them.

="foo"&FORMAT("bar",IF(test1,"Bold",IF(test2,"Italic",IF(test3,"Underline")))

"those plain text results could include standard HTML formatting tags"

I like that idea even more and it seems it would be easier to implement rather than using a Format() function.

<<

Colin, if you mean by a 'better one' a more complex one, what are you trying to show? If multiple true test conditions should set different aspects of the format, what are you trying to achieve?

If there's a well-defined way to express conditions and associated formatting through a dialog, then if there are formatting codes that could do the same thing, concatenated and/or nested IF calls can duplicate that formatting. That is, there's no way to express logical relationships in a conditional formatting dialog that can't be duplicated by formulas.

Colin:

> The existing method of conditional formatting lends itself well to additional conditional formats

Agreed. No vote here for replacing conditional formatting, merely augmenting it. I can sort formulaically, or via Data->Sort. I can filter via database formulas, or choose Data->Advanced Filter. Even more on point, I can insert a hyperlink via the HYPERLINK(link, friendly text) function, or via Insert->Hyperlink. Each has its place.

>  ...icon sets, heat maps and all the other new format options added in Excel 2007. How would you specify these formats, multiple conditions, format order, etc in a formula?

I wouldn't for some of those you specify.  They're /cell/ formats, not /text/ formats.  It doesn't make sense to have, say, multiple border formats on a single cell.  That's what Conditional Formatting is for.[1]

But what if you want to conditionally format only one piece of text?  One number?  What do you do?

Also, with conditional formatting, I can be looking at a cell and see it's bold.  But the toolbar button doesn't have a highlighted B icon.  Format Cells doesn't show it's bold.  Why not? (a novice might ask). Isn't this also a problem?

Harlan:

> Circular recalcs

But why is the problem different than numeric recalc?  They're just formulas, and resolving circular formatting requires the same effort as resolving a circular calculation. The cell reference is right there, the auditing tools will still work, select precedents/dependents will still work.

That's what's nice about formulas: you can see why something is happening at a glance.  It's not "invisible" unless you happen to have the right dialog box open.

> Recalc trees

I imagine that reading and setting formatting is currently less optimized than reading and setting numeric cell values.  However, the number of formatting dependencies (and frequency of use) will likely be far less than the number of calculation dependencies.

If a user complained that having 800 cross-linked formatting dependencies makes a sheet take 10 minutes to calc, I'd say "don't do that."  Either it's so important that the 10 minutes should be worth it, or they're doing something horribly horribly wrong.

That being said, having an option to calculate formatting separately (as with Data Tables [Tools->Options->Calculation->Automatic except tables]) would be a nice to have.

Of course, the CELL() function already returns number formats, so some coding to extract formats and place it into function returns already exists.

> Would FORMAT always return strings, like TEXT does?

Good question, and I'm of two minds. Returning text (or even requiring text input, such that numbers must be TEXT()ified) would certainly be easier to implement. It would be better than nothing.

However, enabling it to work on text and numbers would be really nice. I can think of many places it would be useful, and since I'm not the one coding it  I'd prefer it this way.

> which wins, the FORMAT function's format or the cell's formatting set using the menu/ribbon?

FORMAT.  Easy.  Explicit formulaic settings always override implicit formatting from the ribbon.

Note that this means the Ribbon now sets "default" cell formatting, and the FORMAT function could be used to override it as part of a conditional.  This is a good thing.  Such a good thing, in fact, that years ago Excel devs tried to build that functionality into the sadly overloaded custom number formatting (i.e., allowing the standard positive;negative;zero;text paradigm to be changed based on threshold conditions in brackets).

> An alternative would be adding a[n HTML] display interpretation layer.

I don't like this because it breaks Excel's "mental model". I'm sure there's a better name, but I mean that part of Excel's beauty is its internally consistency. Once you think within its framework it's very easy to guess how to do something (or whether something is possible).

Open/close tags around text just doesn't fit the formula/argument model so pervasive in the app. Calculating HTML tags, nesting them, nesting the text, dealing with valid HTML, etc are completely uncontemplated by the formula model (and the formula-jockey user base). Implementing HTML such that it doesn't feel tacked on would be very difficult.

Just my thinking on the topic.

[1] (note from above): Separating myself from reality, it would be very cool if cell formats /could/ be set via formula.  Multiple formats would overlay each other (e.g., overlaid colors would blend, or multiple border styles would either duplicate or add to each other, depending on position).  Leave it up to the user to make sure it represents what s/he wants.  If it looks like crap, well, don't use 5 different FORMAT functions in one cell!

Of course, this would probably be technically infeasible so I'm not seriously proposing that.

<<

Bryan,

>>Circular recalcs

>But why is the problem different than numeric recalc?...

Because cells evaluating to numbers have only one numeric value, and only one text value strictly dependent on the numeric value. Formatting would have multiple aspects, all of which would be independent of each other (a cell could be any combination of bold, italic, red, strikethrough, etc., and none imply anything about the others). That means while a single cell contains a single value, it could hold a variable array of formatting properties. Recalc granularity is at the cell level. Array granularity recalc for formatting would be a nightmare. Dropping the recalc granularity to each format property plus cell values would make the recalc tree way too big (and S L O W).

>They're just formulas, and resolving circular formatting requires the same effort as resolving a circular calculation.

Wrong.

It's ONLY referencing VALUES that causes circularity. Excel can handle A1 containing the formula =ROW(A1) without circular recalc.

Adding formatting to the mix means that other cells' values AND/OR formatting could cause circular recalc. You'd be adding another dimension to the complexity of detecting circular recalc. Also, if GETFORMAT would return default formatting, i.e., that set using menu/ribbon, then EVERY formatting operation would need to trigger recalc, and FORMAT and GETFORMAT would need to be volatile.

> . . . the auditing tools will still work,...

OK, but detecting circular recalc due to formatting or a mix of formatting and calculations becomes trickier.

Consider three sets of formulas.

A1: =FORMAT("foo",IF(COUNT(1/SEARCH("Bold",GETFORMAT(B1))),"Italic"))

B1: =FORMAT("bar",IF(COUNT(1/SEARCH("Italic",GETFORMAT(A1))),"Bold"))

C1: =FORMAT(GETFORMAT(C1),

D1: =10*COUNT(1/SEARCH("Red",GETFORMAT(E1)))

E1: =FORMAT(D1,IF(F1>0,"Red"))

F1: =D1-1

Which have recalc loops and which don't? Why?

>>Recalc trees

>I imagine that reading and setting formatting is currently less optimized than reading and setting numeric cell values. . . .

There is no current reading of formatting (see my comments about CELL below). The only setting of formatting would be done via conditional formatting, and that occurs after recalc, and just uses cell values which are effectively constants when CF checks them. That is, CF can't alter any cell VALUES, and it can't alter OTHER cells' values or formatting.

> . . . However, the number of formatting dependencies (and frequency of use) will likely be far less than the number of calculation dependencies. . . .

Very likely, but circular recalc detection requires a mechanism be in place from the start and always operate. Whether this would be frequently used or not, it'd need to be checked ON EVERY RECALC.

> . . . If a user complained that having 800 cross-linked formatting dependencies makes a sheet take 10 minutes to calc, I'd say "don't do that."  Either it's so important that the 10 minutes should be worth it, or they're doing something horribly horribly wrong. . . .

Or you're suggesting functionality that's horribly horribly wrong.

> . . . Of course, the CELL() function already returns number formats, so some coding to extract formats and place it into function returns already exists....

Partial Lotus 123 compatibility. In 123, if A1 contained @CELL("Format",A1..A1) [and 123 requires that the 2nd argument to @CELL be a range reference, and 123 distinguishes between cell references, A1, and single-cell range references, A1..A1], it'd trigger circular recalc. Why not in Excel? Because Excel happily returns cell/range properties from CELL, ROW, COLUMN, AREAS and maybe some other functions without referring to the cell/range VALUE(S). Mixing values and formatting would require Excel to drop back to 123's semantics in this respect.

Currently Excel's circular recalc detection is more sophisticated than 123's (and others') because Excel distinguishes between getting values and getting properties. It can do that because currently no cell's value can affect any cell's properties other than through conditional formatting, but that's always after recalc, and the properties conditional formatting currently affects (through XL11) aren't accessible through any built-in functions. Making formatting and values potentially fully interdependent would require nontrivial additional sophistication in recalc detection.

>>Would FORMAT always return strings, like TEXT does?

> . . . However, enabling it to work on text and numbers would be really nice. I can think of many places it would be useful, . . .

OK, start thinking what formulas would need to look like. Would you be formatting parts of numbers or numbers as a whole. Lots easier to specify numbers as a whole, so

=FORMAT(100,"Red")

would make the entire number display in red text but still be treated as a number when this cell is referenced by other cells' formulas. But if you wanted to format different parts, how would you do it? For example, if the whole number portion should be bold and the fractional part not, would you have to use

=--(FORMAT(INT(OriginalCellFormula),"Bold")&TEXT(MOD(OriginalCellFormula,1),".00"))

?

Actually, that begs the question what GETFORMAT would return in this instance. The default cell formatting unless all characters have the same FORMAT formatting? Could GETFORMAT(LEFT(X99,4)) return something different than GETFORMAT(MID(X99,5,4))?

Begs another question: how would FORMAT handle an array 2nd argument? ALL current built-in functions handle arrays for any of their arguments. Would FORMAT break that consistency? Could FORMAT even be used in an array formula? How?

It's your idea, you figure out the details.

>>An alternative would be adding a[n HTML] display interpretation layer.

>I don't like this because it breaks Excel's "mental model". . . .

You mean it breaks your mental model of Excel. It's completely consistent with mine, and makes more sense to me than FORMAT and GETFORMAT functions.

> . . . I'm sure there's a better name, but I mean that part of Excel's beauty is its internally consistency. . . .

ROTFLMAO!

Excel! Internal consistency! Since when? Small examples: AVERAGE (mean), MEDIAN and MODE are measures of central tendency of random variables. AVERAGE and MEDIAN accept 3D references, MODE doesn't; =IF(1:1="foo","bar","") returns 256 instances of "bar" or "", but =IF(A:A="foo","bar","") returns a single instance of #NUM!.

> . . . Once you think within its framework it's very easy to guess how to do something (or whether something is possible). . . .

I haven't had much trouble figuring out what Excel can and can't do. It can generate text strings with embedded HTML formatting tags very easily. It's just lacking a rendering layer to convert those tags into formatting. And such embedded tags fit even more easily into Excel's CURRENT recalc framework than your FORMAT and GETFORMAT functions.

Actually, GETFORMAT would be useful for returning the DEFAULT cell formatting. Such a function could be supplemented with standard string searching to locate formatting tags, making it relatively simple to determine cell formatting in detail.

And a separate rendering layer would have the same advantages as conditional formatting: it'd happen AFTER recalc completes, and it'd treat all cell values as constants.

> . . . Open/close tags around text just doesn't fit the formula/argument model so pervasive in the app. . . .

You mean not in your model of Excel. Fits quite well in mine. It's just TEXT, and Excel has no problem generating or matching text. What's the fundamental difference between

="Lemme have "&FORMAT("another","bold")&" beer!"

and

="Lemme have "&"<b>"&"another"&"</b>"&" beer!"

?

If you really can't stand that, time for VBA.

Function tag(s As String, t As String) As String

 'error checking for t omitted

 tag = t & s & "<b>")&" beer!"

> . . . Calculating HTML tags, nesting them, nesting the text, dealing with valid HTML, etc are completely uncontemplated by the formula model (and the formula-jockey user base). Implementing HTML such that it doesn't feel tacked on would be very difficult....

Me, I consider myself part of the formula-jockey user base, but maybe my conceptual constraints and prejudices are less burdensome than those of most others.

Look at the tag udf above. How hard would it be to use? And the formatting tags Excel would need to support would be limited to only those formatting properties that could be set for characters: typeface, point size, bold, italic, underline variations, strikethrough, text color. If you want 'give me a break' to have 'give me a' in bold and 'me a break' in italic, it'd need to be tag("give ","<b>")&tag(tag("me a ","<b>"),"<i>")&tag("break","<i>"), but that's not much different than you'd need with FORMAT. Heck, when error checking for the t argument in tag is added, it could copy t in a local variable, and replace all