Excel Calculations and VBA
In the following Excel spreadsheet, I need to do 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 colors selected using Java?
+1
Lost
a source
to share
2 answers
It looks like you want to do something like this ... "Countif cell color is red" . That is, you mean applying different multipliers based on the cell format. See also "Color Functions in Excel" .
But honestly, your best bet 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. Create a column containing the information contained in the "color" formatting and use that.
+2
a source to share
You can 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 calculations:
| 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) |
. | .. | ... | ... |
+1
a source to share