I am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.
please follow this link [URL] .....
Part A
2/12
3/12
4/12
5/12
6/12
plan
0
0
50
50
0
[Code] .....
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.
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.
View 3 Replies View RelatedI am trying to set up my excel to calculate daily compound interest.
The amount is 10,000 at 0.75% per day for 6 months.
I have tried several different things with no success -
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 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.
View 1 Replies View RelatedI'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 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 have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.
View 7 Replies View RelatedI'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..
View 9 Replies View RelatedI have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies View RelatedIn sheet 2 of my workbook I have a whole bunch of formulas that process data from a timeseries in sheet 1. What I want to do is to make a macro that will export the results of some formulas into a list in sheet 3, so that I can keep this info as I move on to the next time series.
Export the result of cell D15 in sheet 2 to cell B3 in sheet 3. But if B3 already contains information from another time series I want the info from D15 to go to cell B4 and so forth.
IF(A1=1400,(AVERAGE(A1:A2)),"")
Placed in column B.
This formula will do what I want, but 1400 may show up 5 times in 20,000 rows. You can change them to values and delete the blanks to move them up where you can see them.
1. That is more time.
2. This is living data changing all the time, so I need the formulas to remain.
Is there a formula that will place each event under the previous in the column B? The first event in B1, second B2, third B3, and etc.
I am building a sheet that facilitates managers at our company decipher information that is given to them on a daily workbook I created.
I want to have a drop down menu in Col J, that refers to the major questions they will ask.
Time started (Before)
Time Finished (After)
Number of Cards (More than)
Stops Per hour (more than)
Amount of Time Taken (More Than)
I have set up in Col (K) a secondary drop-down list which based on the question selected in (J) will offer options for the answer E.G Time questions will make (K) refer to options based on time and selections based on amounts will offer numbers as an answer.
The elements in brackets are the parts of the formula that create the search option. E.G If select 'Time Started' it will search for all records that started before the time selected in the next col (K)
Right if that makes sense.. I have completed this. so I have the following: A question drop down list in (J), and an answer drop down in (K) which is dependant on the type of question asked. (I have attached an example)
I want to now have a totals box after the question where it displays a total amount of cols that are true to the details in the question.
After that (if possible) I will want to use conditional formatting to highlight the ones that reflect to the question.
Realistically if possible i wouldnt mind creating a question box, a box that allows them to select 'more than', 'exact' or 'less than' and then the specific value. Basically a conditional formatting box for dummies within the worksheet. Attached is a demonstration.
My task is to sum up data in a worksheet which counts on the number of "working days"(Exclude Sat, Sun and Public Holiday) between "the date of receiving the case" and "the date of completion" of the same case.
So here I have already listed out the days of each month to distinguish "Working Days" and "Non-working days" by "1" and "0" respectively for the easy calculation of the no. of working days as I think.
like this(January):
Date
Results (Exclude holidays)
1/1/12
[Code].....
Then, I use the lookup formula to determine the receiving date (beginning) and the submission day (Ending) sucessfully.
Receiving date
completion date
Receiving date
completion date
1/3/2012
1/8/2012
1
0
Here comes the problem, I want to sum up the range of cell from the result of 1/3/2012 to 1/8/2012, i.e. (1+1+1+1+0+0)=4 to get the total no. of processing days of each case. I tried to use INDIRECT like "=SUM(INDIRECT(VLOOKUP(F5, A2:A226, B2:B226)):INDIRECT(VLOOKUP(G5, A2:A226, B2:B226)))" but it returns me #REF!
I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered:
Set R = ActiveSheet. Range("d1"). CurrentRegion
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
For i = 1 To R.Rows.Count
If Not R.Rows(i).EntireRow.Hidden Then
intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on
End If
Next i
I have a list of data in Excel with Autofilters. At the bottom of the data set is a row that totals up many of the columns. Whenever I use the Autofilters, the totals at the bottom still reflect the entire set of data, rather than only the filtered data.
Is there a way (using macros, formulas, or something else) to make the totals automatically update to only reflect the data that is visible when using Autofilters?
how to return the top 5 results in a set of data.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View 3 Replies View RelatedI am building an excel workbook to calculate a number of different results and I am having trouble with one particular formula.
I need to enter in a formula that will return a value for the following:
ex. C21 = 10
C22 = 10
C23 = 10
C24 = IF CUMULATIVE SUM OF C21:C23 IS GREATER THAN 15 THEN START COUNTING AT 15 BUT INCLUDE THE FIRST 15
SO USING THE EXAMPLE ....IF C21:C23 = 30 THEN C24 SHOULD EQUAL 30. BUT IF C21:C23 WERE TO EQUAL 14 THEN C24 SHOULD EQUAL 0
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
Just curious to know of any other formulas similar to the "quartile" function that allow you to calculate results in thirds (plus any other if known)
View 2 Replies View RelatedI have a Template which is used to import log data, calculate and displaying the results in several charts. Then all sheets are copied to a new workbook, a Save As dialog apears for the new workbook and the template will close. This is all done with VBA and it works fine.
Problem:
The Worksheets with the Charts on it refer to the data-source at the template file.
Strange because the copied worksheets have the same names as the originals.
(when Opens: This Workbook contains Links to other Workbooks...)
Question:
How can I establish that the Charts will point to their own data sources in the same Workbook rather then linking to the Template file on a Server.
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.
At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.
EXAMPLE:
TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00
Employees - 4
SUM - £4,237.86
Median - £1,033.93............................
To start, have 1 sheet with approx 25 columns of data and i need one column to check 3 others + another sheet to complete its fields.
In column M i need to enter some prices from another worksheet (tag name 'prices') but these prices depend on whats completed in the fields in columns L, T & V.
Column L has fields completed with eg. Houses, Offices, Warehouses
Column T has some fields completed with eg. B1, D, E (these are just zonal refs)
Column V has some fields completed with numbers 1,2,3
Prices worksheet has 4 columns
A = Property (same heading and fields completed as Column L on first worksheet)
B = Standard Price (this has prices in fields that correspond to the properties in A above)
C = Cresta Price (same description as above)
D = Wind Price (same description as above)
I need column M (blank fields) to look at L (heading 'property') then add in Standard Price from B (worksheet prices) then check to see if any fields are completed in Columns T & V and if they are then it needs to addon the prices from either columns C or D from Prices worksheet.
I am a little lost on this, and have tried to remedy my issue by searches to no avail. I have a cell formula that references another cell:
Cell A1 (=A3) where Cell A3 = 1
Cell A1 displays the text "=A3" and not the value of 1. Autocalc is on, and the worsheet and workbook is unprotected.