Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
In the end I just added extra 'score' columns next to the free form text descriptions, and used conditional formatting to get the styling. –  ngm May 14 '10 at 8:04
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.