Conditional IF Function By Name
Dec 4, 2008
I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"
I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.
View 3 Replies
ADVERTISEMENT
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
Feb 8, 2014
I would like to use conditional formatting to highlight the max value if the constraints are not violated.
For example,
Row 91 - Profit
Row 94 - Constraint A (750<=A<=855)
Row 95 - Constraint B (12<=B<=17)
So, I tried to type this in conditional formatting but it didn't work ><
=MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),$B$91:$Q$91,0))
View 9 Replies
View Related
Dec 14, 2009
Is it possible to use OR function in Conditional Formatting? I want to do the following.
If A1 = 1, A2 cell is filled in Red
If A1 = 2, A2 cell is filled in Red
If A1 = 3, A2 cell is filled in Green,
If A1 = 4, A2 cell is filled in Green,
I only managed to do the above for 3 conditions as Conditional Formatting only allows me to no more than 3.
View 6 Replies
View Related
Apr 12, 2013
I want to apply conditional formatting in a cell based on the contents of another cell. I can do it if I just want it based off 1 condition (=b151="sample") but would like to if it met 1 of a couple possibilities.
For example, cell H151 I would like to fill green if cell B151 contained the text "sample" or "example". B151 has a list box with about 10 possibilities but I only want a few of those to turn H151 green.
View 3 Replies
View Related
Jul 10, 2009
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
Eg.
Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756
This would need to return 28756.
View 5 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
Aug 25, 2007
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
View 7 Replies
View Related
Mar 27, 2009
I imagine this is a simple conditional SUMIF function. I'd like a cell to add values in e.g. column "d" when that row meets certain criterion in column "a".
In other words, I have a column that has times recorded in minutes, and another that says a person's name which correlates with the times. I'd like a cell on another sheet to give a total sum of minutes for each person.
Ideally, part of the function would translate the minute count into hours/minutes, but I think I can figure out how to do that by changing the format in the cell...
View 6 Replies
View Related
Nov 12, 2009
I am having many difficulties getting conditional formatting using the match function to work correctly in my excel document.
What I would like to accomplish is as follows in the 3Q09 tab. If a subdivision name is found in C-62 through C-70 and a match is found for the subdivision name in AB-24 to AB-66 I would like it so the AB-24 to AB-66 Subdivision name is bolded for every match. I removed the function formulas from the 3q09 tab I used to have since they didnt work and I do not want to confuse anyone.
This is correctly done on the "Working Correctly" Tab included in the same file, so please view this for clarification if you need it. Why it works on one tab and not the other simply baffles me.
View 6 Replies
View Related
Oct 5, 2013
I have three columns named "Type" , "start_date" and "end_date".
Type column can hold only "A","B" and "C".
start_date and end_date columns are time stamps. ex: 9/14/13 9:35 AM
I want to do conditional formatting like,
If type = "A" and (end_date - start_date) value between 30 and 40 (in hrs) then it should be amber.
I did for greater than and lesser than values, but I am facing problem while doing the above one.
View 3 Replies
View Related
May 9, 2006
for my thesis i need to create median industry multipliers. i have a list of
companies with their industry codes and multiples, but now i need to create a
list with medians per industry. is there a function similar to SUMIF for
medians?
View 11 Replies
View Related
Sep 24, 2008
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).
View 2 Replies
View Related
Oct 30, 2013
Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:
Jul-12
6.59%
Aug-12
1.13%
Sep-12
2.61%
Oct-12
-3.56%
[code].....
I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.
View 3 Replies
View Related
Dec 3, 2013
I'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.
I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).
What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17
=IF(AND(A2=LOOKUP(A2,O2:O4),F2=LOOKUP(F2,Q2:Q17),"Yes","No")
Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.
View 8 Replies
View Related
Mar 23, 2009
I behind developed to a time a function of Addition with an only conditional criterion.
I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.
Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores até 32.767
Dim Valor, Nome
Dim lin, col As Integer
Dim Total
Application.Volatile
lin = 1
col = lin
For Each Nome In IntervalSearches
If Nome = name Then
Valor = IntervalReturn(lin, col)................
View 9 Replies
View Related
Apr 24, 2002
Can I have more than 3 conditions for the conditional formatting function?
View 9 Replies
View Related
May 1, 2006
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 8 Replies
View Related
Jan 19, 2008
how to put together a function that will provide for me 1 of 3 results, (0, 1 or 2). Example Attached.
What I would like to do is have a function to have the results for the "Pts" (D) Column be either 0, 1 or 2 based on the following.
PM (D)<1.84 (F:10) = 2
PM (D)<2.25 (E:10), >1.84 (F:10) = 1
PM (D)>2.25 (E:10) = 0
View 2 Replies
View Related
Apr 22, 2008
Data for once source
Column A shipping container numbers (alpha & numerical)
Column B the container size code
Column C relevant date of arrival
Data from another source
Column D shipping container numbers (alpha & numerical)
Column E the container size code
Column F relevant date of shipment
What I want is:
If Column A containers match with Column B containers Then in Column G the containers that matched and Column H the container size code
Column I the date of arrival
Column J the date of shipment
Vlookup seem to be an answer, but can’t get it to work. I am not good in VB, so may be formulas would be better Named range and IF & Match FORMULA may be?!! The list will be approximately 20,000 numbers long. Have attached the sample file.
View 2 Replies
View Related
Jun 23, 2014
I have the following data; B1 and B2 are entered by the user and are named 'heat' and 'race' respectively. Conditional Array Formula Example.xlsx I'm after the ROW number that matches the minimum time (col C) for the heat and race combination shown - in the example below (heat: A, race: 100m), I'd like row '5' returned (or, better still, row '2' of the array A4:D11), as that's the lowest time for Heat A, 100m.
A
B
C
D
[Code].....
but this dosesn't exactly seem elegant, and doesn't work!
ie I simply want the row number (ideally relative to the range) for the lowest time for any given inputs 'Race' and 'Heat'.
View 3 Replies
View Related
Mar 9, 2014
I need the equivalent of a double conditional in the Function SUMIF.
In my example (a test file is attached), I have used hypothetical stock trades as a test case.
Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.
I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.
So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.
I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.
So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.
If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.
I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.
View 9 Replies
View Related
Mar 6, 2012
I have an "actual build" worksheet, which allows the user to select which materials were used, material properties (e.g. material grade etc.), including its corresponding length. The user can enter all these in manually, or select the material name from a pre-defined list (Reference worksheet contains this information), and then the associated properties are automatic lookups from the Reference worksheet.
So all this is fine / completed, and the LOOKUP returns its theoretical length from the reference table. However, its actual material lengths will almost never match the theoretical length, so what I want to happen is for a conditional format to highlight the cell, to notify the user that they need to enter a manual value. The auto-looked up length is still useful a proposed build on the worksheet, but having it highlighted to ensure it is manually entered later is the objective.
The other thing is it can't just check if it's a formula, it has to check if it is a formula with a MATCH/LOOKUP function. E.g. it needs to be unhighlighted if someone has a basic formula, ie = 2*0.42 if there two of the item etc..
Hence, I was thinking of having a conditional formula that "format only cells that contain" and then format only cells with "specific text" and "containing" and "MATCH(" text function, however this doesn't seem to work.
Length cell formula:
=OFFSET('Reference- Materials'!$N$5, MATCH($BO32, Materials_Item_Number, 0), 0) * $AH32
View 2 Replies
View Related
Sep 4, 2008
I'm trying to do:
Column A is full of daily consecutive dates, Columns B to H are full of numbers.
I'd like to create function that accepts 2 dates and a column reference as input like this:
function(date1, date2, columnnumber)
And as output it gives the product of all the numbers in that referenced column between the two dates.
View 9 Replies
View Related
Jul 28, 2006
Can you make the first part (the logical test) be anything other than a numerical value? I want a value to display only if a check mark (using the wingding font for the check mark) appears next to certain items in a list. For example: =If(A1=checkmark,B1+C1,""). If I am not clear maybe I can explain it another way.
View 4 Replies
View Related
May 6, 2014
Using Excel 2010.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
View 3 Replies
View Related
Jun 19, 2013
I am making some conditional formats for a document. One that I am trying to achieve is have a group of cells have a background color, but once the data has been added, the color go away.
This is what my conditional format looks like: =ISBLANK($A$1:$H$22) and chose the Green fill background. So, my understanding is, the cells should have a green background if they are blank, but go back to a standard format, once cells have data put in them.
View 2 Replies
View Related
Dec 18, 2013
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
View 6 Replies
View Related
Nov 12, 2009
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
View 3 Replies
View Related
