Continuous Evaluation Of Time Function NOW()
Mar 4, 2008
1) The following formula returns TRUE or FALSE:
=AND($J$15"",(MOD(SECOND(NOW())-1,6)+10) = COLUMN())
It works fine as long as the w/b is busy doing something!
2) Otherwise if the w/b is not busy, the formula appears to be calculated only ONCE at the current time NOW(), and F9 must be pressed repeatedly to re-calculate the formula despite Tools::Options::Calculation is set to Automatic.
3) Is it possible to have NOW() representing a progressive NOW()?
4) If not, can the above formula be modified such that it is evaluated continually for, say, the next 60 or 120 seconds from the current NOW()?
View 9 Replies
ADVERTISEMENT
Sep 7, 2013
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
View 5 Replies
View Related
Sep 8, 2009
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:
{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}
The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.
So my formula looks like this:
{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}
This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
View 2 Replies
View Related
Aug 1, 2013
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
View 1 Replies
View Related
Apr 15, 2014
As part of a larger formula, I need a way to evaluate a range row by row and generate an array with the results. More specifically, I need to evaluate whether at least one of the cells in a row has a specific value.
So given the following table:
1
1
1
2
2
1
2
2
3
3
When querying the table for the value "1", it should result in the array {TRUE, TRUE, TRUE, FALSE, FALSE}.
How can I achieve this?
View 4 Replies
View Related
Sep 18, 2007
It is to large to upload here and I have removed the sensitive data that would normally be present. The part I'm currently stuck at is the multiple search criteria, one part works while the other does not. Also, I have yet to discover a way to indicate to the user that the correct Row is selected and being edited in the right cells.
View 2 Replies
View Related
May 2, 2007
I have created a non-subjective job evaluation spreadsheet, which takes data obtained from a work daily of an individual and is implemented in my spreadsheet. The columns of areas to be evaluated will average each individuals performance by averaging the individual to the rest of his shifts performance and a point system has been established depending on their work level. The situation I'm running into is I'm using the formula below to establish my point system. In an effort to be fair in every way possible, should a particular column end up with all zeros "0" this would then become the average, which should provide the individuals in this group with three "3" points. My forumula as it is set up will now only give one "1" point, when it should be three. The point system works great other than this one particular problem, when everyone has a zero. I'd sure appreciate anyone's suggestion how I might be able to add one more IF statement to this existing formula. Note: I29 is the cell for this particular column that represents the average of the column with data in it. I28 is 10% above average, I27 is 20% above average, I30 is 10% below average and I31 is 20% below average.
point system is 1 point for 20% below average, 2 points for 10% below average, 3 points for average, 4 points for 10% above average and 5 points for 20% above average.
=IF(ISTEXT(I3),0,IF(I3="",0,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4))))))))
View 10 Replies
View Related
Jun 24, 2008
I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result. So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.
I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.
View 2 Replies
View Related
Dec 21, 2011
I'm using Excel 2010.
A formula I am using is outputting "0" instead of the expected value. Upon evaluating the formula I realized that this was because some of the values - years, in this case - had quotes around them while others did not. Please see the image below for the screenshot of the evaluation.
These values - the years - are being evaluated in the following formula.
Code:
=SUM(
IF(inventory!$F$3:$R$3=$A3,
IF(inventory!$B$4:$B$56=$G$1,
IF(inventory!$D$4:$D$56=$G$2,
inventory!$F$4:$R$56))))
This formula references the following sheet (an excerpt from that sheet). You can also see the formulas found in the cells causing the problem.
*ABC5212/20/20102010Adjustments53*20112011543/17/20112011Carwen Printers559/9/20112011Adjustments569/9/20112011Copeland Printing
Spreadsheet FormulasCellFormulaB52=IF(A52="",C52,YEAR(A52))B53=IF(A53="",C53,YEAR(A53))
B54=IF(A54="",C54,YEAR(A54))B55=IF(A55="",C55,YEAR(A55))B56=IF(A56="",C56,YEAR(A56))
I suspect that the problem is being caused by the output of the formula in these cells. If I simply type in "2011" instead of using the formula in B52:B56, then the first formula in the code section above does not have a problem.
How I can reconcile this?
View 2 Replies
View Related
Mar 28, 2014
I am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies
View Related
Sep 8, 2009
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
View 2 Replies
View Related
Nov 17, 2005
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
View 13 Replies
View Related
Nov 19, 2008
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:
=((I12/100)*25)/D12
After 1pm I would like the formula to be
=((I12/100)*25)/B12
I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
View 6 Replies
View Related
Jan 25, 2009
D: is quantity to be made
G: is number of minutes this will take
J: is the time each order should be finished
Problem
at 12:00 work stops for lunch and resumes again at 12:30
Somehow I need this to be reflected J:
View 4 Replies
View Related
Dec 17, 2008
I m Trying to add some features to it and run into an issue. Here are the fields we are working with!
A1= 13:30:00 17/12/2008
(in other words 1:30pm on date given) - Need to know proper cell number format!!
B1= 23:00:00
(this represents 23 hours) - format for cell is [h]:mm:ss
C1= Unknown function
(This field is my problem!!)
Answer here SHOULD be "14:30:00 16/12/2008"
I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View 4 Replies
View Related
Apr 22, 2006
Here is what I want to do:
Sub test()
Dim num As Integer
For num = 10 To 20 And num = 30 To 40 And num = 50 To 60
msgbox num
Next
End Sub
Rigth now I have to do this:
Sub test2()
Dim num As Integer
For num = 10 To 20
msgbox num
Next
For num = 30 To 40
msgbox num
Next
For num = 40 To 50
msgbox num
Next
End Sub
View 4 Replies
View Related
Mar 19, 2014
Is there a way to select cells with formulas for a top row and then fill down the formulas for these non adjacent, non-contiguous, etc. columns? I can paste a single formula, but not varying.
i.e. Cells A1, D1, and G1 have my formulas, and I want to identify that they have formulas in the first place and then drag down to let's say A10, D10, and G10. I will not know which columns contains cells before hand, and the given formulas are not fixed
View 9 Replies
View Related
Nov 17, 2012
I need to rank a list that is not continious. I have used the rank function before but its always been in list of numbers.
How do i get the rank to work for example, I only want to rank on cells: B3, B6, B8, B10
View 3 Replies
View Related
Feb 17, 2013
I found instructions within this forum to successfully create a macro to start continuous recalcs of my excel spreadsheet to keep now() current.
Sub continuousrefresh()
Application.OnTime Now + TimeValue("00:00:05"), "continuousrefresh"
ThisWorkbook.RefreshAll
Calculate
End Sub
I want to be able to set up another macro to STOP this continuousrefresh macro. I think this should be easy but I am just not getting it.
View 1 Replies
View Related
Jun 29, 2006
I have attached an example Excel file that shows what I am looking for. I have one sheet with a list of parts in inventory. Each part, we'll call them A-F, has a reorder point and will automatically detect whether or not the B column should say order or not. What I want is a second sheet that will list all the parts that need to be ordered continuosly, without the breaks where parts dont need to be ordered.
View 9 Replies
View Related
Jan 24, 2009
is it posible and if so how do I use the IF function to tell cell that
it has a time between 12:00 and 13:00 then add 30 minutes and if not then do nothing
View 4 Replies
View Related
Jan 5, 2007
I finally got an ontime code to work almost the way I want it. What it does not do correctly is necessarly copy paste information for the correct hour of the day. For example if the computer goes down it will start again copy/pasting for the next hour readings. Even if its three hours latter. The log should be blank for three hours since those hours wwere missed. My code is below, how can it be fixed to run "Capture" macro for the correct time of each day and leave the rest blank? Rich
Public LastSoon
Function Soon() 'As Date
Dim tm As Variant
tm = Now
tm = tm + 1 / 24 'next hour
tm = tm - Int(tm) 'time only (no date)
tm = Int(tm * 24) 'on the hour
tm = tm / 24 - 5 / 1440 '5 minutes before hour
Soon = tm
View 9 Replies
View Related
Feb 25, 2014
I have a set of data that has repeating column headers. So, on a sheet called "January" I have "Forecast" and "Actual" headers that repeat over and over underneath "Week 1", "Week 2", etc. On a different sheet (called "2014"), I'm trying to write a formula that will produce the latest or rightmost number underneath the "forecast" header. What I want is to be able to input data into the "January" sheet every week so that I have a record of progress and I want the "2014" sheet to show the latest data for every month.
View 5 Replies
View Related
Mar 27, 2014
Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls‎
View 14 Replies
View Related
Dec 23, 2013
how to to get a continuous number in a cell without the increase in holidays that is Sunday etc.,
View 1 Replies
View Related
Apr 19, 2009
find a continuous data range in Column A of a worksheet with a header in A1. But now I'm thinking that I asked the wrong question. Each day I have new data (a series of numbers) that are moved into column A of a spreadsheet. I never know ahead of time how many numbers will be in the data set. And I need to perform a series of calculations on the data. I need to do the following:
1. Find the range of the data set.
2. Perform a series of calculations on the data set.
3. Report the results in a new location.
If #1 is pre-defined, then #2 and #3 are very easy. However, #1 is causing problems. I can find the range as a string:
View 4 Replies
View Related
Oct 22, 2011
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1
And column R is for sign-X
So far count of sign-2, which is 3 times, must be paced in cell S7,
And then count of sign-1, which is 1 time, must be placed in cell T7,
And then count of sign-2, which is 5 times, must be placed in cell V7,
And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
[code]....
View 8 Replies
View Related
Oct 9, 2003
I'm trying to use Indirect in a formula where named range refers to multiple non adjacent cells; e.g. ever other cell {A1, A3, A5... etc}; however, when I use indirect, I recieve a #ref error. Replacing the indirect with the actual named range will make the formula work just fine, or using a named range that a continuous range works fine as well. It seems that indirect doesn't like non continuous ranges. ?
View 6 Replies
View Related
Jul 31, 2013
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1
And column R is for sign-X
So far count of sign-2, which is 3 times, must be paced in cell S7,
And then count of sign-1, which is 1 time, must be placed in cell T7,
And then count of sign-2, which is 5 times, must be placed in cell V7,
And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
Example table: 1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1P1P2P3P4P5P6P7P8P9P10P11P12P13P14
1X21X21X21X21X221X21X21X21X21X
11111111111111
3111XX22X11111X
322
1
51
[code].....
View 1 Replies
View Related
Mar 6, 2014
I'm a novice when it comes to macros but was wondering would it be possible to develop one to get continuous averages for a set of data. It's a very big data set with my first observation starting at A1 and last at over A19,000. The data is broken up half hourly so the first average would be from A1:A48 the second from A49:A96 and so on. Can a macro be created to repeat this format rather than me having to continuously highlight the different figures to get an average?
View 9 Replies
View Related
