Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative. Example:.....
I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
=IF(SUM('SL-001 - AT-001-001'!R[852]C:R[856]C)=0,SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C, 'SL-001 - AT-001-001'!R[840]C:R[844]C,'SL-001 - AT-001-001'!R[846]C:R[850]C), SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C,'SL-001 - AT-001-001'!R[840]C:R[844]C, 'SL-001 - AT-001-001'!R[846]C:R[850]C,'SL-001 - AT-001-001'!R[852]C:R[856]C)) *'SL-001 - AT-001-001'!R992C*R3C9 and I would like a macro that will extract the numbers between each instance of the letters R and C , i.e. 852, 856, 826 etc etc. in cells A2, A3, A4 respectively.
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
I would like to place in sequence increasing the numbers of one I creak in a cell. In the formula I determine it I creak and the corresponding frame number to the placed ones. The problem is that he is accumulated only the greater and not sequencia it.
1803456781101112 1134567810111280
but,80-80
Function ordenar2(Myrange As Range, num As Integer) As String Dim Myorder As Double Dim X2 As String Dim n As Integer n = 1 Do While n
I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
It lives on Google Docs as well, there are multiple people putting dates in and sometimes people will use just CAPS for the day name.
What I would like to achieve is that if someone puts another Friday 07/03/2014 under the Saturdays in the list, I want it to automatically sort itself under the Friday 07/03/2014 ones.
Whole row would need to move those spots up, not just the one cell.
The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns.
As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles.
Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?
In Column A, anytime a specific "text" appears, I want to concatenate the information in the row, and place it into worksheet Data-list under a specific heading.
[URL]
The formula I would use (not vba) is =IF(A3="FN",B3,""), then I just copy it all the way down. However there are 27K cells, which is why I want a VBA Code
What I would like to do is if a string e.g. "*12/18/09*" is found in column A, then place "1" in column B. There are many rows that I would like to search. Either a formula or VBA would be fine.
I have been trying to see if I can peform the following, I want to be able to have Day 1 (Col B) by using the Start and Finisg Columns Place a 1 in column H for every hour between the Start and Finish times.
This needs to be done for each of the Day in column B, ie 7 Days.
I am trying to place the contents of an array into a column on the sheet. I can enter the array into 1 cell (which strings the values), but I can't seem to make range(1)=array(1).
I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell.
** SWITCHLIST FOR TRAIN---Grain Spec -East DEPARTURE TIME from WESTTOWN is 01:00 TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd. Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd. Train should leave this town with 2 car(s) TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay 1/05/2007 8:57:52 a.m. ...............................
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
| ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
