How To Perform 2 Consecutive Conditional Formulas For Same Row
Dec 21, 2013
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
ADVERTISEMENT
Feb 6, 2014
In the past I have used VBA script to change column with size based on cell values as per follows:
[Code]....
Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.
To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:
[Code] ....
I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.
View 7 Replies
View Related
Aug 23, 2014
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?
View 4 Replies
View Related
Nov 28, 2008
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
View 2 Replies
View Related
Apr 2, 2009
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.
View 4 Replies
View Related
Mar 28, 2013
I 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 Related
Dec 18, 2007
i 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.
View 9 Replies
View Related
Jan 19, 2014
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
View 3 Replies
View Related
Aug 20, 2009
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.
View 12 Replies
View Related
Jun 23, 2012
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
View 9 Replies
View Related
Feb 27, 2013
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 Related
Jun 11, 2013
I'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.
View 1 Replies
View Related
Mar 16, 2014
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
View 1 Replies
View Related
Sep 11, 2007
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.
View 9 Replies
View Related
Dec 29, 2008
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 Related
Oct 19, 2009
I 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 Related
Aug 11, 2012
Is there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies
View Related
Jul 9, 2013
I 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.
View 9 Replies
View Related
Feb 16, 2014
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
View 3 Replies
View Related
Mar 13, 2014
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
View 2 Replies
View Related
Nov 24, 2010
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.
View 7 Replies
View Related
Jan 28, 2011
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.
View 14 Replies
View Related
May 29, 2013
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.
View 1 Replies
View Related
Dec 1, 2008
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.
View 9 Replies
View Related
Feb 26, 2013
I am working on two worksheets. On my first worksheet I have one column with consecutive numbering starting from Cell 8.
A8 = 1
A9 = 2
A10 = 3
A11 = 4
A12 = 5
On my second worksheet I want to link to the values in the first worksheet but not in a consecutive manner. Each number will have an interval of 15 cells. As shown below:
D15 = 'link on Sheet1!A8'
D30 = 'link on Sheet1!A9'
D45 = 'link on Sheet1!A10'
D60 = 'link on Sheet1!A11'
D75 = 'link on Sheet1!A12'
View 6 Replies
View Related
Dec 7, 2006
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.
View 9 Replies
View Related
Dec 25, 2013
it is more than 5 days now that I try to perform calculation on pivot.
problem: I have one cell that I set to show values as "% of the column total" when try to perform the calculation through insert calculation field with other cell the calculation is not correct/ it seems like the "show values as "% of the column total"" disturbing the calculation resolve this thing
View 14 Replies
View Related
Jul 29, 2013
I have a large spreadsheet set up with multiple rows, I have the formula -
=a5&"repeated text"&c5
this brings back 2 results
test repeated text test
repeated text
In the instance this is the latter with no combined cell vale, how would I mass delete them?
View 2 Replies
View Related
Sep 16, 2013
I have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50
aaple blue 75
aaple yellow 80
apple green 90
grape red 12
grape blue 17
grape yellow 45
grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
View 8 Replies
View Related
Feb 13, 2008
The following code works great in selecting individual cells and forming a text string with them. What I need to do is to change this slightly andi instead of just reading the cell, have the preceding value added to the current value "(Cells (e,i)" and that new runnng total value is added to the "temp" string.
The concept is 12 cells represent Jan - Dec (numbered 1-12)
I'm trying to perform a running total; where Feb(2) = (Jan(1) + Feb(2)) and Mar(3) = Feb(2) + Mar(3).....
I tried setting x to do the running total and then passing it to "temp" but I kept getting mismatch errors, or buffer overflow errors, or out of memory errors.
Is there someone who can provide some assistance with the proper way to do this? ....
View 9 Replies
View Related
