I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.
I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.
I have a set of 7 years daily rainfall data. I need to calculate the daily average of rainfall from this 7 years data set. I don't wanted to calculate it by for example copying and pasting the first day rainfall of each year rainfall to a new sheet and average it for all the days. I need to calculate it all at a time.
While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre
I need to count the daily average of a task to a week ending number. I need to see the current average after each day during the week. Example – Mon = 2, Tues = 4 AVERAGE is 3 – Wed = 2 AVERAGE IS NOW 2.6…and so on averaging out after each day is added.
I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?
Am trying without success to create a formula to calculate average daily balance from a ledger that has a variable amount of entries per month. The variability of # of entries has me stumped. For example:
I have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.
I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?
I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).
I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?
I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...
Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:
B3=Capital B4=Interest Rate B5=Starting Date B6=Finishing Date
For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.
calculate overtime hours from daily time entries. Normal hours are 7.6 per day Time 1/2 is hours over 7.6 but no more than 2 hours Double Time is all hours over that.
I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.
WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068
I currently have a profit/loss spreadsheet where I use a SUMIF to calculate daily gains/losses.
=SUMIF('Daily Sheet'!B:B,B377,'Daily Sheet'!H:H)
Basically, on the 'Daily Sheet' page when a sale is made I write it in a row. Each sale gets its own row. One of the column's has the date. (Column B) Column H has the respective gain or loss for that sale/purchase. B377 in this case contains the date 18-Nov. (B360 to B389 has Nov 1 through Nov 30) So this cell will calculate all the gains and losses in column H for each sale/purchase entry I have for that day. Some days I have 30 rows, others only 2 or 3, so this works great.
My problem now is that my rows have gotten much more complicated and detailed, and now I'd like to be able to only sum column H:H if it meets TWO criteria. (I'd like to add in Column C that specifies the type of sale or purchase being made, which is R, T or S for my purposes.) So I'd like to somehow write a sumif that only adds up the sales for 18-Nov AND sale type R.
I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.
Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))
However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.
I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..
I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.
how to find the average times of two different sets of groups. There are the baseline times and intervention times. Both groups have three subtopics: Time Fell Asleep, Woke Up, and Duration (how many hours I slept). I am not sure how to find the average of each subtopic. Here are the times....