I am trying to write a formula so that:
if J9<10, display 00000
if J9 is between 10 and 99, display 0000
if J9 is 100-999, display 000
if J9 is 1000-9999, display 00
if J9 is 10000-99999, display 0
if J9>99999, the cell is blank (Note, it will be okay if this last one is not possible)
So far I have IF(AND(J9>9,J9<100),"0000","000"). So it works for numbers between 10-999 right now.
Excel 2010
Conditional Formatting 1 cell with 3 different formulas
I am monitoring tank levels using a program called "PI". I need to know if the tank is rising, lowering or staying the same.
I am using conditional formatting to turn red if high, blueif low and yellow if stays the same. Column B, F and J are tank volumns, Column C is in feet and D is in inches.
A B C D E F G H I J K L
1 TIME LEVEL FT IN TIME LEVEL FT IN TIME LEVEL FT IN
2 7:00 3628 18 11 9:00 3456 18 0 11:00 3321 17 3
AB AC AD
1305 5600 3600
LOW HIGH CURRENT
I started using formals =$B$2=$AD$2 COLOR YELLOW
=$B$2>$AC$2 COLOR RED
=$B$2
I'm trying to use conditional formulas with dates.
I have a list of employees that will get raises on November 1 of each year. The raise depends on their grade, which increases by one every year on the day and month they were hired.
I'm including my Excel file. For example I have one employee hired on January 16, 2012. As of today, his grade is 3. On Nov 1, 2014 he will still be on grade 3 (he won't advance to grade 4 until January 16, 2015). On Nov 1, 2015 he will be at grade 4 and on Nov 1, 2016 he will be at grade 5. Another employee was hired on October 15, 2013. His grade today is 1. On Nov 1, 2014, he will be on grade 2. What formulas can I put in the yellow cells to do these calculations?
Attached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
How to set some conditions for identifying trends. there are two trends I am trying to track, one if there are any 3 consecutive months where the score was >0, and the other was if there two occurrences of 2 consecutive months (tracking area 1 has that example), tracking area 2 shows the three months in a row, and tracking area 3 did not meet either. It doesn't mater if meets both in same row I only need to identify if yes or no.
View 3 Replies View RelatedI need to apply conditional formatting to cells in a column that contain formulas, or more accurately cells that do not. I have a spreadsheet with default values in a column based on a lookup function. I need to know if the user overwrites the default and cannot use UDF's or VBA. I don't think it's possible so my alternative is to include a hidden column that does the same lookup and do the conditional format off of a comparison.
View 2 Replies View Relatedi am trying to write a formula that calculates the difference between two cells, i.e. the buy price and sell price of a stock, to result in the profit/loss of the trade.
However, the problem is, When "going long" i need to calculate b-a = c
C:C=B:B-A:A
but when i go "short" i need the calculation to be a-b = c
C:C=A:A-B:B
i am thinking that if i have a column, say d, indicating either "long" or "short", as a conditional argument, then this can determine which formula to apply to the calculation...
e.g. please try to make sense of this...
if "d=long" then C:C=B:B-A:A, but if "d=short" then C:C=A:A-B:B
is this sort of calculation possible? any suggestions would be greatly appreciated. i have just finished reading a formula text book, but if the argument im creating isn't exactly shown word for word i am having trouble adapting the formula as needed.
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
Book0.5 template.xlsx
Can you have IF and AND statements in Conditional formatting formulas?
I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.
How do I create 3 color conditional formatting formulas for the following rules?:
if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012
Is it possible to conditional format cells with formulas in Excel 2007. It used to be possible in Excel 2003 but I'm struggling to find this in the new version.
View 6 Replies View RelatedI'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
In looking for examples of business budgets I found a sample spreadsheet that used these two conditional statements:
A) to calculate the difference between the Budget and Actual numbers: =IF(OR(B10,A1), B1-A1,) where B1 is Actual $ and A1 is the budget $.
B) to calculate the percentage (+ or -) =IF(A1,C1/A1,) where A1 is, again the budget, and C1 is the result of the difference calculated in A) above.
What is the advantage of these conditional formulas over the more straight forward B1-A1 and C1/A1
Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.
I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).
How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.
The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.
I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?
View 9 Replies View RelatedI want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?
View 5 Replies View RelatedIs there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies View RelatedI have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
Is it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?
See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.
The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.
DATA EXAMPLE - [URL]
RELEVANT INFORMATION
Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error
(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP($Dxx&$Gxx,'PriceWS'!$D:$F,3,0),""),"")'.
L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.
M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.
N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP($Dxx&$Oxx,'PressWS'!$A:$L,12,0),"")'.
P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),IF(Hxx="T",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),""))'.
Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue
Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?
I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.
If I use functions like FIND, they look at the result of a formula, and not the formula itself.
I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.
I have an excel file which consist of 12 sheets. The main sheet contain columns from A to EL and 556 rows.
At the moment, i have data on from column A to AI (we will add more data day by day). All data in main sheet are linking to another 4 sheets which basically using IF, VLOOKUP, and Conditional Formatting. My file originally was only 4.6MB in size.
However, after adding the formula (IF & Vlookup) and conditional formatting to the fourth sheet (number of columns is from A to BZ) then the file size increased from 4.6MB to 13.7MB.
My excel file also became very slow and i need to take off the Automatically Calculation option.
Col A - budget items (description)
Col B - budget dollars
Col C - actual dollars
Row 10 - summation Cols B and C
When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.
How can I highlight the cells in Col C that have entered numbers rather than formulas.
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
I have a list of values in a named range called Categories. I use this list in a validation dropdown.
Can I do a CSE formula in the "formula is" field of the conditional formatting dialog such that if one of the values in the list is not chosen, I can color the cell?
I can do a CSE formula to test a list in a cell:
{=if(A1Categories, 1,0)}
This evaluates correctly. I am wondering if there is a way to make this work inside the conditional formatting window. I am trying to avoid the built in error validation msg box options and would love to use this. It's the most efficient way I can think of. I know I can do it by adding another column (and step) and then formatting on the value in there that has the CSE formula, but I thought I'd see if I could learn something new and if this was possible.
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.