Use cell formatting (e.g. "Good", "Bad", "Neutral") in formulas?

Question

I am compiling a comparison of different pieces of software in an Excel spreadsheet. It is a big long list of features (the rows), with each column being one of the applications I'm evaluating. I've used styles to visually show how well each product meets each feature, as well as the importance of that feature, and now I'm wondering if there's a way I can use those annotations in a formula.

The table is like:

.         | Product A | Product B | Product C
Feature A |   blah        blah        blah
Feature B |   blah        blah        blah
Feature C |   blah        blah        blah
....      |
....      |
etc       |

Where I've put 'blah' in the table above, in my actual spreadsheet is (potentially lengthy) descriptive text explaining something about this feature in the given product.

I've then used the styles "Good", "Neutral" and "Bad" to visually annotate the description, to show how well each product meets that feature.

For each feature I've also used the styles Accent4, 60% Accent4, 40% Accent4, etc, to annotate the importance of each feature.

Now I'm wondering if somehow I can use those styles (the annotations) to tot up a total score for each product.

e.g., Score for feature A = valueof(60% Accent4) * valueof(Good)

Is it possible at all?

Solution 1

You probably can do it using VBA, but it definitely is not very easy or user friendly. You might want to look at using Conditional Formatting with numerical values and color codes instead.

Alternatively you could use something like Resolver One, which is a spreadsheet that uses IronPython for scripting. Ironpython is much easier to work with than Excel formulas or macros, and it also allows you to embed objects and complex data types into the grid. You can then define operators on those objects so that SUM(B:B) would return your desired result.

I used Resolver One to make a Texas Holdem Monte Carlo simulator, which made extensive use of embedded objects in the grid. There are lots of other fun things you can do with it too, like pivot charts, WatiN integration, WPF controls, and Speech Synthesis.

Solution 2

I don't have experience with Excel 2007, but in 2003 I would probably create a hidden data sheet/table for calculating your values for good/bad and your accent values. This data table could be used as the basis for generating your extra styles and your overall scores.

So whatever formulae you use to work out the styles for the cells, are stored outside of the style calculation and then indirectly referenced. This would allow the values to be accessed for other purposes such as working out your scoring system.

Hope that I have grasped your problem correctly

Comments

[ngm]

In the end I just added extra 'score' columns next to the free form text descriptions, and used conditional formatting to get the styling.