I am having a little trouble with a spreadsheet I am creating, the formulas and cell formatting I should be using to enable this to work.
Here goes:
in cell E3 I have a time started (e.g 12:45 pm), In F3 i have time finished (e.g 2:30 pm)
So, what i'm wanting is the time taken in G3 and also, i have the amount of units that is entered manually in H3. I would like I3 to show the time taken per unit.
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
field A1 & B1 are variable user input. The goal is to subtract B1 from A1, take the result and divide it by 176 (a fixed number) and display the result as minutes:sec.
For example:
A1 B1 14,000 (minus) 2,200 (= 11,800 /176 =) 67 I want to result (67) to display in field C1 as 01:07 (min:sec)
I tried (A1-B1)/176 and formatted C1 to be min:sec but the result displays as 5:27 I have no idea what that number represents.
So say you have a loop running a large number (on the order of 1 to 10 billion loops) and you want to exit after some amount of time x, what is the most efficient way to do this?
doing something like:
Code: stTime=timer do while timer-stTime < x *do stuff* loop is slow...
i also could approximate the time per loop and only check when the counter passes that general area...but i dont want to use a counter either
I have been looking all over and cannot find an answer on how to do this. I work for a call center that takes inbound technical support calls. We recently added a chat support option for our end users. Our chat agents can take up to 3 chats at the same time. I need to determine the total amount of concurrent chats for each agent per day as well as the total amount of concurrent time for each agent per day. Below is a sample of my data. Notice how the start time on row 5 is earlier than the end time on row 4. I need excel to count this as a concurrent chat and then calculate the amount of time that was concurrent (in this example it would be 16 minutes). Currently I have 2000 rows of data and 30 different agents.
Is there a way to freeze the user input when it first comes up for a given amount of time. In essence, I am trying to force the user to read before clicking.
I have the following code below for a timer in a userform. Right now it is configured to start when a button is clicked. I would like to have it automatically start the timer after the workbook is open for more than 5 minutes but so far have been unable to get it to do so. I have tried putting the code in the workbook module but it still won't run. It also has a button to reset the timer if they need more time in the workbook and also a button for them to save and close the workbook if they are finished. I'm running Excel 2007.
VB: Private Sub CBReset_Click() Dim T, E, M As Double, S As Double T = Timer Do E = CDbl(Time) * 24 * 60 * 60 - T 'elapsed time in secs M = AllowedTime - 1 - Int(E / 60) S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that? For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).
Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
I have a list of names and prices as shown in two columns as a master list:
Apple and Lime 3,5 Assam 2,5 Chamomile Flowers 2,5 Chamomile Lemongrass3,5 Chilli mint 3,5 (the list is a lot longer than this!)
I have created a pick list using data validation to pick any of the names to insert elsewhere in the spread sheet. How can i get the next cell adjacent to this name to search and input the corresponding price?
I need to create a price list a we give a discount based on the price of the product for example:....
All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
I have a routine that looks a variable's first letter and then assigns it to a specific folder. I have tried using just the first letter (firstletter) and the ASCII value of the first letter but neither seem to work - see below.
firstletter = Left(rng.Value, 1) iletter = Asc(firstletter) MsgBox iletter If iletter < 71 Then cmfolder = "CM A-F" Else If iletter > 70 And iletter < 77 Then cmfolder = "CM G - L" Else If iletter > 76 And iletter < 83 Then cmfolder = "CM M - R" Else cmfolder = "CM S - Z" MsgBox cmfolder
1. Start Date (Col. C) - assign date to respective position based on first cell that contains value greater than 0 or "". eg. at first cell with 1 in it, looks up to row 4 and assigns date include in that cell in same column.
2. End Date (Col. D) - assigns date based on last cell with value greater than 0 or ""....
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
I have amount, Activation date & Validity in days. I want to segregate the amount on days wise.
E.g. acgtivation date is 1 mar, validity is 45 days and amount is 900 then it will show 2 in each day from 1st mar to 14 Apr and on 15 apr it will reflect 0.
I need to write an excel function/macro/code to assign yes or no to a cell if the Description fits a certain category. I have attached a sample workbook and will explain what I mean.
In the sample workbook, there is data on a company. This included a unique ID, city, state, and description. What I want to do is fill out the yes/no column. So if a particular company has 2 of the 4 necessary "Descriptions", then column G will return yes, otherwise No. It must return yes for a particular company/ID if 2 of the "Descriptions" are "Sale", "Service", "Business", or "Par". I only want it to count at least two unique descriptions.
For example, in the sample workbook, Company A has all 4 of the necessary descriptions, thus everything in column G for this specific client should be yes. For Company B, only 1 of the 4 descriptions matches, so it returns No for all Company 2 columns. Company C has 2 of 4, so it returns yes for these columns. Note, it doesn't matter for Company C that "Business" was listed twice. It only needs to count the first unique time that description occurs.
I have an excel spreadsheet that 7 different categories (eg. Ave waittime, Avg loadtime, etc) that I have conditionally formatted based on 5 ratings (each rating a different color Green, light green, yellow, red, and purple) I would like to be able to give each cell a value based on what color it is eg. Green=5, light green=4 and so on.
I have a "memo of meeting" excel document used to capture notes during client meetings. In this document there's a cell for "purpose", "agenda", and "notes". The 'consultation type' cell is a drop-down populated by values set in Sheet2. The 'agenda' & 'notes" cells are then populated with information (also stored in Sheet2) based on what drop-down option was chosen in the 'purpose' cell. I'm currently making this happen with basic =IF() statements; however, the problem is that by doing it this way, the =IF() statement must reside in the actual cell which will end up showing the data.
This is undesirable as there may be additional notes, agenda items, etc. that I'll need to add during the meeting and as it's currently setup, I can only have either the pre-populated info OR the ad-hoc notes - not both. It seems a simple fix. Maybe put the =IF() statement in a cell off to the side but have it "assign" the value to 'agenda'/'notes' but that doesn't seem to work.
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
Would like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
Sheet 2 contains a drop down list containing names, would like to assign a Macro to a button on same sheet that would refer to sheet 3 based on certain criteria.
Sheet 3 contains columns A - K of data. 2 criteria, data in column D and column K. Column D would be the same as names in the drop down list, whereas column K refers to a constant, either y or n.
Example: If D contains "Cheddar" and K contains "Y", copy and paste A:K to Sheet1, preferably starting at row 7.
I do have a data set from A1:C8 as follows:- Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100
The above brands are categorized as follows:- Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire
I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories. Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire
