Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Generating a Specific Formula
I am trying to create a "Traffic Light" formula and an experiencing
difficulty. What I am trying to accomplish is if I have a budget # of , say $3, If the actual cost is less than 90% then Iwant the cell to show green in color. If the number approaches 90%-100% of the budget # than I want the cell to show as yellow. And, If the actual nuber exceeds the budget # ($3), then I want the cell to show up red in color. Has anyone had experience with this type of situation and if so could you please lend me some pointers? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Generating a Specific Formula
On the menu barformatconditional formatformula is
-- Don Guillett Microsoft MVP Excel SalesAid Software "Gene Cocchi" <Gene wrote in message ... I am trying to create a "Traffic Light" formula and an experiencing difficulty. What I am trying to accomplish is if I have a budget # of , say $3, If the actual cost is less than 90% then Iwant the cell to show green in color. If the number approaches 90%-100% of the budget # than I want the cell to show as yellow. And, If the actual nuber exceeds the budget # ($3), then I want the cell to show up red in color. Has anyone had experience with this type of situation and if so could you please lend me some pointers? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Generating a Specific Formula
Use formatconditional formatting, assume J4 is the cell you want to format
and H3 the budgeted value, select J4 for the first condition use formula is =J4/$H$3*100<90 click the format button and select green pattern, click OK then click add now use =AND(J4/$H$3*100=90,J4/$H$3*100<=100) repeat and select orange/yellow pattern finally for the third use =J4/$H$3*100100 repeat and select red then close by clicking OK twice -- Regards, Peo Sjoblom "Gene Cocchi" <Gene wrote in message ... I am trying to create a "Traffic Light" formula and an experiencing difficulty. What I am trying to accomplish is if I have a budget # of , say $3, If the actual cost is less than 90% then Iwant the cell to show green in color. If the number approaches 90%-100% of the budget # than I want the cell to show as yellow. And, If the actual nuber exceeds the budget # ($3), then I want the cell to show up red in color. Has anyone had experience with this type of situation and if so could you please lend me some pointers? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Generating a Specific Formula
You can use Conditional Formatting for this:
Select the cell containing your actual cost. I'll assume cell B1 for this example, and that cell A1 contains your budget amount. From the Format Menu, choose "Conditional Formatting..." Change "Cell Value Is" to "Formula Is" Enter the formula: =B1<A1*.9 Click the FORMAT button and select your format (Green) Click the ADD button Change "Cell Value Is" to "Formula Is" Enter the formula: =B1<=A1 Set your format (Yellow) Click the ADD button Enter the formula: =B1A1 Set your format (Red) Click OK These steps are for Excel 2003 or earlier. If you have 2007, then there are a few differences, but the same idea applies. Post back if you have 2007 and still need help. HTH, Elkar "Gene Cocchi" wrote: I am trying to create a "Traffic Light" formula and an experiencing difficulty. What I am trying to accomplish is if I have a budget # of , say $3, If the actual cost is less than 90% then Iwant the cell to show green in color. If the number approaches 90%-100% of the budget # than I want the cell to show as yellow. And, If the actual nuber exceeds the budget # ($3), then I want the cell to show up red in color. Has anyone had experience with this type of situation and if so could you please lend me some pointers? Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help Generating a Specific Formula
Don,
Thank You Very Much! Your feedback worked perfectly! "Don Guillett" wrote: On the menu barformatconditional formatformula is -- Don Guillett Microsoft MVP Excel SalesAid Software "Gene Cocchi" <Gene wrote in message ... I am trying to create a "Traffic Light" formula and an experiencing difficulty. What I am trying to accomplish is if I have a budget # of , say $3, If the actual cost is less than 90% then Iwant the cell to show green in color. If the number approaches 90%-100% of the budget # than I want the cell to show as yellow. And, If the actual nuber exceeds the budget # ($3), then I want the cell to show up red in color. Has anyone had experience with this type of situation and if so could you please lend me some pointers? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to add specific #'s across the top | New Users to Excel | |||
SUM formula generating formula instead of result. | Excel Worksheet Functions | |||
My cell formula is generating an unwanted e-mail message. | Excel Worksheet Functions | |||
Need help with generating a formula PLSE. | Excel Discussion (Misc queries) | |||
Formula generating a column of data? | Excel Worksheet Functions |