How Do I Reduce Calculation Time
I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%)
I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong.
I feel I will soon have to give up Excel, or my formulas, or my job ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
How Do I Reduce The Calculating Time In A Workbook
I have been building a spread sheet data base which I think is pretty simple. It now has quite a few formula's and is taking time to calculate when I add data. I have tried to simplify some formula's and get rid of complex one's that can be replaced with simple ones... Is there any advice or suggestions for reducing the calculating time..?
View Replies!
View Related
Calculation Of Time With Two Different Time Formats
Need a point in the right direction with this one! I want to calculate the difference between two time values, the problem is there two different formats as below:- 1) 520.00 - this is a number format 2) 500:00:00 - this is a time format HHH MM SS What could I use to show I have a 20.00 hour difference???
View Replies!
View Related
Calculation On Time
I have been burning brain cells trying to figure this out. I get these numbers from an online source and they come in like this: A B C D E 1/1/0912:01AM02:40AM11:18AM07:55PM The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem. What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column. This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.
View Replies!
View Related
Time Calculation ...
Having been looking round this site for quite some time now and always finding what I needed I am now a registered member who needs your expertise. I have a spreadsheet for which I need to calculate hours worked depending on a few criteria. [data] ... The criteria is that Sat/Eve is 8pm to 6am weekdays and midnight to midnight on a saturday. Sun is midnight to midnight on a sunday, BH is a bank holiday and basic is everthing else. What I want to know is it these columns can be populated automatically using formulas. I would really appreciate it if someone out there is up to completing this challange, as I have to manually populate this at the moment and it can be 5000+ lines long (it takes hours). If i need to change the layout it's not a problem, whatever it takes to automate it has got to be worth the effort.
View Replies!
View Related
Same Time Calculation
I am trying to get a total column that will give the total only when two particular devices are down at the same time. This total will be taken from a long list of downtime entries for different devices but I only want the total when two particular devices are down, for example Devicedatedowntimedateuptimetotal time 102/01/0911:00:0002/01/0911:09:0000:09:00 202/01/0911:00:0002/01/0911:04:0000:04:00 202/01/0902/01/09 103/01/0903/01/09 303/01/0903/01/09 604/01/0904/01/09 204/01/0913:09:0004/01/0913:12:0000:03:00 104/01/0913:02:0004/01/0913:15:0000:13:00 505/02/0905/02/09 total 1/200:07:00 In the example I am just wanting to work out the total time when both device 1 and 2 were down at the same time, above the total would be 7 minutes because for 4 minutes on the 2/1/9 and 3 minutes on the 4/1/9 they were down at the same time.
View Replies!
View Related
Time Calculation
I have a time card sheet that I want to make automatically calculate the time I have worked. It is set up with four columns that can't be changed. The first column is "Time In(TI)", second column "Time Out(TO)", Third column "Time Taken For Lunch(TTFL)", and Finally "Hours Worked". I have no problem getting the formula to calculate the difference in "Time In" and "Time Out" but taking the 30 minutes out of that is causing a rift. If I put the 30 minutes in 00:30:00 format it will take it but it thinks it's 12:30:00 and it displays in 00:30:00 which I want it to display 30. Just to clarify, I am using =(TI-TO)-TTFL in the Hours Worked cell.
View Replies!
View Related
Calculation Of Time Formula
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details.... The formula currently being used is... =IF(G50=E50, H50-F50+(H50
View Replies!
View Related
Variable Time Calculation
[code] ...... =SUMIF($A:$A,">="&TODAY()-1,D:D)-SUMIF($A:$A,">"&TODAY(),D:D) calculates the total in Col D looking back from today. The results gives you 12:00. What I need is a formula that will look back from the current time to 24 hours in the past, NOT 1 day, to calculate the total in Col D. Looking back 24 hours, from 10:00am, the results would be 6:00. Since the first time on 12/12 has not arrive, it will not calculate it, but will look back from 10:00am on 12/12 to 10:00am on 12/11 and calculate a result of 6:00 hours.
View Replies!
View Related
Time Spent Calculation
I need to find out the total time an issue was 'open' (not resolved) during business hours. Issues come in and resolution time is based off of a priority. Priority 1 should be resolved in one hour, Priority 2 should be resolved in 4 hours, Priority 3 should be resolved in 1 business day (8 hrs), Priority 4 should be resolved in 2 business days (16 hrs), Priority 5 should be resolved in 5 business days (40 hrs), Priority 6 should be resolved in 10 business days (80 hours). I have start dates/times field in a column (mm/dd/yyyy hh:mm) , a resolution dates/times field in another column (mm/dd/yyyy hh:mm) and a priority in another column (1-6). I need to calculate the time an issue is in open status (not resolved) based on it's priority but I need for it to take into consideration business hours (Monday thru Friday...7 am to 7 pm)...and not calculate time outside of 'business hours'.
View Replies!
View Related
Formatting With Time Calculation
I have a calculation whereby I use two variables which are 1. Man hours left in the week formatted as [hh] and 2. Amount of work left to do in the week formatted as 0" Man Hrs" I need to carry out a calc which says:- Man hours left in the week - Amount of work left to do in the week. The answer should tell me if I have a deficit or surplus of hours which can then use for planning purposes. Unfortunately the problem I have is that I can't make the formatting the same for both unless someone knows of a workaround. Man hours left in the week = 128. Man hours required to complete all work this week = 224. Therefore I need to say 128-224 = -96. However when I do this this an answer of 16 is returned.
View Replies!
View Related
Textbox Time Calculation
I'm attempting to calculate the total number of hours between a start time and end time using textboxes on a Userform. Here is my latest attempt: Private Sub D24_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim myvalue As String Dim myhour As String Dim myminutes As String myvalue = D24.Value Select Case Len(D24) Case 1 myhour = "0" & Mid(myvalue, 1, 1) myminutes = "00" Case 2 myhour = Mid(myvalue, 1, 2) myminutes = "00" Case 3 The above codes are an attempt to force an entry as time (7, 07, 700, 0700=7:00 / 13, 1300=13:00 etc.). However, the result is not correct. For example: Start Time in Textbox(D24)=07:00 End Time in Textbox(D25)=17:30 Result in Textbox(D27)=0.4375 Desired Result in Textbox(D27)=10.5 I've never calculated times in textboxes,
View Replies!
View Related
Subtotals And Time Calculation
I am trying to calculate the time spent on each task. I have column A with Task IDs and column B with Time. Column A could have the same task ID repeated but the column B has the time for each entry. There is only start time for each task. Where the Task is repeated, want to know how many times the task has taken place and overall time spend on one task ID, calculating from the first entry to the last entry.
View Replies!
View Related
Time Calculation With Colored Cells
I was looking for a solution through the excel questions but could not find anything which would help me. As you can see in column B are two different colored Sun, red and magenta as well as Mon and Tue. I would like to find a formula or macro without creating an extra column which looks for the color magenta i.e. if the particular cells are magenta (Sun, Mon and Tue) then perform an action. I cannot use the formula in K7. ******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18= ABCDEFGHIJK1Month: 2Working - Hours 15% 35% 3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00 515Sat0:006:0006:000:006:0006:00 615Sat20:000:0004:0020:000:0004:00 716Sun0:006:0006:00 0:006:0006:00816Sun19:000:0005:00 19:000:0005:009?? 1022Sat20:000:0004:0020:000:0004:00 1123Sun0:006:00 0:006:0006:001223Sun19:000:00 19:000:0005:001324Mon0:005:0005:000:005:0005:00 1424Mon20:000:0004:00 20:000:001525Tue0:006:0006:00 0:006:001625Tue19:000:0005:00 19:000:001726Wed0:005:0005:000:005:0005:00 18 54:00 28:00 22:00Timesheet [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Calculation Time And Charge Rates
I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows: $1000 + hourly charge = total =<50 hrs= $15/hr >50hrs but <100hrs= $6.50/hr >100hrs=$4.10/hr ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820. I have an idea on how to write the formula, but i am having a little bit of trouble with it.
View Replies!
View Related
Time Calculation - Ignore Weekend
Please see attached Excel File. I would like to calculate Column D depending on below criteria. 1. if both date is same than normal Column C-Column B. 2. if both date is not same than time diifrence should not be count after 17:30 to next day 08:30. 3. I do not want to include weekend (SAT & SUN) time diffrence if both date have. In column D I have filled two cells that I want by formula.
View Replies!
View Related
Calculating Time, Timesheet Calculation
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout: .....A............B..........C..........D.......E.....F 1....Date.........Time IN....Time OUT...Hours... Total 2....01/01/07.....1830.......1930.......01:00...01:00 3....01/02/07.....1930.......2330.......04:00...05:00 4....01/03/07......830.......1900.......10:30...15:30 5 Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm' The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows: =IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
View Replies!
View Related
Converting Long Time Calculation To UDF
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula: =IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60))) to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17). To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated. I'd like to have a UDF so I can just enter the formula: =CalcTime(Start Time,Finish Time)
View Replies!
View Related
Date/Time Calculation For WeekEnds/After Business Hours
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends. eg. Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55 So far I can't get the caluclation that will compensate for the after hours and week ends.
View Replies!
View Related
Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
View Replies!
View Related
Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA). {=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))} All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.
View Replies!
View Related
File Size - Reduce
I currently have an excel file with lots of formulas on quite a few pages and these are carried down for so many records - its sort of a template to generate information from/to certain sheets for many other purposes. - is there any way to compress the file (without actually zipping it) as it must remain a working document?
View Replies!
View Related
Reduce Numbers To X Digits
I have a column with numbers in about 500 rows. The entries are 5 numbers long and others 8. So I thought i could use one of the following: A macro code to tell a cell to delete the first 3 numbers if the entry is 8 numbers long? OR A macro code to tell a cell to reduce itself to 5 digits long starting from the right? Attached is a small example
View Replies!
View Related
Reduce File Size
My excel file has grown to 23Mb. It has around 10 worksheets and a similar number of userforms. Because the file is large, it slows down the VBA code when it encounters a save command. Is there any way I can improve this? Do userforms use alot of memory or is it the length of code? There are a few images on the userforms but the images are less than 1 Mb in total when all added together.
View Replies!
View Related
Reduce Picture Memory
I got a CD with pricing and (high resolution) pictures (for printing). I've built the search/display macros for displaying a page on the net, so all I want to do is reduce the pics (Adobe Photoshop) to save memory - easy, right? Why can't I get the pics out of excel for work in Photoshop? I've done the tools>options>General tab>ignore other applications (clear) thing. I've done the "shift>edit>copy picture" or "right-click>copy" thing - but I can't paste anything anywhere. What's up? I didn't think I was this bad, but I guess I am; life is humbling.
View Replies!
View Related
Reduce File Size By Use Of Macros
I still cant figure out how to write macros. If someone could help me out with this I would appreciate it. Currently I have a worksheet that is 20mb. This file contains what takes up most of the space. I repeat this line many times throughout the worksheet. 010807.zip Once again it is a zero eliminating code. If someone could reduce the number of formulas for me, I think I could figure out how to adapt it to repeat throughout the sheet.
View Replies!
View Related
Reduce The Number Of Lines That Are Highlighted
I have the following code in a User form and I was wondering if there was a way to reduce the number of lines that are highlighted, but obviously still get the required results! Private Sub cbSearch_Click() Dim Product As Range Set Product = Sheets("Master").Range("A1:A300").Find(Val(tbLookFor), lookat:=xlWhole, LookIn:=xlValues) If Product Is Nothing Then MsgBox "Incorrect Product code, try again" Else 'Populate the userform with data tbData1 = Product.Offset(0, 1) 'Down 1 row, over 1 column tbData2 = Product.Offset(0, 2) tbData3 = Product.Offset(0, 3) tbData4 = Product.Offset(0, 4) tbData5 = Product.Offset(0, 6) tbData6 = Product.Offset(0, 7) tbData7 = Product.Offset(0, 8) tbData8 = Product.Offset(0, 9) tbData9 = Product.Offset(0, 10) tbData10 = Product.Offset(0, 11) tbData11 = Product.Offset(0, 12).....................
View Replies!
View Related
Material Order List: Reduce Waste
I am reposting this because I do not think I did a very good job explaining what I am trying to do. I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself. I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet. Attach is the sample data along with a "notes tab"
View Replies!
View Related
Reduce Number Of Active Rows In A Worksheet
i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure... 1. i calculate the last row in column A using lastrow = Range("A65536").End(xlUp).row i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution... 2. i use macros.. this is just an info... 3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.
View Replies!
View Related
How To Reduce Formula Size/improve Efficiency
how to reduce formula size/improve excel efficiency? I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs. I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over. I am curently using: =IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])
View Replies!
View Related
Reduce/Refine Range String Address
I have a string containing the addresses of a range of rows. an possible example: "7:62,63:168,169:270,310:318,319:345,346:350,351:355,360:366" i would like a sub/ function that will somehow be able to combine/reduce this down to: "7:270, 310:355,360:366" would anyone have any good ideas as to how this can be achieved?
View Replies!
View Related
Cut/Reduce Cell Text Length
I have a cell with 200+ character, I only want the 40 first character is there a function that will give me only those 40 first character or do I have to use a "=len" and manually remove the extra characters?)
View Replies!
View Related
Macro To Reduce Lengthy Financial Data...?
I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).
View Replies!
View Related
Reduce Consecutive Numbers By Referencing Every Nth
I need to take every 4th data point from an array of several hundred (col A) and place the reduced array in a new column (col B). I initially tried approaching this in VBA and then tried excel, but to keep it simple i won't show the code that didn't work. Col A Col B 5.001 5.001 5.002 5.802 5.001 5.951 5.003 5.802 5.805 5.801 5.804 6.951 6.950 6.952 6.951
View Replies!
View Related
Transpose Data & Reduce Repeats By Use Of Headings
i have a unique transposing situation and i need a script to transpose my data. i haven't encountered anything like this yet so i wonder if its possible my data is currently in this format: Object1, Property1, Value Object1, Property2, Value Object2, Property1, Value Object2, Property2, Value i'd like to transpose it to: ........... Property1 Property2 Object1 Value Value Object2 Value Value
View Replies!
View Related
Will VBA In Place Of A Function Reduce This Workbook Size
I know that VBA doesn't always slim down workbook size and calculations are calculations whether in VBA or Formula format; but in this instance, do you think VBA would be a better choice? This workbook is currently enormous in size and slow, and I think I can slim it down if I used the right VBA procedures. However, Im not certain what those procedures would be. On two seperate worksheets in a workbook, I have a column that contains a formula that references a third sheet. Sheet 1 = "Order" Sheet 2 = "Turn-in" Sheet 3 = "Bank" Ok, here's the scenario: On the "Order" sheet, I have a list of orders from my company; on the "turn-In" sheet, a list of turn-ins. The "Bank" sheet lists ALL transactions (ORDER AND TURN-IN). On both the turn-in and Order sheets, the formula is pretty much the same; it references a different column on the bank sheet, but does it the exact same way. Ok... The Order sheet lists all order from the company by order number (column A); the formula uses that order number and finds the transaction on the "Bank" sheet and returns the order cost as the result. however, the order numbers on the bank sheet have a 5 digit prefix on them (the prefix is the same on all orders, its used by the bank but only orders with my 5 digit prefix are on the list sent to me and pasted to this sheet. So all orders on bank sheet have the same prefix) The below formula is in Cell C10 on "Order" Sheet; Column C lists the Cost reported by the bank. The formula below references the Bank sheet database, looks for an order number that matches the order number in A10 (Adding the prefix of course), and returns a cooresponding cost for that transaction. =IF(ISBLANK(A10),"",IF(COUNTIF(BANK!$A$4:$W$10001,("ECGGT"&A10))>1,"",SUMIF(BANK!$A$4:$W$10001,"ECGGT"&$A10,BANK!$F$4))) I know this seems long for what it is, but it has to add the prefix, and then match the order number, and reference the cooresponding cost. The kicker, this forumla is in more than 10,000 cells; 6,000 on the order sheet, and 5,000 on the turn-in sheet. Of course the references are a little different, but the principle is the same for all of them. Is there a way to do this in VBA to save space and resources? This formula is really dragging.
View Replies!
View Related
Reduce Worksheet Scroll Bar Length/Height
I used to have cells in the extremes of my worksheet filled which resulted in the scroll bars being stretched very thin. Since then, I am only using the top-left part of the worksheet but it is difficult to scroll through since the scroll bars are still stretched. How do I bring them back to normal?
View Replies!
View Related
Workbook Containing Macro To Reduce File Size Locks Up When Shared
I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url] Option Explicit Sub ExcelDiet() Dim j As Long Dim k As Long Dim LastRow As Long Dim LastCol As Long Dim ColFormula As Range Dim RowFormula As Range Dim ColValue As Range Dim RowValue As Range Dim Shp As Shape Dim ws As Worksheet Application. ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?
View Replies!
View Related
|
TRACKER
