Tracker
HOME    TRACKER    Excel

Triple Conditional IF/AND Formula

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.

Related Forum Messages:
Triple Condition Formula?
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")

Triple Quotes In Text
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?

Email Dual Sort & Find Macro - Triple Function
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? ........

Percentage With Formula Or Conditional Formula
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.

Is There A Formula For Conditional Concatenating
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.

Formula Using Conditional Formatting
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.

Conditional Range Formula
Using this basic version of my data:

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.

OR Formula In Conditional Formatting
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?

Conditional Format If No Formula
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.

Set Up Formula In Conditional Formating
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]

Conditional Formula Results
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?

Conditional IF AND Formula, Change It To VBA
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

Conditional COVAR Formula
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.

Conditional IF Function/Formula
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.

Conditional Formatting Formula
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)

Conditional Formula - Calculate Only If Net Unit Is Zero
Here's the table for one game

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.

Conditional Color Changing And A Formula
My nominal number is 2.000 and in column A, it can not be larger or smaller than .005 which is in column C. If it is larger than 2.005 or smaller than 1.995, I would like to change the cell column B color to red. I have tried =abs(a1-b1)c1. but does not do what I want it to.

Conditional Format For Formula Or Number
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.

Complex Logical/conditional Formula
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?

Formula To Count Conditional Formats
I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.

The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this:

if cell is numeric
if column in (E, M, X, Y)
if (value <c1 or > c2)
highlight in blue
end
else ' columns not in (E, M, X, Y)
if (value > c1 or < c2)
highlight in red
end
end
end

My attempt to put together a formula for each cell appears to work, it looks like this for cell B3:
'=AND(ISNUMBER(\$B3),
OR(
AND(.....................

Conditional Format Formula Modification
I'm using this formula in a Conditional Format rule.

=CELL("row")=ROW()

Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?

Conditional Formula, Then Delete Rows
In column A, is a timeline - based on every 15 minutes.
In column H, I use conditional formula to change the cell color of any cell equal to or greater than the value of 00:00:07.
I would like to delete all other rows in column H that is under 7 seconds.
I would like the rest of the worksheet to stay in tact - so I can see the timeline. (or at least column A)
I have tried several macros and vba codes, and it either deletes all the rows or simply does not work with my worksheet.
I am unable to attach a worksheet, but I copy/pasted a simple view. This continues down the page, until 24 hours is complete.
Where 00:00:00 is, I am looking for anything over/equal to 00:00:07 - then delete the rest. The cells are formatted as 'general'.

Formula In Conditional Format Range
I want to gray out a row of cells (B11:M11) when "x" is entered in cell M11. Suggestions for using Conditional Formatting or a formula would be great. I have tried a couple of formulas but none work.

Conditional Formula And Calculate Automatically
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)))))))

Formula For Conditional Formatting Wizard
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),””))

Conditional Formatting Based On Now Formula
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...

Workday Formula On A Conditional Formatting
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.

Conditional Formula Across Numbers Worksheet
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.

Combining Conditions Within Conditional Formula
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")

Combining Formula And Conditional Format
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.

Conditional Formatting - Formula=text
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 -

Conditional Formatting Formula For Value Between Two Other Values
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.

Conditional Formula To Concatenate Names
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.

Conditional Formatting - With Two Conditions Within One Formula
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.

Conditional Function/Formula Across Worksheets
I have 7 sheets named: joe, kelly, meg, nate, dave, tom, sam. To keep it simple, in cell B2 on each sheet there is either an "8" or it's blank. On an 8th sheet I'm trying to determine a formula to use that will display the person or persons if Cell B2=8. So if just meg's B2 has 8 and tom's B2 has 8; the formula would produce meg & tom.

Relative Conditional Formatting Formula
Is it possible to have the conditional format auto adjust the cell value range (similar to how the drop and drag with the autocomplete cell values turned on works)?

For example:

If I conditional format: A1 - does not equal A2 then change colour to blue. Then use the Format Paint to copy the A1 conditional format to B1. Is there any way that it can auto adjust the cell reference to change to - does not equal B2 (the next cell down and so on) rather than having to manually go to each cell and adjust the cell reference?

Ultimately I want the following to happen:

A1 - does not equal A2 (colour blue)
B1 - does not equal B2 (colour blue)
C1 - does not equal C2 (colour blue) etc

Conditional Formula To Calculate Total
I need a formula to calculate a total based on two conditions.

Multi-Conditional Table Formula
I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.

Conditional Formatting Formula To See Duplicates
I know it can be done cause I've used it before but I can't seem to make it work this time. I want to use the conditional formatting to compare two ranges of cells within one worksheet I know it's something like =match(A2:C12) the two ranges are sitting one on top of the other. . .

More Than 3 Conditional Formats For Formula Results
I have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc

I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.

Formula To Enable Sorting Of Conditional Formatting
I have three conditional formats on my spreadsheet:

Conditional Formula (TIME): Averaging, Dividing
I have Excel 97. I'm working on a spreadsheet and I just can't figure out how to create a conditional formula that will look through Column A & B, then add up the related time value (hh:mm) in Column C.

A B C D
Gender Age Start time End Time
Female Juvenile 23:50 00:10

I tried the SUM(IF((A:A="Male")*(B:B="Adult),C:C)) or something like that.

The times seem to have a problem adding up or averaging if the time value exceeds 24:00 hrs. Or if it's computing numbers that some are before and after 24:00 hrs.

Is there some way to create a formula that will result in something like: Of 5 Adult males, the average start time was 23:50 hrs and the average end time was 01:30?

Formula + Conditional Formatting For No Of Days Before Due Date
Heres what I am trying to do:

I have a due date of 30/10/2007 in cell A2, I would like the cells in row 2 to change to the colour green 15 days before the due date (15/10/2007) and then I would like Row 2 to change to the colour Amber 10 days before the due date (20/10/2007).

This is what I was trying to do but it is not working for me:

____A_______________B__________C
1___Due Date________15__________10
2___30/10/2007______=a2-b1______=a2-c1

The Formula in B2 and C2 work fine.

I was then thinking that I could put a formula in Conditional formatting that says something like
If todays date is equal to b2 then Green else do nothing or If todays date is equal to c2 then Amber else do nothing

I can't figure out the formula for that though.

Change Cell Color W/o Using Conditional Formula
I want to create a function or vba code for a column IF any text in that column is = to Selfpay turn them all to Red text. I want to do this without using conditional formula.

Multi Conditional Formula For Year And Month
I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.

************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]

Conditional Format Formula Based On Two Cells.
I want to create a conditional format formula which is based on values in 2 cells.

Example:

Cell B1 = 100
Cell C1 = 50

I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.

Conditional Formatting - Based On Formula - If One Of 3 Criteria Are Met Then...
I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28. Or function just returns true/false. I would rather not use 2 formulas.

Conditional, Variable Formula: Leave Blank Or Put A 1
I could really do with is this: =IF(F4>0,F4*3.4%+0.2,"") but I need to be able to make it either 3.4% OR 3.9%. What I thought was if I use another cell, say F3 which I can leave blank or put a 1 in, and tell it if it has a 1 it is 3.9% + 0.20 or if F3 is blank then it is 3.4% + 0.20.

Conditional Formatting: Show If A Formula Has Been Overwritten
There is a clever piece of conditional formatting designed to show if a formula has been overwritten. It works by creating a range called (say) CellHasFormula and then entering =GET.CELLl(48,Indirect("rc",false)) in the reference box. In the cell you wish to conditionally format you enter =CellHasFormula. If the formula in that cell is overwritten, then the conditional formatting is displayed.

Simple Formula Construction And Conditional Formatting?
I have used Excel for a number of years and have gotten on ok on my own working out simple formulas myself but I have been stuck on this one for a while. Is it possible to enter a value into a cell and have a predefined formula that works with the data that I enter?? I would like to enter a value between 1-10 and the cell then takes this number and multiplies it by a value in another cell. Eg.

I have a cell that displays £10 (let's say D6)
In another cell I enter 8
I would then like the cell to do the formula =8*D6 automatically, as this value can be different each time (1-10)