I have the attached table. I need to format with conditional formatting with the following criteria:
1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.
I did the formatting in conditional formatting but the it did not results in the desired solution.
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:
http://www.excelforum.com/excel-gene...-new-post.html
I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
I have set of column data A & B In those A contains Serial No & B contains Ticket No...
I am looking for formula to solve the function by those conditions... almost get done by countifs functions.
1.If Serial & Ticket No Only Once result should be "FIRST TIME LABOUR ONLY"
2.If Repeat Twice For The First Serial No And Ticket "Labour Only", For The Second Serial No And Ticket "Labour & Parts"
3.If Serial No Twice But Ticekt No is different for both serial no Result Should be "PART USED 1 OF 2"
4.More Than 2 Times Serial & Ticket No Repeats" Result Should Be "CCI"
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View RelatedI need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedHaving some problems with a simple vlookup formula.
Never experiened this before.
I keep getting a #ref
Here is the forumla I am using
=VLOOKUP("JK09",Feb!A2:A9,13)
What I am expecting is for excel to look up the value of "JK09" within the range of A2:a9 within the worksheet Feb, and when it finds it it will give me the value in coloum 13 of the macthing row.
Is it possible to have a formula that will return the answer as the value.
For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.
I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.
Would this require a macro?
I am trying to add a formula to a cell but I get this error, #NAME?
Here is the formula: =((pi * (5^2)) / 2) - ((5^2) * ASIN(1 - (A73 / 5))) - ((5 -A73) * SQRT(A73 * ((2 * 5) - A73)))
I'm trying to add it to cell B73,
my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.
I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:
I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1
Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.
Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....
I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E
Machine 2's expected results have been filled in....
I have an excel sheet which has been exported from an 'Employee Attendance Software',
http://www.flickr.com/photos/34247990@N06/4299288997/
In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.
Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion
http://www.flickr.com/photos/34247990@N06/4299288959/
I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.
IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%
Is there a =LOOKUP formula that can be used here?
how to change a proportion of text in a resulting concatenation
formula.
cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell
A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is
there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?
I have the new version of excel and I can't figure the correct formula to do the following.
Attached is my example.
A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1
Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.
View 9 Replies View RelatedI am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:
I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).
Here is my existing Keyword highlighting
Sub Highlight_Keywords()
Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long
I have quarters of the year. I'm wanting to find a function wherein when I punch in for example year 2012, the cell would give me the sum of the 4Qs. If if change it to 2013, then it would give me the sum of 4Qs for 2013.
See attached sheet : sum from blocks of cells.xlsx
I am trying to combine text from multiple cells into one cell. I am referencing cells that reference cells on another tab. I have tried several formulas (see attached) but I have not been able to get rid of the zeros in the resulting string. I know I could reference the original cells, but I would like to find a solution to this without using VBA.
View 3 Replies View RelatedI am importing a .txt file into Excel that has two columns of dollars and cents. When I do my subtotals, all subtotals should be zero. Some of the totals show as 0.00, some as (0.00) and some as a dash which is what I would like for all of them. I have tried: ...
View 9 Replies View RelatedI am working on project of doing Turn around time Analysis of the volumes of work recieved before 12:30PM and after over a weeks time. My data consist of Work ref, Datein, Dateout, Timein, Timeout. To carry this analysis I have to Mannually filer the data as per Datein / Timein and Dateout and then count the no of work ref completed within Same day or one day or more than one day for each date of a given week. I recorded a Macro which gives count of no of work. for one particular date of week and repeated the same for each 5 working dates of given week. this Macro has gone to big and I want to use some looping technique which would change my filtering criteria. I tried doing this by assining a variable to Criteria but Macro gives no values.
Range("A1").Select
Selection.AutoFilter
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="=04/02/08", Operator:=xlAnd
Range("E1").Select
Selection.AutoFilter Field:=5, Criteria1:="<=12:30", Operator:=xlAnd
Range("IV1").Value = "=SUBTOTAL(9,H2:H272)"
Selection.AutoFilter Field:=9, Criteria1:="=L*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B3").Value = Range("IV1").Value
Selection.AutoFilter Field:=9, Criteria1:="=S*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B4").Value = Range("IV1").Value
I want to repeate the above code for different dates of week 04/02/2008 to 08/02/2008. How to change Criteria using Loopin technique.
My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!
View 3 Replies View RelatedI need to determine federal tax withholding from gross pay.
If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%
If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?
I tried this one: =IF(M10
I have two columns of adjacent data Call them A & B (although this could differ)
If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.
Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies View Related