In this case the largest groups are:
50:01 - 75:00 = 15
75:01 - 100:00 = 25
100:01 - 125:00 = 15
55% fall in to this bracket.
What we are targeting as a business is that the majority of worker fall in to a similar range each month. So the challenge for my team is to increase the % value month on month. the close to 100% the better they are doing.
Equally what in Excel could I use to automate this calculation.
How can i formulate the following in a formula: Sales of product A (product A belongs to product class ABC) divided by the total sales of all products in product class ABC
I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator. In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!
'Initializing tagrow so we can test what train we are trending' tagrow = Cells(12, 2).Value
'Only Propylene flows need adjustment in A and B trains, calculate everything' If tagrow = 7 Or tagrow = 8 Then Sheet2.Calculate Range("D15:D115").Value = Range("C15:C115").Value / 1000 End If
I am trying to write some code that will repair errors in a standard workbook. One of the errors is a blank cell that needs to be changed to "325¨¬F ¡Â T ¡Â 350¨¬F". How do I insert these symbols/operators into the VBA code?
I have an IF Function that I want to use to check mathematical errors between two sheets. For example Sheet2 is where I put all my material prices and Sheet1 cell A1 shows the total amount that is one Sheet2. On Sheet1, I have an IF Function in B1 that does the same calculation A1 does, but will display "ERROR" if A1 does not Match B1.
The IF Function works, my problem is that I want this to work from B1:B100 on Sheet1. But on Sheet2 the next SUM would be from row 42 to row 59.
How can I make and IF Function from B1:B100 on Sheet1 offset every 20 rows on Sheet2
For example:
Sheet1 B1 would be: Formula:
[Code].....
Sheet1 B2 would be: Formula:
[Code] ......
I need to do this without having to manually enter each range to sum up.
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
In cell C2 I want to compute the value of 5x^6 + SQRT of 6, where the value of x is located in cell A%. What formula would I need to enter into cell C2 to do this?
I want to add some to a macro have which asks before performing the rest of the macro... "Are all details correct?" with a yes and no button. yes, carries on with the macro and no ends the macro.
I use a lot of VLOOKUP formulae in the sheets i put together and, despite both the lookup value and table array being in the same format (usually text format), i often get an #N/A being returned.
Here's the fun part... if i then do something simple like access the lookup value cell (either by double clicking or pressing F2) and then hit return, then the vlookup calculation suddenly returns the correct value.
I'm convinced it's something to do with the way the cells are formatted but can't work out what.
Looking for some help to figure out what is causing our problem. My friend has created the attached excel sheet in Excel 2003. The issue is that the a vlookup does not seem to be finding the correct line for the value from the lookup table when it is returned twice.
Let me explain - The details are contained in the attached workbook. There are three sheets in the workbook - NH3Curve, Samples and Qvalue table.
On the Samples sheet, in cell C12 and C13 you can enter varying values. Go across to F12 and F13 and you will see that they both have the value 22.1. This is where the issue occurs - in cells G12 and G13 the lookup value should be the same - but it is not. G12 is actually returning the value for 22.0 not 22.1. (lines 173 and 174 on the Qvalue sheet). We cannot ferret out why this is happening.
Any help would be greatly appreciated. Sometimes when you look at something you can't see the obvious, so I hope it is that simple.
What I am doing is selecting the field in A which equates to the lowest value in C (C= hourly sales), then I am subtracting 60 mins from the value selected in A. This in effect will give me my closing Times i.e. find the cell with Zero Sales then subtract 1 hour to find what must be the closing time (assuming of course that there is at least ?1 of sales per hour while open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula cell
I have an attached spreadsheet, where I have an IF statement within "O8". Bascially, the result in that cell should equal "50", however displays as "0".
I think this might be an excel glitch - as the same formula worked for "M8".
I have been working on a Spreadsheet for GVWR (Gross Vehicle Weight Ratings). I'm having a problem with the Formulas in Excel, I thought you may know what's going on and why it's not giving me the right data. I'm pretty good with Excel, I've been using it for about 17 years for calculating Tank formulas, etc, but have never run in to this before.
I have three columns to the right of the sheet. One is Stock Vehicle weight of an item____(E) The next column is the Aftermarket weight of the item______ (F) Then the third column (G) is the total weight minus the stock weight using a very simple formula =SUM(E6-F6) gives the correct weight in the third column, but at the end of each area (Topic) i have a Total Weight area in the third column (G), using the formula of =SUM(G6:G26) which should just add up everything in column G, but it doesn’t. I have re-made the spreadsheet three times,
it returns an error when I try to enter it. Is there a brave soul which a good eye for errors that might be able to shine some light on the error. Currently it appears the error centers around the MONTH( ) portions of the function. But each embedded function is able to stand alone and I can't imagine why they won't work together.
I have put together the below macro to take data from an Excel spreadsheet and place in a word documents (using bookmarks.
What should happen is it should open up the correct word template depending on what data is in column E (Servicecode) and fill in the bookmarks with the data from excel.
It all works fine and goes through the loop and saves, inputting the correct data into the bookmarks but after looking at the saved templates and scrolling through using F8 the IF ELSE does not seem to work. It CALLS theBearSSR macro rather than the relevant macro e.g. word in E2 is Cele, the macro should call the CeleSSR macro (which opens the correct template).
I don't think i have declared my DIM Servicecode correctly... I can't seem to figure out how to make the IF ELSE condition look at the Servicecode and then go to the correct sub routine (the difference between them is the template that is opened).
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2 Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2 Event Assistant@City of Saint Paul
Sample Data 3 Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3 Marketing Coordinator@Town & Country Caterers
I have a userform with several fields located in it. For the most part, it looks like a user information form, Name, Date,, stuff like that. There is a field that the user enters some information, and using the afterUpdate function for the field, it searches to see if the value is unique. The problem is that when I tab from that field, if the value is not unique, I want the cursor placed back in that field (textbox). SetFocus doesn't seem to do what I want, unless I am using it incorrectly.
I have a question regarding Do While loops. I'm trying to write a do while loop to search through an array for a particular value and return the row number. This value is in the first column of the array and there are 211 of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times, then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.
i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:
Table 1 - Items with quantity that is waiting for input:
Quantity Product 5 Button A White 7 Ribbon B Blue 8 Thread A Black 10 Cloth A White 3 Button B Blue 4Button C White 9Ribbon A Pink15Button A White Above is just a part of it, the list goes on to over 200 rows long.
I have a barcode scanner I have setup with Excel to scan PC barcodes at the office.
Previously what used to happen is I would get a list of all PC's on level 6, under data validation I had an error alert setup. The source code was =$A:$A (A:A being the list of PC's). So basically If I scanned a PC that wasn't on the list a error alert would popup saying STOP - Take down PC details. This worked Fine.
I now need to use it for the opposite.
I have a list of PC's that are missing in column A, If I scan a PC on level 6 or wherever and its in that list I want the Error Alert to popup to advise me to take down the details. So the =$A:$A won't work anymore.
Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".