[Solved] Changing color of currency based on their values

[Editor3000]

I hate starting new topics if I don't have to, but I cannot find anything in OOo Help file or in any of the forums.

I am running WindowsXP & OpenOffice.org 2.4

I have written a Calc document for my job and it works but I would like to add some color to it. I would like the color of USD currency numbers to change colors within certain value ranges. Making negative numbers turn red is as simple as checking a box, what I need is this:

Example:
Fields B3 & D3 are formated for standard US Currency
Field B3 input is $189.95, and field C3 entry is 6. ($189.95 / 6 = $37.99)

=Sum(B3/C3) displays the answer as Currency in field D3

If the value of D3 is $24.99 and below, the value would be shown in RED
If the value of D3 is $25.00 thru $31.99, the vlaue would be displayed in BLACK
If the value of D3 is $32.00 and above, the value would be displayed in GREEN

I have searched the openoffice.org help file and forums but only found similar examples but none that involve currency. I have tried to adapt the formulas for currency in the "Format Cells>Numbers>Format Code" field, but have failed every time. If someone could give me an example of how to write a formula of the above example, it would be greatly appreciated.
I am relatively new at using this program and love it. I have found alot of useses for it the more I play with it.

[Nikos]

Does it need to be a formula?

I think conditional formatting (Format ---> Conditional Formatting) on the cells with your result should do.

[Editor3000]

It does not have to be a formula I guess.
As much as I have looked at the Conditional Formating window and read up on it in the Help file, I can't figure out how to use it.

What would I put in the Conditional Formating window to get it to display those values with those properties?

[Villeroy]

=Sum(B3/C3)+STYLE(IF($D3<25;"RED";IF($D3<32;"BLACK";"GREEN"))
where RED, BLACK and GREEN are the names of predefined cell-styles.
Since constant values in formulas can be considered as bad practice (hard to maintain, particulary if cluttered across sheets):
Create a helper table:
0 RED
25 BLACK
32 GREEN
.. first column has thresholds, the second column style names. Name it, say "Lookup_Style" by just typing the name into the name box while the helper table beeing selected.
=Sum(B3/C3)+STYLE(VLOOKUP($D3;Lookup_Style;2))

This works with more than 3 conditions (just insert rows into Lookup_Style) as long as Lookup_Style is sorted ascending by it's first column.

The whole office suite is based on format styles. Conditional formatting works with up to 4 styles (one "base format" plus 3 conditions), without requiring formulas.
Example with 5 formats applied to numbers and text: http://user.services.openoffice.org/en/ ... php?id=667

[Editor3000]

I thank you for your help, but I still don't quite understand it.
I am beginning to understand it but I'm not there yet. I still can't figure out what to do to get it to do what I want. Thank you for the downloaded file. It is very colorful but I'm not sure how you did it. I know it's random for your example. I just can't figure it out. When I tried
=Sum(B3/C3)+STYLE(IF($D3<25;"RED";IF($D3<32;"BLACK";"GREEN"))
I just got a "ERR:522"

What did I do wrong

[Villeroy]

522
Circular reference
Formula refers directly or indirectly to itself and the Iterations option is not set under Tools - Options - OpenOffice.org Calc - Calculate.

You put the formula in D3, so the formula has a reference to itself. I thought that D3 has an independent value and you would need the formula for another cell, say E3.
In D3 you can use:
=SUM(B3/C3)+STYLE(IF(CURRENT()<25;"RED";IF(CURRENT()<32;"BLACK";"GREEN")))

CURRENT() is the current result of this formula SUM(B3/C3)
This should return the right value without formatting anything until you defined the respective cell-styles (activate my document and hit F11 to show the stylist
In my document try the simplified: =STYLE(IF($A13>0.5;$C$1;$C$2))
By the way, the sum of B3/C3 is the same as B3/C3. You divide B3 through C3 and sum up the resulting single value.

[Villeroy]

How to quickly create 3 styles:
Get three unused cells and format them in 3 different ways, say A1:A3 of some sheet.
Call the stylist (F11)
Highlight the first one (Ctrl+Click A1)
Drag the cell into the stylist. This will prompt for a style name for a new style drived from the dragged cell.
Repeat with the other cells.

[Editor3000]

I thank you very much for all your help!
While looking over your documnet "style_function", & reading the help file (again) I finally had an revilation!
The document now works exactly like I want!

Thank you! Everyone that helped!

[Editor3000]

When I started looking for help, I didn't understand what I was reading. As a result for anyone else who is looking do do the same thing, I decided I would post step-by-step instructions in a language that a beginner would understand.

If you want to make numbers or cells change to specific colors whenever a certain value or value range is put in a cell, follow thease instructions:

1. Select an empty cell
2. Right click in the cell
3. Select "Format Cells..."
4. Use the tabs across the top of the window that opens to change the background color, font color, font size, text alignment, and everything else you wnat a cell to change to if a certain condition is placed in any box you specify. When finished, press "OK" to close the window.
Example: Normally, a cell is standard format. Size 10 font, Black font, White background, and the text is aligned to the bottom left of the cell.
You can change the cell to a size 12 font, White font, and a "Green 2" background.
5. Once you have that one cell looking the way you want, press "F11" to open a window called "Styles and Formatting"
6. Highlight the cell you just modified to look the way you want. The color should invert when it is highlighted properly. Example: the Green will turn Pink
7. With the cell highlighted, click and hold on the cell with the left button.
8. While holding the left mouse button, drag the cell into the Styles and Formatrting window.
9. The "Create Style" window will open.
10. In the empty field, give the cell a name like, "Green2" and Click OK to save the cell's settings and close the Create Style window.
11. (Optional) At this point, you can delete the cell you modified by right clicking in the cell.
12. (Optional) Select "Delete Contents..." from the pop up menu
13. (Optional) The Delete Contents window will open. Check "Delete All" and click OK to close the window.
14. (Optional) Now you can click the "X" on the top right corner of the Styles and Formatting window to close it.
You can repeat the above steps as many times as you want to make different styles and formats of the cells. For this example, we will deal with just one.

Here is where we now set the conditions of when a cell will change to the color scheme you just set up.

15. Select any empty cell by clicking the left mouse button once.
16. Move your mouse to the top menu bar and select Format
17. Scroll down to "Conditional Formatting" and left click to select.
18. You are now presented with a window with three sections called Conditions. For this example, we will only use Condition 1
We are going to make it so if any number above the number 10 is entered in the blank field you selected, it will take on the format you named "Green2"
19. We are going to leave the first field alone. This field says "Cell Value"
20. The second field needs to be changed. Click the drop down arrow and select "greater than or equal to"
21. Since we want the number to change color when it reaches or exceeds the number 10, we are going to enter the number 10 into the 3rd field which should be blank.
22. Now, to make it change color, the 4th field is titled "Cell Style" and is currently labled "Default"
23. Open the drop down box by clicking the down arrow and select "Green2" from the list.
24. Click OK to save the settings and close the box.

Now let's see it work!

25. With that same field selected, you can type any number between 0 and 9, it will stay with the default settings.
26. Now enter any number from 10 or above and the field will automatically take on all the settings you set up for the field earlier.

IT'S AWESOME MAGIC!

Now, you can make different cell styles and formats by following steps 1 thru 14. This time make it red!
Now by going back into the conditional formatting in steps 15 thur 24, you can click the check box for condition 2 to activate it
You can change "equal to" to "less than or equal to" then input 3 into the blank value field.
Open the drop down menu that says "default" and change it to "red" or whatever you named it.
Now if the number is equal to, or drops below 3, then the cell will turn red.

Now make another cell style, this time yellow.
Go back to the conditional formating window and activate the 3rd condition.
Select the drop down menu to change "equal to" to "between"
Now the blank field splits.
In the first blank field, enter 4, then in the second blank field, enter 7.
Open the drop down box that says "default" and change it to "yellow" or whatever you named it.

Now, if you enter 0 thru 3, the field will be red
If you enter 4 thru 7, the field will turn yellow
If you enter 8 or 9, the field will be defaulted (should be white)
and if you enter any number that is 10 or above, the field will be green.

Enjoy