# Triple Conditional IF/AND Formula

Nov 30, 2009
I am trying to create a conditional/IF formula contingent on 3 different possibilities for f2, with the formula I wish to use based on these 3 possibilities, as shown below:

If f2 <=10, f2*100

If f2 >10 but f2<20, 1000+((f2-10)*50)

If f2 >=20, 1500+((f2-20)*20)

I'm not sure how to create this formula using three different conditions, especially as it is unclear to me how Excel needs to have the parenthesis placed.

View 2 Replies
ADVERTISEMENT
May 23, 2008

ColA-ColB-ColC- Z

a1 - b1 - c1 -1

a2 - b2 - c2 -2

a3 - b3 - c3 -3

if there a formula to check all the rows,

in cell a1,

if a1=a1, b1=b1, c1=c1, it'll return 1

if a3=a3, b3=b3, c3=c3, it'll return 3

I checked the formula below, from how I read, it only check for 1 condition...

=IF(COUNTIF(db!$G:$G,nvT!E6),INDEX(db!$H:$H,MATCH(E6,db!$G:$G,FALSE),1), "0")

View 9 Replies
View Related
Jan 30, 2009

I'm running the following to put quotes around data in a workbook:

Sub test()

For Each s In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

s.Value = """" & Format(s.Value, "00000000") & """"

Next

For Each s In Range("B1:B" & Cells(Rows.Count, 1).End(xlUp).Row)

s.Value = """" & Format(s.Value, "####") & """"

Next

End Sub

And exporting as a CSV file for loading into another program. The problem is everything looks OK in Excel, but when I open the file in Notepad to check the output, the quotes are all triple. How can I get only single quotes around the data?

View 9 Replies
View Related
May 20, 2008

I'm looking for a macro that can sort emails by 2 very distinct ways, with 1 special find function as well. Not too much eh? ........

View 9 Replies
View Related
Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies
View Related
Mar 14, 2009

I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:

For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.

What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.

Therefore, I came to the conclusion that:

0.25% x 80 = 20 hours

0.50% x 80 = 40 hours

0.75% x 80 = 60 hours

Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.

View 9 Replies
View Related
Jul 28, 2014

I am making the monthly schedule for my colegues. I wonder if there is a conditional formula that can highlight a row with 10+ cells if in one of them I type SUNDAY or something.

I am attaching an example of the schedule. I thing you will get my point.

View 3 Replies
View Related
Mar 9, 2009

Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this,

=$D2=OR(HCAT,HCAL)

but its not highlighting anything, any clues?

View 3 Replies
View Related
Dec 19, 2009

Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?

View 3 Replies
View Related
Mar 23, 2005

I would like to concatenate cells from a column based on the match in the

preceding column with a defined value.

Example:

Col. A B

Row1: T1 Jim

Row2: T3 Paul

Row3: T1 Joe

Row4: T2 Mike

Row5: T3 Caroline

The formula should return for T1 for instance: Jim;Joe;

(for T2: Mike;

for T3: Paul;Caroline

Cells in column A can repeat any number of times.

View 9 Replies
View Related
Dec 29, 2009

I want to set up formula in conditional formating so that when:

$AF6 = "CA" the color is red

or

if $AF6 = ("GA","ME","PR") then the color is blue

what I came up with is Condition 1

= ($AF6 ="ca") [which will turn the cell red]

Condition 2

="OR($AF6={""GA"",""ME"",""PR""}, "")" [which doesn't do anything]

View 2 Replies
View Related
Jul 18, 2012

I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?

2011 % of PDD

2011 % of Total $

0%

0%

0%

0%

0%

0%

[code].....

View 1 Replies
View Related
Oct 21, 2013

I have a file with two columns

Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.

Column B has serial numbers of equipment from column A.

I need to create a complex conditional formatting, which will check:

If A1=EQ1, and B1"HCC*" then make it red,

OR

if A1=EQ2, and B1"ABC*" then make it red,

OR

if A1=EQ3, and B1OR("CDE*","FGE") then make it red.

And then same thing for A2, B2 cells.

I have thousands of rows in both columns.

I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.

I thought of something like:

1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")

2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")

...

But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?

View 2 Replies
View Related
Feb 1, 2008

1. I have a range of random numbers within a column, i.e. A1:A9

2. A specific number will appear in a cell at the end of that column, i.e. A11

3. I need to see via conditional formatting which of the above cells add up to that number.

I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.

View 9 Replies
View Related
Apr 1, 2009

Using this basic version of my data:

NameGradeWeekJim31Sally31Fred32Claire53Susan43Chris52Ted41Jenn53Nicole43

I am trying to figure out How many 3rd grade, 4th grade, 5th graders there will be for each week. I will need a formula to fill in for each blank cell below.

# of 3rd# of 4th# of 5thWeek 1Week 2Week 3

I think I need a formula that would "read" something like:

if "Week" is equal to "1, 2, or 3" then within the rows that contain that week, count all "Grade" that are equal to "3, 4, or 5"

I tried sumif, counta, if, and combinations of them, but I am not sure I am using them correctly.

View 9 Replies
View Related
May 29, 2009

I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.

We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.

View 9 Replies
View Related
Sep 3, 2006

I am preparing data for an econometric application using excel. The format of the data is as follows: (figues are made up)

Portfolio Return(Pn) Return(MarketIndex) Covariance

1 | 2.1 | 1.1 | =COVAR(B?:B?:C?:C? ...

1 | 2.2 | 1.3 | =COVAR(B?:B?:C?:C? ...

1 | 2.3 | 1.5 | =COVAR(B?:B?:C?:C? ...

1 | 2.4 | 1.7 | =COVAR(B?:B?:C?:C? ...

2 | 2.4 | 1.1 | =COVAR(B?:B?:C?:C? ...

2 | 2.3 | 1.3 | =COVAR(B?:B?:C?:C? ...

2 | 2.2 | 1.5 | =COVAR(B?:B?:C?:C? ...

... ... ...

From this I need to calculate the Covariance of a portfolio's returns with the market index. The problem is that I cant seem to specify a condition within the =COVAR(...) formula so that the COVAR Folrmula automatically selects only the applicable pairwise observations... That is, I need to write a COVAR function that will automatically search the first Column for a specific portfolio number and then include the corresponding returns data in the COVAR formula. For example, the first four formula need to return (B2:B5,C2:C5) and the next three formula need to return (B6:B8,C6:C8). I hope this has been descriptive enough of my problem. I am doing my head in trying to use Index-Match functions but know there must be some simple solution.

View 2 Replies
View Related
Sep 8, 2006

Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.

View 2 Replies
View Related
Mar 20, 2007

I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))

The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is

='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)

View 5 Replies
View Related
Jul 11, 2007

I am trying to understand VBA codes. Actually I wrote a formula with IF and I want to change it to VBA because it is so long and creates some problems. It is too important for me =IF(AND(0<C6;C6<9);"0";((IF(AND(8<C6;C6<14);(C6-8)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C6-13)*(26,45));" ")))))))

The logic is like that, this program will calculate an amount.

btwn 0 and 8 days, it is free

btwn 9 and 13 days, 8.4 euros per day

after 13 to infinity, 26.45 euros per day

Therefore for example for 10 days, you have to pay 2*8.4 = 16.8 euros

for 14 days, 5*8.4 + 2*26.45 = 92.9 euros

View 3 Replies
View Related
Jun 21, 2014

I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.

Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).

Remember also that YoY growth can be +100% (or more), 0% and -100%.

View 10 Replies
View Related
Mar 16, 2014

I have a list that, how do i create a border that data contains formula (formula not display/blank)...

i want to create dynamic border with condition if data display/showing must be border shown too...

for this, i want to line "TOTAL" in column AD expands up, based on data display/showing...

note : in cell AF2 as parameter/controller to show data by month (type 2 instead February or 3 --March etc...)

View 5 Replies
View Related
Nov 6, 2008

I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.

View 2 Replies
View Related
Jan 10, 2009

I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.

View 5 Replies
View Related
Mar 1, 2009

I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.

The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.

im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...

this is the formula I have now, obviously it doesnt work

=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))

View 7 Replies
View Related
May 29, 2009

I am having an issue, because aparently workday formula does not work in conditional formattings.

I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.

View 5 Replies
View Related
Sep 16, 2009

I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -

View 3 Replies
View Related
Sep 21, 2009

I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.

View 3 Replies
View Related
Sep 30, 2009

I have 2 worksheet.

Worksheet 1 contains raw data.

Worksheet 2 contains 2x variables that I need to 'look up' in Worksheet 1 and tell if they are 'true' or 'false'.

I thought this would be easy, have tried lookup, and count etc and nothing is working.

View 2 Replies
View Related
Oct 2, 2009

I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.

At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.

I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.

The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.

View 9 Replies
View Related
Nov 25, 2009

I am trying to create a formula in the Conditional Formatting wizard.

I would like the formula to format B2:F2 in a particular way if a count of the number of values in this range equals 5 AND if all values are less than or equal to 25.

I have tried with following formula without success:

=IF(AND(COUNT(B2:F2)=5),COUNTIF(B2:F2,”<=25”)=5),””))

View 7 Replies
View Related
Aug 27, 2013

This is the most simplest conditional formatting which is working except on some cells. I want to compare two columns cells ex : if A=B , then format A as green.

Its working, the "formula" and "cell value contains" everything except for few cells. Cell B has formula's in it related to other cells. When i enter values in other cells, Cell B computes but A refuses to format "green".

If I just enter same value[ex 1, 1]in both the cells sans formula ,it highlights A green . I want to know , if the formula in cell B is causing the errors in formatting and if it is

View 2 Replies
View Related
Oct 22, 2013

I have a grading system for university students, my spreadsheet looks like this:

Student Name Academic Calendar GPA Fall GPA Winter GPA Spring GPA Summer

John Quarter 3.00 2.56 2.89 3.6

Jane Semester 3.1 n/a 3.21 3.7

I think I will need to do some conditional formatting to make it easy to fill the student GPAs .

I was thinking how to enter a formula to have the GPA winter appear as shaded when I enter "semester" in the Academic Calendar column.

View 9 Replies
View Related
Apr 29, 2014

I have a sheet which contains conditional formatting formulas.How i can Lock or protect the forumluas to view others.

View 2 Replies
View Related
Mar 13, 2014

I have a set of data in A:P

I have a named range "TR" in which I need to look up a value

I would like to conditionally format Columns M:P if the value in the cell is less than a value in the lookup table

Column M corresponds with the 2nd column of the lookup table

Column N corresponds with the 3rd column of the lookup table ETC

My formula would be:

if M2 < Vlookup(b2&g2,TR,2,false) then conditional format

if N2 < Vlookup(b2&g2,TR,3,false) then conditional format

etc for Columns O and P

I have attached a sample workbook. TEST CONDITIONAL FORMAT.xlsx

View 2 Replies
View Related
Apr 23, 2009

i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need.

Basically i need to say the following:

=if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")

View 3 Replies
View Related
Jun 12, 2006

I am working between two worksheets. In one worksheet I have a list of

account numbers. In the second worksheet I have the same account numbers in

a different order. What I am trying to do is, If the account number from

worksheet one is found in the column with the account numbers in worksheet

two, I want to display the matching title for the account number that is in

the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in

column m from wks 2, display the corresponding title in the same row of the

matching acct # found in column m). Is this possible?

View 10 Replies
View Related
Jun 27, 2006

Here's the table for one game

Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net

Profit/Loss($)

xx/xx----2----------------##--------------2--------------125---------- ##

xx/xx----##---------------1---------------1--------------150---------- ##

xx/xx----##---------------1---------------0--------------165---------- +65

xx/xx----##---------------4--------------(4)-------------170--------- ##

xx/xx----2----------------##-------------(2)-------------165---------- ##

xx/xx----##---------------1--------------(3)-------------180---------- ##

xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.

## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net

unit), it will calculate the net profit once.

View 83 Replies
View Related
Dec 3, 2009

I would like to combine a formula with conditional format:

In P10

If I4 = "FPI/FPI" and P10 = "Y", then the cell pattern is a different color.

View 4 Replies
View Related
Feb 19, 2010

My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks.

The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the -40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise).

What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information.

Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...

View 3 Replies
View Related