Working Out How Many People Reach Specific Condition
Dec 11, 2013
I've put together a spreadsheet look at the time taken for a vehicle to get from one building to another when called. I've set up conditional formats where the timings turn orange after 5 minutes and red after 10 minutes taken. What I also have to show though is who is taking the vehicle and break it down to show any delays to patients. Is there a formula that will automatically tell me when there has been a delay to patients?? Be amazing if there is as it would save me uber amounts of time as i have to collate a whole years worth of data!
I've attached a small sample of what I've done to show you how the spreadsheet looks.
sample.xlsx
View 5 Replies
ADVERTISEMENT
Jan 1, 2009
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)
Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.
View 2 Replies
View Related
Aug 19, 2014
I am trying to count the number of extra people scheduled for each hour of the day, from 2 dropdowns in A3:B7
The formula in (1) D11:D26 works, but when the shift passes over the midnight it will not count accurately.
The formula in (2) E11:E26 also works well, but only counts the number of times a particular hour is mentioned, but not the number of extras in C3:C7.
The results in (3) F11: F26 are what I need, which is based on looking at the times from the dropdowns and using the number of extras in C3:C7.
View 2 Replies
View Related
Sep 10, 2013
I am trying to average a range of data in column B, the values range from 0 up to 100. I want to average the data in column b for the lowest 5 people with a value higher than 0. I have searched the archives and the formulas i have tried give me a "False".
this is the formula I was using=IF(COUNTIF(B3:B22,"
View 4 Replies
View Related
Feb 24, 2014
I want to change country name 'California' and 'Belziuma' with new country name as 'USA' in column B2.
Similarly change country name 'Moscow' with new name 'Russia'.
If cond not working.
View 1 Replies
View Related
Feb 1, 2014
I try to add some conditions to get specific text. i.e.
if cell b4 <= 20% then cell f4=01.30min
if cell b4 >= 21% and <=50% then cell f4=02.00 hr
if cell b4 >= 51% and <=70% then cell f4=02.30min
if cell b4 >= 71% then cell f4=03.00 hr
I tried different ways, including select case but unsuccessful.
View 8 Replies
View Related
Jun 3, 2014
I want to know if it's possible to calculate the probability that a certain range of values, reach to a certain point.....
The range is this (example):
4
3
9
7
14
22
15
20
42
46.....
What I want to know is if this tendency will reach to 1000. Or how many steps more will be need to reach 1000.
Like I said, and don't know if this is in the right place, but if you put this values in a graphic you will see that the tendency is growing. I assume that this tendency will reach to 1000. But will be in the next 10 steps or in the next 30.
View 3 Replies
View Related
Apr 21, 2009
Is there a way that Excel can automatically enter to next row once the typing has reach the end.
Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?
Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.
View 9 Replies
View Related
Dec 7, 2007
I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.
In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.
View 9 Replies
View Related
Mar 14, 2014
Let's say I had the following data ...
47
50
50
50
44
50
46
50
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
View 4 Replies
View Related
Jul 30, 2009
I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.
So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.
View 3 Replies
View Related
May 12, 2007
I searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created"?
Sub prettyPattern2()
Dim lRow As Range
For Each lRow In Range("A6:K500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow
End Sub
View 8 Replies
View Related
Mar 24, 2009
I have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.
What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
View 4 Replies
View Related
May 21, 2009
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month.
View 11 Replies
View Related
Jan 14, 2006
function that add to any cell that doesn't reach the disired amount.
If the required number is 14 and a calculated cell comes up with 13 or less; I need a funtion to take that number and add a penalty number to it.
For example: For arguements sake the penalty is $150.00. Suppose cell 1A has 5 and cell 1B has 5; cell 1C adds them for a total of 10. I need cell 1D to realize that the number in 1C did not equate to atleast 14 and therfore 1D should show $150.00 and for every cell selected that falls short of 14 should add an additional $150.00 to 1D.
View 9 Replies
View Related
Aug 27, 2007
Below is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average.
Jan 89%
Feb 88%
Mar 83%
Apr 89%
May 90%
Jun 86%
Jul 82%
Aug
Sep
Oct
Nov
Dec
Goal 99%
View 9 Replies
View Related
Aug 11, 2014
I have this code (not sure from which thread) which can export my worksheet with specific file name & folder (according to date and part type). I tried to put it in my worksheet but it's not working.
[Code]....
and it's highlighted at following part
[Code] ....
I received this error from the message box : Run-time error '1004'
Document not saved. The document may be open,or an error may have been encountered when saving.
View 14 Replies
View Related
Jan 25, 2013
I'm looking to setup a macro that will print specific pages on the current worksheet. What pages will print in that same worksheet will be determined on the value of a specific cell.
Here is what I have that isn't working.
VB:
Sub Print_Specific_Pages()
'''''''''''''''''''''''''''''''''''''''''''''Prints only pages that contain data
''''''''''''''''''''''''''''''''''''''''''''
If Range("B3").Value = 0 Then
Worksheets.PrintOut from:=1, to:=1 'Prints page 1 when there is no data.
[Code] ......
View 5 Replies
View Related
Mar 13, 2014
I create a macro to copy cells for data starting from A4 to N4 from sheet - Register to 'Case History' when the cells in column N are 'Yes'. Also, would like to copy specific range from A-N and not the entire row. Please refer to the attachment.
Tracker Dept..xlsx
View 2 Replies
View Related
Jan 3, 2013
I'm trying to create a formula that tells me the following:
If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.
View 2 Replies
View Related
Oct 28, 2005
I am looking for a Formula that can process the following:
The Sum Target Value is variable e.g.; 147
I have two columns of numerical values: Column “A” and Column “B.”
Column “A” Houses the Numerical Labels that I wish to have Returned when the
Sum Target Value is processed /reached - Subtract Sum Target Value as noted
below.
To Subtract Sum Target Value:
Start from LAST non-zero numerical value in Column “B” and Subtract one Cell
value at a time (or Sum up the Column) until the Sum Target Value or nearest
possible Sum BELOW, the Sum Target Value is reached. In this instance, it is
147. I wish to Sum the values in Column “B” to 147 per the above. The
summed values can be below BUT NOT over the Sum Target Value.
Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”)
and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target
Value. The Result – Numerical Label should come from Column “A.”
Col “A” Col “B”
2003
2051
2105
21511
22010
22515
23016
23510
24011
24529
25020
Reaching the Sum Target Value of 147 in Column “B” would go up to value 26,
Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but
including the value of 31 above it, would exceed the Sum Target Value of 147.
The required Result is returned from Column “A” Label 290 which is ONE Cell
to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column
“B”).
View 14 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
Feb 28, 2014
I have a workbook used for scheduling purposes and you can add hours to it by typing in D+1 or D-1, etc. I want to prevent this from working and just ignore the cell if the cell contents say "DC". I've tried to adjust my formula but haven't had any luck. I've attached an example worksheet with the formula for an easier understanding.
Formula: [Code] .......
Attached File : Testsheet.xlsx
View 5 Replies
View Related
Jul 22, 2013
This very simple color code below worked before but now it gives "Application-defined or object-defined error". When I try it on a new workbook, it works fine.
Code:
Sub sdsdfsd()
Selection.Interior.Color = 65535
End Sub
View 1 Replies
View Related
Nov 26, 2009
Is it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.
View 6 Replies
View Related
Jul 2, 2008
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.
Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16
View 4 Replies
View Related
Nov 7, 2012
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
View 3 Replies
View Related
Apr 6, 2012
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
View 3 Replies
View Related
Aug 22, 2013
The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder
My modified code is:
Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String
[Code] .........
View 1 Replies
View Related
May 20, 2006
Suddenly when I open a ExcelWorkbook Microsoft Visual Basic shows a messagebox stating: "Runtime faillure 9 Subscript out of reach" only the closebtn is enabled. After closing the box the workbook opens normally.
View 2 Replies
View Related
