I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I’d want to count the negative occurences in B2, E2, H2, K2 etc. Also, I’d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.
I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.
Right now, I'm using the following: =SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)
I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months.
I have three letters that occur in column B of sheet 2.
A for active, P for pending, and S for sold. The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows.
I would like to total the sales for each period and place it on sheet 1. can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.
How to make information gathering easier. So I have a spreadsheet with information in one tab and Graphs & tables in another. I am trying to count how many times a word appears in my last column (the word is "HM - GM") but I only want to count how many times that appears in the month of Jul (The July column is my first column, it shows as "Jul-13" but when you click on the column it appears as 01/07/2013). I have tried a few =COUNTIFS formulas I found around the site, but none seem to work for me. I am trying to get the counted information into a table so it will show how many 'HM - GM' were in Jul-13.
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME 101 511 23/12 08:34 101 512 23/12 11:58 101 511 23/12 12:34 101 512 23/12 14:45 101 512 23/12 14:47 101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I'm an entry level Excel user who is looking for a spread sheet to print multiple pages for tagging pallets in a warehouse. The pallets will contain the address of the customer and the number the pallet is.
Example 30 pallets would be 1 of 30 then print 2 0f 30 then print 3 of 30 then print
how I can put in a varible pallet total and have it print the all sheets up to the given number?
I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......
SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:
=PV(i discount, AP,0, SV,1)
But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......
Reminder: NPW= FV( 1/(1+i Discount))^n FV: future value i Discount: discount rate n: numbers of years (periods)
I have a repetitive task that needs to loop effectively. The only things that change each time are the start Columns ("C:C") next moves to ("D:D"), this then continues down the different columns, until it reaches the end of the columns.
The other thing that changes is the formula initially it starts at RC[-1]&"",""&RC. Next time the numbers will be plus one, so RC[-2]&"",""&RC.
The last thing that moves is the "B1:B" & LR on the next step will move to "C1:C" & LR.
Basically a loop with variables in the loop. Is this possible? Below is the code I have begun to write but I could be writing this for ever, over and over again.
For example: From To .................. P1.........P4 P2.........P4 P1.........P5 P1.........P4 P3.........P5 P2.........P4 P2.........P4 P2.........P3
There are hundreds of sheets like this with more complex panel numbers (there are 70 to 80 panels). I want to count the number of times each panel is linked with another panel i.e the number of times its name is mentioned in front of it. This may look like as follows.
I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.
example: 1. apple 2. apple 3. apple 4. apple
How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.
I have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab.
I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.
The following =MODE(Table_X[Crash Alpha])
Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?
As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:
I've got the following table (see attachment). In a column A there're the articles and some of them appear several times. In column B there's the quantity. The goal is to get in column C the sum of quantities in column B for the repetitive articles in column A and then delete the repetitive articles.
so the table should look like: 3RD125070442H 30 3RE071870493H 4 3RI100009624T 15
in excel 2007 I found only the function to remove the duplicate cells but I need to get the sums before removing them and have no clue how to that.
I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.
I am getting ready to create a spreadsheet to list vendor's names and emails along with other data. I would like to include at the bottom of the email addresses a quick copy and paste distribution list. I have achieved this in the past by using a similar formula:
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1. However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
I would like to lookup the last occurence of a particular cell value in a worksheet and use the cell value in the column immediately adjacent to this one in a calculation.
For example, The text "Run #" occurs several times in a sheet with a numerical value in the next column over. I would like to use the last occurence - or as far as that goes, the largest occurence of the numerical value. Unfortunately, there is additional data in between the "Run #" occurences, so a simple max() for the column will not work.
I am trying to extract the last occurence of ppp (4th column). So as long as 4th column is "ppp", i would want to extract the row wher the the last "ppp" occurence start and display in sheet2 "108-Jan-07vcdppp4". If data in 4th column is detected to be "ppp1", it would also extract the row wher the last "ppp1" occurence start and display in sheet2.
Note that the number of ocurrence is random and do not follow specific pattern. Pls see the attached for the example.