Either of 4 types of Candle in a cell

[hsandeep]

I want to make Marubozu Candles in a cell (D3)
Input cells:
A3: Either 1 or 2
B3: Either 3 or 4

D3 Candle should be EITHER of 4 types:
If A3:B3 combination is 1,3 then FULL GREEN CANDLE
If A3:B3 combination is 2,3 then FULL RED CANDLE
If A3:B3 combination is 1,4 then HALF GREEN CANDLE (HALF GREEN: candle starts at lower boundary of the cell & ends in the middle)
If A3:B3 combination is 2,4 then HALF RED CANDLE (HALF RED: candle starts at UPPER boundary of the cell & ends in the middle)

Any idea given would be highly appreciated.
I am using Excel 2007.

 

[Scott Huish]

=vlookup((a3&b3)+0,{13,"full green candle";23,"full red candle";14,"half green candle";24,"half red candle"},2,0)

 

[hsandeep]

=vlookup((a3&b3)+0,{13,"full green candle";23,"full red candle";14,"half green candle";24,"half red candle"},2,0)

I want Candle SYMBOL in the cell...a rectangular box with either Green or Red color filled (for FULL) & 'Half' rectangular box with either Green or Red color filled

 

[Scott Huish]

OK, put this formula in D3:
=VLOOKUP((A3&B3)+0,{13,"¢";23,"¢";14,"¤";24,"¤"},2,0)

Change the font to Wingdings 2. You can use a different font but you'll have to change the corresponding characters in the formula.

Then you will need to set up 2 conditional formatting formulas.
Select Column D.
Conditional Formatting Formula 1: =$A1=1
Format Font Color to Green
Conditional Formatting Formula 2: =$A1=2
Format Font Color to Red

 

[hsandeep]

This helps but not fully.

¢ gives a square box BUT only half (HALF candle criteria is full filled for GREEN Candle)
Box should start from TOP going down till half of the cell height is covered.

For FULL CANDLES i.e. BOX should at least cover 80% of the cell's height (any symbol)?

 

[Scott Huish]

The premise is still the same you just need to find a font set that has the characters you want.