Having problems with the following:
First Column Second Column Cumulative (B-A)
1 10 9
2 20 27
3 30 54
4 40 90
Every day,a new row is added and i want a formula at the top of the sheet to show the latest figure in column C.
I have Column A to show the Project I am working on and Columns B-M show the months of the year with the figures under certain months. In Column N I just want to show where a figure falls under a certain month, I want Column N to show the month (header) that particular figure is under e.g. N2 should return a value of "March", N3 ro return a value "June" and N4 to return a value "April/September".
View 5 Replies View RelatedLooking for a formula to show percentage increase of a figure.
Cell A1 is £45,000
Cell A2 is £51,000
Cell A3 ANSWER result shown as a percentage increase.
I have data for various firms - such as revenues, employees, etc. - for multiple time periods. There is no uniformity, so for some firms I'll have data for 8 time periods, for others for 1, for others for 30, etc. I have hundreds of columns, but the three relevant ones are: in one column is firm ID, in another column in reporting date, and in another column is number of employees.
The attached sample data will make it easier to understand.
I'd like a summary table that tells me the most recent reported number of employees for each firm, and the date of that reported figure. If that firm never reported any employee figures, then the summary table should still have a row for that firm ID, but blanks in the 'reporting date' and 'employees' columns. So, the summary table should be 3 columns - firm ID, reporting date and employees.
The challenging aspect is that the most recent reported employee figure is not always the most recent date on which we got data from the firm (so I can't just do a sort followed by a 'remove duplicates'). It'll need either a multi-step formula solution or a VBA code (that's my guess).
The attached sample data will make it easier to understand. It has the form of data I have and the desired output form.
Excel help needed.xlsx‎
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"
So it reads
Alan Ingram 1
David walker 3
And so on...
See attached. This is a report that I have to do every month. I cant work out a formula for it. What I need is a formula that will pick out which is the largest Figure of column A,B,C,D for each row and put the corresponding header in column G. i have manually put these in. Would anyone know a formula i could use for this.
View 3 Replies View Relatedhow can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.
View 5 Replies View RelatedI have an excel file with 2 tabs. tab 1: Active and Tab2: tenure. Tabe 1 has the following column headers
Manager
Employee Name
Dept
Assignment
Original Start Date
End Date
Bill Rate
The Employee names may be repeating in this tab becasue whenever the employee is extended for a new assignment he has a new start date and new end date and new assignemtn and rates....This helps track the historical information about the employees. TAB2: (tenure) also has the following column headers...................
If I have data that shows one year back information, how do I get my chart to have the last date showing up on the axis (the date label) to always be the most current date?
View 4 Replies View RelatedI've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies View Relatedformula to use in my report
Let's say I have a summary table on Sheet 1 6000 rows of data on sheet 2.
In Sheet 2 I have 3 columns, Name, Month and Score. Name Column (A) has names of students Month Column (B) has months January, February and so on. Score Column (C) has data from Green, Yellow and Red. Red is if they failed.
In the summary table on Sheet 1, I need to show all the names that got a "Red" Score. And their corresponding months.
It should look like this.
Month l Name l Score
-----------------------------------------
January l Mike J l Red
-----------------------------------------
March l Joe M l Red
I have some more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row to represent a date. 2nd how can I get the sheet to show the next expected date that the data may appear in that column, taking into account the date it last appeared and the average between appearances?
View 9 Replies View RelatedI need a formula to find the latest date (Column A) a given value appears in Column B.
Column A is a list of dates.
Column B can contain any of 6 values (For example: A, B, C, D, E, F).
And, to make it even more fun, the system I'm working on doesn't allow macros.
Excel. Let's say this how a column looks like:
a
b
b
c
d
Is there a formula to get this outcome:
a 1
b 2
c 1
d 1
If not, is there a way to create a list of words that are in a column?
I need a single formula that will give me a total figure each day.
I am trying to get a total weight for each days sales
Sheet 1 holds the unique ID and total items sold for each day
Sheet 2 holds the unique ID and the weight of each item.
I have got as far as creating helper cells but this is making the whole sheet way too big. I know there is a more efficient way .....
I learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
View 3 Replies View RelatedI have a column with a number of strings looking like below examples
View 3 Replies View RelatedI made a data entry user-form on my file which is find the latest row of the sheet and input the data to the next row in columns "A" to "E", What I need is copy the formula from the previous row to this row in columns "F" to "L".
Here is the code of the user form:
Private Sub CommandButton1_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Data")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = Me.TBDate
ssheet.Cells(nr, 2) = Me.TBOpen
ssheet.Cells(nr, 3) = Me.TBLow
ssheet.Cells(nr, 4) = Me.TBHigh
ssheet.Cells(nr, 5) = Me.TBClose
End Sub
i have a figure of 1000 in cell a1. i want to divide it by a figure in cell b1 and have an answer in c1, or divide it by a figure in c1 and have an answer in b1.
in summary, cell a1 has i figure in it while cells b1 and c1 are empty until i enter a number into one or the other.
I have 3 columns (Name, Pass Date, Fail Date) Each name will only have 1 date in either column B or C.
I want a formula that will say "if there is a date in the pass column (B) then this name is a pass, if there is a date in the Fail column (C) then this name is a Fail"
I have created a spreadsheet at work which is being used by a couple of people.
One of the worksheets rounds off figures from a table in sheet1. My problem is that once I've set the spreadsheet up it seems to work fine.
But i opened it again and it displayed a random figure but the formula stated "=#N/A".
I'm pretty sure all the computers are running the Addin correctly. Is there something I've left out?
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
I am trying to make a column show the result of a formula as positive number only. Right now the column subtracts two other cells and displays the result. I am trying to make it show all the results, positive or negative as positive.
View 3 Replies View Relatedam looking to find the most recent value in a series of cells that I define. I have set up a worksheet for each month and I want to only pull out the latest total entered for a particular cell reference (cell B16 in each worksheet has the number I need).
I tried using : =LOOKUP(9.99999999E+307,Jul:Jun!B16) but that just gives me a #value error.
I had a search through the archives too and found [url] and [url](neither of which I really understood or could use, although I did have a go), anyone got any ideas? Am sure it's very easy!
Just to clarify, I have 12 worksheets, each with a different month. Each month I enter data into the relevant worksheet. I want a running total on a summary worksheet and need to pull out the latest numbers automatically (they don't need to SUM or anything).
I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).
View 3 Replies View RelatedI need a macro that can jump to the nearest current date in column B from anywhere on the current tab. all the dates are in 10/09/2009 format, and the actual date of today might not be in that column. This can be + or -1 one day search from today. After this macro run's the nearest current date will be the active selected cell in column B.
View 9 Replies View RelatedI have a series of rows 14000+ lines strong in an excel sheet.
The series contain caribou location reports from those caribou that have a
collar attached to them.
Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).
I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).
There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.
with the formula below..
=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)
*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475)
I need a VB Script or Excel tip to Locate the latest value in the column.
Example1
Value
1
2
3
4
5
6
Latest Value
6
Example2
Value
1
2
3
4
5
Latest Value
5
Note: I have 1 Row and 'N' Column. Column may have null value, which need to be ignore as mentioned in the example.
Also., if in future new value has been entered in the column then the Latest value has to be updated automatically with the new value.