Excel Calculations and VBA

Question

In the following Excel spreadsheet, I need to make the following calculations:

Input      Color Selected        Output  
-------    --------------       --------
  40            red               40x18
  40            blue              40x12
  40            green             40x16
  40            yellow            40x13
  39            red               39x18
  28            blue              28x12
  33            green             33x16
  25            yellow            25x13

My question is, how can I assign values to the colors being selected using Java?

Answer 1

It sounds like you want to be doing something like this... "Countif cell color is red". That is, you mean to apply different multipliers based on the format of a cell. See also "Color Functions In Excel".

But to be honest, the best thing to do is to create a new column that contains the semantics of the information you are trying to represent using formatting and use THAT for your conditional expression instead. Make a column that contains the information contained in the "color" formatting and use that.

Answer 2

You could have a separate table with colors and numbers:

   |    F    |   G   |
---|---------|-------|--
 1 | red     |   18  |
 2 | blue    |   12  |
 3 | green   |   16  |
 . | ...     |   ..  |

And then use the table in your calculation:

   |   A   |     B     |                 C                 |
---|-------|-----------|-----------------------------------|--
 1 |   40  | red       |  =A1*VLOOKUP(B1,$F$1:$G$100,2,0)  |
 2 |   40  | blue      |  =A2*VLOOKUP(B2,$F$1:$G$100,2,0)  |
 . |   ..  | ...       |                ...                |