I have a spreadsheet in my PDA that figures out my average gas consumption and it does fine, HOWEVER, it's made to do ONE calculation only which means I have to always re-enter how much gas I got NOW and how much the last time.
I would like to extend this formula/sheet so that it will keep a track of the TOTAL average consumption as well as after the last fill up.
Ie, how what was the odometer at last fillup 15,000 miles
how much is the odometer showing today 15,300
How much gas did you put in today, 30 gallons and so on.
Normally, I would have to go in and retype the values but I would like to continue and simply type in the NEW odometer setting and the gas I got this last time and so on and it should tell me what my average was TODAY and the total of ALL the entries.
I SUCK at math so a description will NOT do it, I will need the actual formula and so on or even better someone to e mail me the spreadsheet and I can then modify it IF needed.
Right now it says MPG=(Miles at this fillup minus Miles at last fillup) Divided by gallons pumped..
I'm thinking that if I play around with it enough, I can extend this, BUT, I was more concerned about figuring out how to do the running total so to speak without having to go in and do this manually somehow..
The first step will be to get the formula or macro to fill in the cells under the date columns that will show how the consumption of the 555 is assessed to be spread out over the period. The formula should portray an equal distribution of the consumption over the period.
******** ******************** ************************************************************************>Microsoft Excel - claimv1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2=BCDEFGHIJKLM2Start DateEnd DateTotal consumptionLast week day14-dec21-dec28-dec04-jan11-jan18-jan25-jan01-feb317-dec24-jan555Assesment 4 Actual Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I made a sheet were i record my gas and water meter numbers. I try to do i everyday but sometimes i forget. I want the average consumption per week so it's no problem when i don't forget one day. In column A is the date, column B has the meter figures, column C has the consumption for that day (this day - yesterday). I want the weekly average so i can make a graph of it. How can i calculate a weekly average even when i miss a couple of days?
I am trying to create a spreadsheet with a chart to show gas consumption in our house. I have sufficient data to produce the standard "S" curve for a 12 month period and would like to be able to have one chart with the standard "S" curve on it plus a curve of progress in the year to date of gas used so that an instant comparison between planned and actual usage can be made. The problem is that the 2 curves require a different X axis. The standard "S" curve has an X axis that is 12 months long whereas the current one is as long as it is from the start of the year being measured. Both curves will have data inputs on different dates in the year.
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:
Sheet A has 10,000 records with these fields: id#, name, address, place of employment.
Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.
Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.
I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.
The information I need to transfer from Issues is: .....
I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....
[Code] .....
How I can write this so their totals show up in the correct row?
I have a Main Customer Spreadsheet. I want to Auto Populate FROM the Main Customer Spreadsheet to a New Spreadsheet. I want to be able to key in a customer name on the New Spreadsheet and take the info for that customer from the MAIN Spreadsheet and fill in the blanks. I need to be able to do this several times a day.
It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...
Wondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.
I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)
I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove).
I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this. I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.
The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.
I have a master tab in a spreadsheet where it contains account #'s (A); their respective account name (B); sales rep (c); region (d) & notes on the account(e)
I also have tabs for each individual region on the spreadsheet.
I was wondering that once I update any of the cells on the individual region tabs...if that could automatically update on the master tab, where that account etc is located...or would it be easier to update the master tab with notes; change in rep, etc...and have that automatically fill over on the individual tabs?
The intent of this sub is to sum each and every row on a spreadsheet. However, it keeps summing the same row (Row 1) for as many rows that are on the spreadsheet.
Code:
Sub SumAllColumnsInAllRows() Application.ScreenUpdating = False Dim bottomA As Long bottomA = Range("A" & Rows.Count).End(xlUp).Row Dim rng As Range Dim lColumn As Long For Each rng In Range("A1:A" & bottomA)
I have a spreadsheet with Row 1 as the header and Row 2 as sub-headers. I want to filter automatically to 2nd row, but whenever I press on Ctrl+Shift+L, I have the first column filtered. I have another spreadsheet that whenever I press Ctrl+Shift+L, it automatically filters the 2nd row instead. I'm not pretty sure what's the difference. I have my spreadsheet frozen on cell B3 (not sure if it has something to do with freezing cells. I know I can select 2nd row before pressing on Ctrl+Shift+L, but is there some other way to have this automated?
I have a set of spreadsheets (Circa 50) that each have around 50 tabs of information on, one set of spreadsheets are from 2012 and another are from 2013.... basically i need to write a formula that goes in to every tab in each spread sheet and basically tells me the entries that are in the 2012 sheets but do not appear in the 2013 sheets. The results of this are the most important thing. All of the names that i need to search are in Column A of each spreadsheet and tab .
I have a "surplus inventory" sheet and a "inventory shipped" sheet. How can I input how much I shipped in the "inventory shipped" sheet and make it take away from my "surplus inventory" sheet automatiaclly?
I have designed a spreadsheet in the office where I work, and think it would be quite flashy (gimiky) if I could have the spreadsheet show the name of the user logged on.
At the minute if I try and access a sheet currently open by another user it will come up with the standard message 'workbook1.xls is currently in use by xxxxx' then you open a read only copy until they have finished.
I was thinking there must be some way of importing this information to excel to display something like 'welcome (xxxxxxxx)(user currently logged on)'
The other part of my problem is that the excel sheet will only display the network uername of the user, but I have seen spreadsheets that cross reference this to the Microsoft Outlook global address list on the MS Exchange server, and display the users actual name.
In a nutshell, I just want to know is there anyway the excel spreadsheet can display the username currently logged into the shared spreadsheet.
we have a spreadsheet which we encourage our employees to use but not sure if they are or not. is it possible to put a counter onto the spreadsheet so we can see how many times it is being opened
I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..
We utilize a shared spreadsheet in our department named "Arrangement Requests". From time to time and seemingly very random the sheet renames itself. When this happens anyone adding information to it becomes unable to save since the original "no longer exists".
This causes problems because everything we do is in real time and expected to be viewed by all as soon as save has been indicated.
I've created a spreadsheet which pulls data from about 5 other spreadsheets via links in the formula.
Unfortunately I am having trouble with these updating. I have to open the 5 individual spreadsheets in order for the data to pull across otherwise I get VALUE in every cell in the amalgamated spreadsheet.
Any thoughts why this should be the case? I've tried it in the past and it's always worked fine.
We have an archery league that is getting very popular, and very time consuming. We have a spreadsheet now that works, but so much of it isn't annomated that it's not saving a bunch of time for us. It is, however, very accurate, so we keep using it, but I think there is a better way out there to do it, and I bet one you excel pro's know the answer.
Here is how it works:
We have three man teams. Let's say we have 20 teams again this year (we did last year). These teams will have their own unique schedules of other teams that they will play against. We have ten weeks of head to head team matches.
Every week, every shooter's average is computed. So, week one their average is whatever they shot. Week two is the average of week 1 and week 2 combined, and so on. This revolving average keeps shooters from sand bagging.
The team head to head match ups are handicapped. Let's say that team 1 is up again team 2 in week 1:
Team 1 is consisted of bill, bob, and barry. Bill's average at this time is 300, bob's is 290, and barry's is 295.
Team 2 is Larry, Leonard, and Louie. Their averages at this point are 290, 280, 300.
The head to head team match up is consisted of three individual head to head's. The shooters with the best averages, at this point in time, face off, the middle averages face off, then the lowest average shooters on each team also face off. You get 2 points for winning your head to head match. You get 1 point for tieing. Zero points for losing.
The handicaps kick in like this. Bill is the high average shooter on Team 1. He faces Louie since Louie is high man on Team 2. Since their averages are both the same, this match is a head's up, scratch, match with neither shooter getting spotted points.
The middle shooters are Barry with his 295 against Larry with his 290. We use a 80% handicap. So, Larry gets "spotted" 80% of 5 points, or 4 points. The same goes for the matchup of Bob against Leonard. Leonard gets 8 points in his match (80% of the ten points that his average is lower).
Shooters shoot their games, scores are taken, points are spotted points are added in, then winners are declared. Then, the points each shooter wins is added into a team total for that week, then on to the next week's match.
My biggest problem is that the #1, #2, and #3 shooter on each team, each week, changes sometimes. In my previous example, lets say that in week two, lets say louie blows it and shoots a 270. So now his average isn't the highest on his team, so the pecking order of the team changes.
So, my challenge to this board, if you wish to accept it, is how do I set this bad boy up? Lots of issues here. The biggest one is how to get the teams to self rank themselves each week?
I'd love to have a tab where I enter each shooter's score in, and excel takes it from there giving points to winners of adjusted matches.
