Functions by format

I’ve seen a few mentions recently about being able to say SUM by colour, or AVERAGE if bold etc.

Currently I don’t think format is part of the calculation chain of any spreadsheet app. So any format based function would have to be volatile. Such functions are fairly trivial to write in VBA, a bit (but not much) harder in xlls.

But I have a more fundamental question:

Should we be able to base functions on formats?

Is it reasonable that changing the look of one cell would change the value in another?

I know these requests are popular on-line, but so was a million rows.

I have had a bit of a think about it and I think we should preserve the clear separation of the value and presentation of that value. (I know that got a bit muddied by Excel percentages but still).

I think having values dependent on formats would make verifying the correctness of a model even harder.

What do you think?




15 Responses to “Functions by format”

  1. fran Says:

    I agree entirely. Surely that is the whole point of numbers in the first place; it’s the value that matters not their font, colour, weight etc. If a value has more weight based on its format then surely it isn’t a number any more.

  2. Gordon Says:

    While there have been occasions where I have wanted to calculate based on format (usually as a result of a workbook presented to me for “fixing”), I’ve been happy enough to use VBA for this as I am in complete agreement that presentation and content should be separate (we live in an XML-enabled world after all!).

    Imagine having to audit a sheet where the only thing that is causing a problem is some minor format difference, say a slightly different RGB colour value? I know this particular example is only a real issue in 2007, but it’s still valid and there could be thousands of ways in which a formula might be broken if formatting had to be taken into account.

  3. Omar Says:

    I agree that presentation needs to be separate from content.

    The situation I see where this would be useful is ad hoc reviewing of a list. Occasionally, my boss might want to manually choose a row of data by changing the background colour of a cell. Once the rows are chosen, often we want to set the auto filter to include only those rows, say for printing purposes.

    Right now, usually I just add a column to type in a marker (say the letter ‘z’). A more permanent solution might be to add check boxes tied to cells in the data range if the person really prefers to click on something.

    That might be the better way. Rather than helping to muddy things by mixing data and formatting, help the people set up the spreadsheet to get the desired result using data only.

  4. jonpeltier Says:

    If the colors are based on conditional formatting (as they should be), then calculations can be based on the same formulaic conditions. This is not as useful prior to 2007, with only three conditions, but it’s robust in that formats are applied for a reason.

  5. Ross Says:

    I don’t think you should base calculations on formats. Jon makes a good point, and so does Omar. I can se there maybe some case where it might be helpful. I think this is one of MS problems in genral. Should they give poeple a feature that might be useful, but could also lead to huge misuse, or do they “protect people from themselfs?” – it a judgment call i think.

    For me unless i could do somthing to mitigate the risk, I would leave it out.

  6. Tom Gleeson Says:

    The mixing of data, presentation and code is the thing that makes spreadsheets approachable to “ordinary folks” (as well as been the stuff of nightmares for audit/conformance folks). Being presented with worksheets where the “blue means this.. ” the “yellow should be subtracted” etc. is a very common occurrence,those comfortable with VBA can easily filter such data, others however would in effect have to re-do the worksheet,(as returning the work it to the Head of Marketing for “re-do” is often not an option) . So I’d be in favour of such functions as for many people Excel is about presenting and understanding data rather than using it as systems building tool.



  7. Greg Says:

    Although I can see the dangers in calculations based on formats and would never use it in a production spreadsheet, there’s always someone that could benefit. If I was doing ad hoc reporting and only wanted to sum the bold values, that could really save me some time. I probably wouldn’t want to give someone else the spreadsheet since it would be hard for them to figure out what I did, but for my own ad hoc analysis, it could be a real time saver. The same goes for a million rows. If I get a data dump and just want to do some quick analysis, I’d much rather throw it in Excel and be done with it. Now if I was going to build an application or share it with other users, I’d probably want to move the data to a database. For quick and dirty work, the more flexibility that Excel has, the better.

  8. Harlan Grove Says:

    IF there a simple, direct means of determining which conditional formatting rule applied using only worksheet functions, then I could see no need for other functions that returned range formatting information.

    But note that there’s already a CELL function which returns some (little) formatting information. It was obviously modeled on Lotus 123’s @CELL function, but Excel’s is stuck in 123 Release 2 functionality. Lotus added a great deal of cell formatting options to 123’s @CELL in 123 Release 3 and later. And, FWIW, it has been possible to use 123’s @CELL in criteria arguments to its @DSUM etc functions for about 18 years by now, so while perhaps not in widespread use, 123 has had the ability to calculate by formatting for a very long time.

    There are other aspects to 123’s @CELL, e.g., it’s ability to return the name of the worksheet containing it’s range argument (more precisely, the name of the worksheet containing the first cell in its range argument), and workbooks don’t need to be saved for this to work. Any GOOD reason Excel still lacks a worksheet function to return the name of a worksheet containing a specified range without needing to save the workbook first? There are other examples of 123 returning more than Excel, and these are things 123 has been able to do for nearly 2 DECADES.

    The Excel developers charged with worksheet functions really do seem to enjoy sitting on their thumbs.

  9. jonpeltier Says:

    @Ross: {Should [MS] give poeple a feature that might be useful, but could also lead to huge misuse, or do they “protect people from themselfs?”}

    Unfortunately they protect the wrong people from the wrong things, and otherwise leave it wide open.

    @Harlan: I’m afraid those developers have been forced to concentrate on implementing Office decrees, like the new UI and the new Shapes paradigm.

  10. Jayson Says:

    If you are summing based on conditional formatting (suggested by Jon), aren’t you really summing based on the same numerical conditions used by the conditional formatting?

  11. Harlan Grove Says:

    Yes, if summing on conditional formatting, that’s summing on the same conditions as used for conditional formatting. But it’s inefficient and a fruitful source of errors over time to have to repeat the same conditions in multiple places. Better to do things as few times as needed, which means avoiding using the same criteria both for conditional formatting and conditional counting/summing/etc.

  12. Jason B Says:

    Personally, I’d like to see this capability (could have used it just yesterday). In the example of summing or sumiffing through formatted data, this would offer a person the option of eliminating work columns and tags without resorting to VBA and clouding the calc.

    There are plenty of existing formulae that produce unauditable spreadsheets for the average user. The key is designing for your audience/purpose.

  13. jonpeltier Says:

    What I often do in put the formula in a helper column, then base both the CF and the calculation on this column. Everything’s out in the open, I don’t have to guess what criterion in CF is being met, and there’s little chance of error.

    Jason – For one-off workbooks, I fire up VBA with something like:

    For Each rCell in ActiveSheet.Range(“A1:A20”).Cells
        If rCell.Font.Bold Then rCell.Offset(, 1).Value = True
    End If

    Easy, without having to draw up a custom UDF.

  14. Lord Says:

    Since formats can be specified by value, making values dependent on format would introduce another source of circular reference. Not a good thing.

  15. Patrick O'Beirne Says:

    I agree with Tom Gleeson – de facto we get sheets and want to know the total of cells they highlighted. Then of course it’s “the total should be Y… what was missed” :-)

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: