Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?
In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.
How would that work? My worksheet is posted below: ......
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies View RelatedI need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75
I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C
I'm having an intelligence lapse and I need help with a formula
Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.
I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.
The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.
I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.
=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).
This produces a blank or error result.
=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))
This produces an error whatever is in the J column
I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100
Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.
The name of each student appears in CL2:CL100.
I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.
I am currently using this Formula to Search a table on sheet 2 (Tabelle2) and copy the date in the relevent cell (Cells) into the correct cell on Sheet 1 (Master Sheet).
[Code] ..........
Used in cells E10:AZ30 and E33:AZ46
This works perfectly and is the formula behind all relevent cells in Sheet 1.
I would like to keep this but to add that it also copies the background color of the cell in Sheet 2 to the cell in sheet 1.
If i can Bring this Formula into VBA and add the color changing part, It must only work on the cells listed above..
Is it possible to reference a cell's colour in a formula within a spreadsheet - without using VBA? i.e If Cell A1 has a fill colour of yellow, can I have a formula in cell A2 that says If Cell A1 fill colour = Yellow, result = 1 etc. I know this can be done in VBA, and I have done that, but just wondered if possible this way.
View 4 Replies View RelatedI'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:
<10000 sales = 0% bonus
>=10000 sales but <=29999 sales = 5% bonus
>=30000 sales but <=to 44999 = 7% bonus
>50000 sales = 10% bonus
Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.
I have my test cases in below format and I would like to calculate # of test cases passed or failed using formula.
-------------------------------------------------
Test case #Step #Result
-------------------------------------------------
Test case 1Step 1pass
Test case 1Step 2pass
Test case 1Step 3fail
Test case 2Step 1pass
Test case 2Step 2pass
Test case 2Step 3pass
Test case 2Step 4pass
Test case 3Step 1fail
Test case 3Step 2fail
I need below result using formula:
# of test cases - Pass = 1
# of test cases - Fail = 2
Is there a way to embolden/highlight/colour, etc. a cell and its contents when a formula in that cell has been overwritten with text?
View 2 Replies View RelatedI would like to add a value to a cell. The value in this cell will either be 1, 2, 3 or 4.
The value of this cell needs to be "1", if the cell to its left is "Red".
The value of this cell needs to be "2", if the cell to its left is "Purple".
The value of this cell needs to be "3", if the cell to its left is "Green".
The value of this cell needs to be "4", if the cell to its left is "Blue".
I believe I need to find the HEX values for the colors, but that is no problem, I just need the formula of the function if you know it.
Is it possible to have a formula check the color index of a cell?
Example if A1 color index = 6 then X
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.
View 14 Replies View RelatedI am not looking into a VBA solution for this one and want to keep it simple. I want to enter a basic IF function/formula to make the color of a cell yellow if the condition is true.
Here is where I am stuck/what I am putting inside the cell:
=If( 'Program Tracker'!E16="","",IF('Program Tracker'!E16>= DATE(2006,3,3),IF('Program Tracker'!E16<=(DATE(2006,3,9)),"YELLOW","")))
Where you see "YELLOW" is where I need to put something in so it makes the active/current cell color yellow.
If I have a cell containing a formula say c1= sum(a1..a10) and the output appears in a50. how do I colour code it so I know the formula in c1 is linked to the output in a50?
View 5 Replies View RelatedI have this worksheet, that was created by some one else, that has formulas all over the place. What I want to do is click a button and have all the cells that contain a formula to change the font color to red and fill the cell with a certain color.
I am going to create a userform with one command button. I know the code will go in the command button.
My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.
For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.
On Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
=SUMPRODUCT((INDIRECT("'"&B2&"'!$B$3:$B$200")=$A$2)*(INDIRECT("'"&B2&"'!$C$3:$C$200")="Yes")*(INDIRE CT("'"&B2&"'!$E$3:$E$200")))
This gives me the intended result. B2 in this case is January. C2 is February and so forth.
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
My formula is =IF((C2+E2)>D2,"WIN","LOSE") the new formula if I wanted the cells with "WIN" to be green, and the 'LOSE' cells to be yellow. Excel 2010
View 6 Replies View RelatedI have a spreadsheet that contains the 5 digit numbers in the rows and the columns respecstively. I'd like a formular or macro to change font color for each cell. If the combined value of the 5 rows are greater or less than the combined range 87030 and 87200, the 5 cells will be changed to Red. If:.........
View 4 Replies View RelatedI have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?
BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326
Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)
How do I test if a cell has #N/A?
View 4 Replies View RelatedSummary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.
Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else: MyCell = ""
End If
End Function
How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)
View 4 Replies View RelatedI am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long
[Code].....