I want to apply a score to the time cells ( decimals ) Basically i want to apply this set of criteria to a Row >
IF LESS THAN 0.00694 (10mins) then score 3
IF LESS THAN 0.01388 (20mins ) then score 1
IF BETWEEN 0.01388 & 0.9811 (20mins and 23:40:00) then score 0
IF GREATER THAN 0.98611 (23:40:00 ) then score 1
IF GREATER THAN 0.09930 (23:50:00) then score 3
I am trying to round up the total sum of time to the quarter hour. I enter time in fields as follows:
WORKSHEET SETUP: Column B: Time I begin working Column C: Time I end working Column D: Total time C minus B Cell format for columns B and C: H:MM AM/PM Cell format for calculated field in column D: H:MM Calculation, column D to show total time on a daily basis: =IF(C6>0,C6-B6,"")
However, I want to calculate total time per month in decimals, rounded up to the nearest quarter hour.
Example:
I worked 5 minutes (I do not want to reflect 15 minutes for five minutes of work). When I total time for the entire month (total daily time in column D), I would like that total to reflect total Hours:Minutes rounded up the quarter hour - but in decimals!
Assuming this is possible, how should I format cells, and what would the formula be for the total of hours per month?
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
I have been given a project to develop a spreadsheet using excel that will take data that is for a whole month, and evaluate by DAY to see if the same doctor was listed as attending two patients in surgery at the same time. If the Doctor's name is the same for two patients within the same timewindow for a certain day, a code is applied to EACH RECORD that the doctor was attending in the code field. If there was NO DUPLICATE patient, a different code is applied in the code field.
The tricky thing about this is: 1) Number of records will vary month-to-month 2) There could be 1-xx patient records where the same doctor is listed and there is overlapping time in the SAME DAY OF THE MONTH. 3) The code applied to multiple patients in the code field applies even if the minutes of overlap = 1 minute. 4) Start/End times and length of the patient visits will vary. (Example - Dr. Jones has two patients on the 15th of the month. The start time for patient 1 is 9:00am and ends at 10:15am. The second patient's start time is 9:30am and end time of 11:00am. Under this situation, the multiple patient code would apply)
Does anyone have any experience evaluating by date, then within a time-window to check for record overlap (doctor name in this case)? There could be 200-1500 records per month, and 100/day to evaluate for overlapping.
I am trying to convert Degrees Minutes & Decimals of a minute (12° 34.567') to Degrees and decimals of a degree (12.57611°). I have the formula to convert latitude, that is two digits, but it doesn't want to work with longitudes, three digits. (see attachment). This is the formula that I am using:
I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?
Is it possible through a macro that the cells where numbers is in percentage format should not show any decimal points (is should not roundoff) just decimal points should not visible. I am aware that this can be done by "decrease decimal" but data will be across thousand of rows manually it will be very time consuming.
I have a worksheet that I use to check invoice prices, which automatically indicates to me if any prices have changed, by using conditional formatting. For example, Column A contains original/current price. Column B contains new price (which I enter by hand and is calculated to round off, etc via other cells). If the price changes in B, a simple formula in Column C shows the new price and uses conditional formatting to turn the cell yellow so I can see that it needs to be changed in our system. However, we also must account for shipping charges on our invoice - which change with each order. So, when calculating new price, I also add shipping costs. This same setup repeats for every row of items.
My question: if I change the shipping, but only change the prices on a few rows, the prices on ALL the rows change because of the new shipping. Is there a way to say, select that only certain cells were (received with new price) and have the changes apply to only those cells?
Example:
Row 1 Cell A: current price including shipping from last invoice (which is calculated in several other cells to include shipping, round off, markup, etc) Cell B: changed price (from invoice) Cell C: compares A and B using =IF(F9<>H9,H9,"") and applies conditional formatting if price changed.
Row 2 (repeats)
If we only get a new order of row 1, the shipping changes. But I DON'T want row 2 to change it's price, since the price did not in fact change.
So basically, what I want is to be able to check off, or select, only the rows that come with latest invoice.
I attached a sample worksheet. Excel Forum example.xlsx‎
How do I change this formula to represent a range? I want cells C2:C18, but I ran out of arguments - can't add any more. If DRC or Production shows in the column, I want the word "Outstanding" if not, I want the word "Completed" .....
Any way I can get an equation to only apply to cells with information in? E.g. I have two columns, one with 'disposal dates' in and the other with 'extended disposal dates'. I would like to get the difference between them (Extended disposal dates- disposal dates) however not all of them have an extended disposal date, therefore some cells display as '#VALUE!' because its calculating the disposal date-blank cell....
I have data in columns that I want to create charts for. However, this data is dynamic and the number of cells where there is data varies. The rest of the cells that don't have numbers have 0s, however if I applied a macro to the whole column all of the zeros would show up in the resulting charts. How can I create a macro where I can highlight just the cells (the ones with non-zero numbers) that I want a chart to be generated for. Or is there a way to ignore zeros/blanks completely in the macro?
Example: here are two columns with data. I want to make a macro that creates a chart by just highlighting the actual numbers and ignoring the zeros.
Not sure what I'm doing wrong here but I think my syntax is wrong, here is the
Sub CalculatSG() Dim FinalRow As Integer FinalRow = Range("C" & Rows.Count).End(xlUp).Row Range("BU5") = "=(BT5/100)*AE52" Range("BU5").Copy Range("BU5:BU[" & FinalRow & "]").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
My objective is to apply the same calculation to the range of cells; the range may change which is why I have defined FinalRow and passed this as a refernence.
I was wondering if is possible to apply a conditional format rule to a range of cells only when certain other cells outside of the range are the active cells. eg row C4-J4 has a conditional format to identify duplicates from range C5-C10, but I also want the same conditional rule to apply separately and uniquely if the active cell falls within the next active range D5-D10. So basically only apply conditional formatting separately and uniquely as the active cell moves its way across the columns?
i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136
the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs).
Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour)
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B13:AP162" On Error Goto ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "AP").Value = "YES" And _ Me.Cells(.Row, "Y").Value <> "" Then Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green Else End If End With..................
I have a excel sheet from a supplier of mine, that has a mixture of text, and numbers and more importantly "Custom format cells" that have prices in them. I need to apply a multiplication formula to each price, but they are all in different rows and columns, and it will take forever to type a formula into each cell.
Is there a way that if I can select all these cells, to add a global formula that will update all these cells?
Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...
I'm looking to see if there is a way to take the decimal place formatting of cell and apply it to other cells.
For example, if someone types in 0.0001 into a source cell, I'd like to take that decimal formatting and apply it to other destination cells. This way when values are typed into those cells it will automatically display 4 places past the decimal, no matter what the value. ie 50 will display 50.0000
I have a drop down list that shows phrases for different math functions, like "divide by 2," "divide by 4," divide by 8," etc. When one of these items is selected, I want it to perform that specific calculation on a range of nearby cells.
However, I also want the user to be able to see the original value of the cells if they select "show original value" from the drop down list.
I have attached a workbook as an example. I think macro would probably work best.
I am having trouble getting some conditional formatting to apply to all cells in a column in a pivot table. Currently, the conditional formatting is only applying to the top level items in the pivot but is not applying to the lower level items. I can see why it is doing this. the range in "Applies to" is only specifying the rows that contain the top level items. I tried to change the range to D10:D647 but, it reverts back to just the top level items. How to get it to apply to everything?
I have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.
