Spreadsheet performance - Shared Formulas

re: Spreadsheet performance - Shared Formulas

John — Mon, 29 May 2006 16:50:22 GMT

Brian,
You might want to fix your examples. In the tables row 2 and 3 are different,(Product 1 and 2). In the code examples you repeate the data from row 2 in row 3.

Niklas’ blog » Blog Archive » Links: dinner, Excel 2007, Green River Killer, Cannes, $100 laptop, SlimStat, FiLO, punks, Sherlock, Pipettes

Mon, 29 May 2006 16:51:24 GMT

PingBack from http://niklasblog.com/?p=969

re: Spreadsheet performance - Shared Formulas

BrianJones — Mon, 29 May 2006 19:08:34 GMT

Great catch John, thanks. I've updated the examples to match the tables...

re: Spreadsheet performance - Shared Formulas

M. David Peterson — Mon, 29 May 2006 20:06:51 GMT

>> I admit that it makes development a bit more difficult, but as I said before, we kind of had to take the training wheels off and actually think more about the end users as well. <<

Beautiful! Take 'em off :) I'm ready to ride without them :D

Computer Science is supposed to be difficult... Not that I would want to encourage the notion of making it as difficult as possible such that the only one who can possibly make sense of it all is Knuth, but you have to draw the line somewhere.

Its also nice to see you folks are thinking about the fact that just because we have the processing power doesn't mean we need to be overclocking our CPU's and keeping them at 90% capacity 24 hours a day. Its nice to know the power is there if we need it. Its also nice to know that when we need it, its there (if thats make any sense :)

re: Spreadsheet performance - Shared Formulas

Mike — Mon, 29 May 2006 23:37:23 GMT

Looks a lot like BIFF with angle brackets so far...

Case in point, what about business solutions that have to update formula values as part of the file update? Since this blog is about the so-called power of Xml and associated API, how do you achieve this without a running Excel instance?

re: Spreadsheet performance - Shared Formulas

Biff — Tue, 30 May 2006 06:57:30 GMT

"Since this blog is about the so-called power of Xml and associated API, how do you achieve this without a running Excel instance?" -- Mike

Perhaps invest in better coders? This appears to be very sensible and straightforward optimization, not rocket science by any stretch of imagination. C'mon, do you believe every user out there must suffer from performance drops because your coders cannot get their act together? Bleh.

re: Spreadsheet performance - Shared Formulas

Mike — Tue, 30 May 2006 07:40:14 GMT

Biff,

I think you missed my point. How can you possibly say you can perform calculations without relying on a running instance of Excel? You realize that in order to do that you end up rewriting Excel, right?

re: Spreadsheet performance - Shared Formulas

Biff — Tue, 30 May 2006 09:29:37 GMT

"How can you possibly say you can perform calculations without relying on a running instance of Excel? You realize that in order to do that you end up rewriting Excel, right?" -- Mike

Wrong. You're free to use any or all of Quattro Pro, OOo Calc, Gnumeric, KSpread, and more pure-bred calculation engines like KDCalc and SpreadsheetGear - plug them in and update your data. You might get different results, but we're talking file formats here, right?

re: Spreadsheet performance - Shared Formulas

Alex — Tue, 30 May 2006 10:56:02 GMT

I guess if you're doing string sharing etc. it's probably a nice idea to extend that to different parts, like formulas.

Brian, is there a way of turning these optimisations off individually or not? I'm just wondering if it's possible to try and actually get some numbers on what difference these changes make.

I have the same worry as Mike - the more complex it is to interpret a spreadsheet, the less useful the OXML format becomes IMHO. But, since OXML doesn't store result values (as far as I know?) you have to process the formulas anyway, so I guess this isn't much extra work on top of that.

Biff - you probably could use alternative engines for some spreadsheets. Gnumeric in particular has a highly compatible implementation, which contains all the functions Excel does and gives more accurate results. But the others are not as compatible.

Since OXML is standardising the functions available, maybe other spreadsheets will build in a compatibility module. It would be better if the formula stuff could be standardised on its own, though.

re: Spreadsheet performance - Shared Formulas

M. David Peterson — Tue, 30 May 2006 11:53:47 GMT

>> I think you missed my point. How can you possibly say you can perform calculations without relying on a running instance of Excel? You realize that in order to do that you end up rewriting Excel, right? <<

I feel like I'm in the twilight zone:

Group:We HATE Microsoft, err... Open Standards Advocates:

"We DEMAND Open Standards to encourage competition and to not lock out other vendors from developing their own versions of office software that can openly read and write to this format"

Group:Same as above:

"How can you possibly say you can perform calculations without relying on a running instance of Excel? You realize that in order to do that you end up rewriting Excel, right?"

Me: "Du doo du doo... Du doo du doo... Do not adjust your set... Do not... "

To quote Biff:

Bleh!

---

Okay, sarcasm aside... The point of an open standard is to ensure that anybody, regardless of who they are, has the ability to openly use this standard for the primary purpose of interop. Or in other words, no matter what tool you might choose to use, you can expect that this same document format will be accessible to open, edit, save, repeat, as long as that vendor of choice has implemented support for this open standard.

"You realize that in order to do that you end up rewriting Excel, right?"

Yep! And thats the whole point.

re: Spreadsheet performance - Shared Formulas

M. David Peterson — Tue, 30 May 2006 11:55:39 GMT

@Alex,

Hey, I'm diggin' your comments... VERY constructive... It's fantastic to see that you have taken to this position as you are obviously someone who has a lot of value to add to the conversation.

Learning things from folks is what I hope to do everyday of my life. Today, you help me learn something I didn't know yesterday.

Thanks! :D

[Brian Jones(MSOffice XML):Blog] Comment Of The Day

O'Reilly XML Blog — Tue, 30 May 2006 12:41:23 GMT

Brian Jones: Open XML Formats : Spreadsheet performance - Shared Formulas via a comment to the above linked, Biff (unfortunately I don't have a link (Biff, if you happen to read this and have a preferred link, let me know...

re: Spreadsheet performance - Shared Formulas

M. David Peterson — Tue, 30 May 2006 12:49:44 GMT

@Biff,

http://www.oreillynet.com/xml/blog/2006/05/comment_of_the_day.html

re: Spreadsheet performance - Shared Formulas

Mike — Tue, 30 May 2006 12:49:55 GMT

Biff said "Quattro Pro, OOo Calc, Gnumeric, KSpread, and more pure-bred calculation engines like KDCalc and SpreadsheetGear"

I didn't know those apps had native support for importing/exporting .xlsx files in order to perform calculations outside of Excel. Thanks for the info.

;-)

re: Spreadsheet performance - Shared Formulas

Biff — Tue, 30 May 2006 21:15:47 GMT

"I didn't know those apps had native support for importing/exporting .xlsx files in order to perform calculations outside of Excel." -- Mike

So your attention span is under 25 words, is it what you wanted to say? <g>

re: Spreadsheet performance - Shared Formulas

Mike — Tue, 30 May 2006 22:37:36 GMT

That was sarcasm.

Btw, are you a Microsoft employee? I'm willing to know if someone of your "caliber" is inside or outside the fence.

re: Spreadsheet performance - Shared Formulas

Biff — Tue, 30 May 2006 23:02:28 GMT

"That was sarcasm. " -- Mike

Doubt it.

"Btw, are you a Microsoft employee?" -- Mike

No, I'm but a lowly Microsoft shill, can't you tell?

re: Spreadsheet performance - Shared Formulas

Gabe — Tue, 30 May 2006 23:49:59 GMT

Alex: The spreadsheet has to contain the values shown for each formula. Doing a recalc may be very expensive or may even change the values (in case of RAND or iterative computations).

It appears you didn't notice that the formula cells have both an "f" tag (for the formula) and a "v" tag (for the value.

re: Spreadsheet performance - Shared Formulas

Patrick Schmid — Thu, 01 Jun 2006 08:27:07 GMT

Hi Brian,

I just came across an interesting Word topic that would be great to hear on from you: Master Documents
Is there still a need for master documents in 2007 (meaning can it better handle documents with >500 pages, more than 32 MB of text, etc?) Do master documents work better (hence don't corrupt easily) when used with new XML file formats?
Thanks,

Patrick

Thoughts on Open XML in ISO

Brian Jones: Open XML Formats — Mon, 05 Jun 2006 21:31:21 GMT

As we move forward with the standardization of the Office Open XML formats, it's interesting to look...

re: Spreadsheet performance - Shared Formulas

Claus Agerskov — Fri, 09 Jun 2006 09:55:07 GMT

By using a reference for relative identical formulars it makes it more difficult to read and understand an OOXML document on the fly.

You also have the problem when updating a document writing a "new" formular because you have to check all the existing formulars if one of them is relative identical - or is it only when the formulars are in the same or extended area like original in B2:B5 but now B1:B5.

You could also implement it in a way so you both have the reference and the formular so the application only have to read one of the two but have to save them both. Quickly to read but slower to write.

re: Spreadsheet performance - Shared Formulas

BrianJones — Fri, 09 Jun 2006 19:32:10 GMT

Claus, it is true that it's a bit more difficult if your goal is to go to a specific cell and analyze it without looking anywhere else. Otherwise though, it's fairly straightforward.

Your suggestion of duplicating the data isn't really useful unless we actually enforced that they matched. Otherwise a consumer wouldn't know which one to trust. We try to avoid data duplication unless it gives a perf gain, and even in that case we try to make sure we enforce that the duplicated data is valid (otherwise it's much harder for consumers to know which value to pick).

If you think about it, the shared formulas not only make things significantly faster, but they also make them easier. If you have 50,000 rows of data, and one of the columns is a calculation of some of the other columns, then it's much easier to update that formula in this case (you only need to edit one cell). Without the shared formulas, you'd have to edit all 50,000 instances of the formula.

-Brian

re: Spreadsheet performance - Shared Formulas

John Robins — Fri, 09 Jun 2006 20:46:48 GMT

What about bugs in dates:
http://openxmldeveloper.org/forums/thread/280.aspx

More information on the Open XML translator and some questions answered

Brian Jones: Open XML Formats — Tue, 11 Jul 2006 16:57:04 GMT

There were a lot of great comments from last week's announcement about the creation of an open source...

Performance of an XML file format for spreadsheets

Brian Jones: Open XML Formats — Thu, 15 Mar 2007 19:44:35 GMT

I've blogged in the past about some of the things we did with the SpreadsheetML format to help improve