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+((f210)*50)
If f2 >=20, 1500+((f220)*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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Triple Condition Formula?
ColAColBColC 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 Replies!
View Related
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?
View Replies!
View Related
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 2550, 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 Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
Conditional Range Formula
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 Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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]
View Replies!
View Related
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?
View Replies!
View Related
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);(C68)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C613)*(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 Replies!
View Related
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 IndexMatch functions but know there must be some simple solution.
View Replies!
View Related
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.
View Replies!
View Related
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)
View Replies!
View Related
Conditional Formula  Calculate Only If Net Unit Is Zero
Here's the table for one game DateBuy(Unit)Sell(Unit)Net UnitPrice($)Net Profit/Loss($) xx/xx2##2125 ## xx/xx##11150 ## xx/xx##10165 +65 xx/xx##4(4)170 ## xx/xx2##(2)165 ## xx/xx##1(3)180 ## xx/xx3##0180 (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 Replies!
View Related
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(a1b1)c1. but does not do what I want it to.
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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(.....................
View Replies!
View Related
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'.
View Replies!
View Related
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)))))))
View Replies!
View Related
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),””))
View Replies!
View Related
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...
View Replies!
View Related
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")
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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 Male Adult 23:10 01:45 Female Adult 21:30 03:30 Male Adult 00:15 01:05 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?
View Replies!
View Related
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______=a2b1______=a2c1 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.
View Replies!
View Related
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]
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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 110 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 (110)
View Replies!
View Related
