# Two Conditional Formulas In One Cell

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
ADVERTISEMENT
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
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
Dec 3, 2013

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

View 4 Replies
View Related
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
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
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
Mar 20, 2013

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???

View 3 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 15, 2008

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 Related
Dec 11, 2013

I'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)?

View 11 Replies
View Related
Jan 10, 2013

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 Related
May 13, 2008

I 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.

View 4 Replies
View Related