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.

ADVERTISEMENT

Triple Condition Formula?

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

Triple Quotes In Text

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

Email Dual Sort & Find Macro - Triple Function

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

Conditional Median Formula Into Conditional Quartile / Percentile?

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

Percentage With Formula Or Conditional Formula

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

Conditional Formula For Row With 10+ Cells?

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

OR Formula In Conditional Formatting

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

Conditional Formula Results

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

Is There A Formula For Conditional Concatenating

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

Set Up Formula In Conditional Formating

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

Conditional Formatting With Formula?

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

Conditional Format With Formula For Each Row

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

Formula Using Conditional Formatting

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

Conditional Range Formula

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

Conditional Format If No Formula

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

Conditional COVAR Formula

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

Conditional IF Function/Formula

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

Conditional Formatting Formula

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

Conditional IF AND Formula, Change It To VBA

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

Conditional Formatting Inside A Formula?

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

Conditional Formatting Border Formula?

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

Conditional Formatting - With Two Conditions Within One Formula

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

Conditional Formatting Formula For Value Between Two Other Values

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

Conditional Formula And Calculate Automatically

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

Workday Formula On A Conditional Formatting

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

Conditional Formatting - Formula=text

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

Conditional Formula To Concatenate Names

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

Conditional Formula Across Numbers Worksheet

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

Conditional Format For Formula Or Number

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

Formula For Conditional Formatting Wizard

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

Conditional Formatting With One Cell Having A Formula?

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

Conditional Formatting Formula - Student GPA

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

Conditional Formatting Formula Lock?

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

Conditional Format Using Formula That Contains VLookup

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

Combining Conditions Within Conditional Formula

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

Complex Logical/conditional Formula

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

Conditional Formula - Calculate Only If Net Unit Is Zero

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

Combining Formula And Conditional Format

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

Conditional Formatting Based On Now Formula

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

VBA Conditional Formatting Formula Changing?

Dec 22, 2011

My VBA code should post the formula "=IF($B6=""Store URL"",TRUE,FALSE)" into the conditional formatting formula box but everytime it pastes something different, such as "=IF($B11="Store URL",TRUE,FALSE)"

The full code is:

Sheet1.Select
Set ConditionalFormattingRange = ActiveSheet.Range("B6:F505")
ConditionalFormattingRange.FormatConditions.delete

[Code].....

View 5 Replies View Related

ADVERTISEMENT