Conditional Formatting Trick 1 – Multi-Coloured Data Bars

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Biff — Mon, 27 Feb 2006 22:51:23 GMT

Kanwal -

You can use a normally entered Sumproduct formula which will evaluate just fine:

=SUMPRODUCT(--($H$4:H715>=$AG$1),--($I$4:I715<>"Swi"))

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

007-Kanwal — Mon, 27 Feb 2006 15:18:31 GMT

Off specific topic but it is on the general topic of Excel......

I share a problem just like discussed by Biff.

(My version Excel 2003-SP3 Calculation Engine 114210 )

Whenever I use the any Array Formula like

SUM(($H$4:H715>=$AG$1)*($I$4:I715<>"Swi")), and I use Auditing>Evaluate, excel crashes everytime at evaluating the formula.

Thank you David for the Great Work. Hopes to listen on "Validation" soon

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

tj — Sun, 26 Feb 2006 22:51:17 GMT

Toffu: I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all.

You are conflating update with upgrade.

Biff is keeping Office 10 updated. Like Biff, I would like to see an answer that doesn't involve upgrading to Office 11 (2003).

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Biff — Sun, 26 Feb 2006 09:11:07 GMT

"I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all."

But I have: My full version: Excel 2002 (10.6501.6735) SP3

As recently as tonight!

Biff's problem

Toffu — Sun, 26 Feb 2006 08:13:46 GMT

Biff: Updating my Office application NEVER has fixed this problem!

I tried your formula in Excel 2003 and it evaluates fine. Maybe you need to update you Office application after all.

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Biff — Sun, 26 Feb 2006 04:27:25 GMT

Off specific topic but it is on the general topic of Excel......

My full version: Excel 2002 (10.6501.6735) SP3

I've been having a problem with Excel crashing. It's happened for a few years now and since I have this opportunity for someone from MS to "hear" about it, here goes!

Whenever I use Formula Auditing>Evaluate Formula on "complex" array formulas like this one:

=IF(ROWS($1:1)

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

David Gainer — Sat, 25 Feb 2006 22:52:18 GMT

Rob, thanks for spotting the typo.

Brandon, good feedback. For XL2007, the design is pretty much set.

Mike, there is a bug in beta1. Will be ok in the next build you get.

All, we thought about putting the property in the mainline UI, but it seemed to confuse people more than help.

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Bill J — Sat, 25 Feb 2006 13:51:13 GMT

This is a great trick. I would keep it in VBA for this release - the entire formula in the current conditional formatting is hard for people to learn. If you added it to the dialog box, it would overly complicate the new feature.

For the Power Excellers who want to show off, they can easily go to VBA to set this amazing trick up.

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Mike — Sat, 25 Feb 2006 08:32:52 GMT

Does not seem to work very well in beta 1. When running the code in the immediate window, I get an automation error, "disconnected client". And then the selection in Excel does not work anymore.

re: Conditional Formatting Trick 1 – Mulit-Coloured Data Bars

Brandon Bloom — Sat, 25 Feb 2006 06:54:49 GMT

Interesting... would there be anyway discoverable (read: code-less) way to do this?

Maybe some combination with Color Scales?blogs.msdn.com/.../477948.aspx

(Also, on the topic of color scales, can that color scale editor become more like gradient editors in applications like Photoshop? It would be nice to be able to drag to adjust gradient points and add colors)