Weighted Percentage With Reference To The Date

Jun 8, 2008

I am making a spreadsheet that requires multiple dates and percentages. Is there any way that I can make the percentage that is related to the most recent date weighted more than a percentage with a date from a month or two ago?

For example:
X//Y//DATE
90%//85%//3-March
85%//70%//21-May
87%//90%//6-June

Is there formula so that I can have the 90% and the 87% be the most important (or weighted), the 70% and the 85% be the second most important, and the 85% and the 90% be the least important when I average them out?

View 14 Replies


ADVERTISEMENT

Calculate Weighted Percentage

Oct 18, 2007

In my spreadsheet (testing.xls) I have a chart to mark the quality of a phone call based on a list of 8elements being judged. I have a likert scale for the quality result for each element, say for example 5 for highest, 3 for average, 1 for the poor, 0 for very poor and NA for an element which is not counted.

At the bottom of my table I have a cell where it would work out the % based on the outcome of the result from the different cells being ticked, what would i need to do to have excel work this out for me? Please bare in mind that if there is for example 1 element out of the 8 elements marked as NA the result would be divided 7 instead of 8.

View 9 Replies View Related

Weighted Averages - Accurate Productivity Percentage

Feb 20, 2012

I have a spreadsheet that tracks productivity for various parts being made basically pcs made per hour against how many pcs should be made per hour shown in a percentage of 100 percent

For example: Part ABC - ran 450 pcs per hour. The rate is 9500 so it ran at 4.74% of the goal

The machine runs about 50 parts.

They had me create a sheet/chart to graph productivity. They told me to just take an average of all the percentage rates the individual parts are running at.

I believe this is where a 'weighted average' should come into play? Taking an average of all those averages is not going to give an accurate overall productivity percentage is it?

How would I go about making a 'weighted' average (if that is indeed necessary) to get an accurate productivity percentage?

View 5 Replies View Related

Weighted Average Filter - Table Reference

Jan 28, 2013

See attach file.

I have a table named 'tbProd' and in cell D2 I have this formula, that allows to calculate the weighted average whether I filter or not by 'Dept':

=SUMPRODUCT(SUBTOTAL(9;OFFSET($D$5:$D$50;ROW($D$5:$D$50)-MIN(ROW($D$5:$D$50));0;1));$C$5:$C$50)/SUBTOTAL(9;$C$5:$C$50)

This formula works well, but I would like to change it, using table references instead of cell references.

exf_Weighted Average Filter.xlsx‎

View 1 Replies View Related

Formula For Largest Percentage Related To Reference Number

May 15, 2014

I have data one sheet which is

Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100

On the next tab I am consolidating all the reference numbers and want to capture the latest % complete

I have been trying =Max(if ref no=1 and type = Erect, % complete),1

But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.

View 1 Replies View Related

Date Range: Reference A Single Date And Output Date Ranges

Oct 11, 2008

I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:

Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View 2 Replies View Related

Sum For Columns Containing Percentage & Date

May 25, 2009

I have a column in a spreadsheet that I enter a percentage complete, or a completion date in, beside it I have a column that shows "diameter inches" I am looking for a formula that will multiply the percentage in the first column by the inches in the second column, and give me a total at the bottom of the page. The problem is that when there is a date entered, I need it to be considered 100% and add the full amount of the corresponding cell to the total.

View 9 Replies View Related

Formatting Based On Percentage Complete And Target Date?

Nov 20, 2013

I have a sheet (example is attached) that lists multiple jobs and their related tasks. I want to know if it's possible to create a conditional formatting that says if the percentage complete under the "task" is under 100% and within X number of days of the target date, it's red/yellow/green.

So, if a target date is 70-100 days out; 0%-10% - RED, 11%-45% - YELLOW, 46%-100% - GREEN

If a target date is 40-69 days out; 0%-45% - RED, 46%-75% - YELLOW, 75%-100% - GREEN

If a target date is 20-40 days out; 0%-60% - RED, 61-90% - YELLOW, 91%-100% - GREEN

If a target date is 0-20 days out; 0%-85% - RED, 86%-95% - YELLOW, 96-100% - GREEN

If a target date has passed, Anything under 100% is RED.

View 4 Replies View Related

How To Compare Percentage Difference Between Date Range Through Pivot Table

May 9, 2014

I would like to compare % difference between date range through pivot table

Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500

How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?

View 2 Replies View Related

Formula To Return Percentage Of Duration Elapsed Between Two Dates A Third Date Represents?

Feb 20, 2014

I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.

As an example:

a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00

In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.

Note: the formula needs to work effectively across the boundary between two consecutive years.

View 1 Replies View Related

Conditional Formatting - Colouring Percentage Of Multiple Cells Based On Date Range

Mar 12, 2014

I am trying to get a row of cells to highlight a percentage based on a date range

Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.

In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.

Name Start Date End Date Jan Feb Mar

Dale 11/01/14 26/02/14 21 days 26 days

I have attached the spreadsheet for an example : Book1.xlsx‎

View 3 Replies View Related

SumIf Reference To Date

Jan 18, 2010

How can I reflect the correct trainee count for Day1-Batch1?

Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.

Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day.
Attached herein is the sample file.

View 9 Replies View Related

Date Reference In Formula

Jan 4, 2010

I am trying to create a formula, comparing the date in column A to 3 fixed dates, then calculate the value in column C:

=IF(OR(A142=Sheet1!B2,A142=Sheet1!B3,A142=Sheet1!B4),C141-B142+8,C141-B142)

Where Sheet1!B2, B3 and B4 are the fixed dates (5/15/2010, 8/31/2010 and 10/31/2010). I would like to copy the formula into multiple cells, but keep the references to the fixed dates the same. When I do a copy, paste, the resulting formula becomes:

=IF(OR(A143=Sheet1!B3,A143=Sheet1!B4,A143=Sheet1!B5),C142-B143+8,C142-B143)

I need the Sheet1 references to remain the same. Or, can I hard-code dates in a formula?

View 2 Replies View Related

Using A Cell With Date As Reference

Apr 6, 2012

Basically I have a row of dates

so,

B1 - 01/04/2012
B2 - 02/04/2012
etc...

And I have tabs for each of those dates aswell.

Basically I want to pull some figures out of the tabs but use the dates in the reference.

So i need a formula that will look at the date in cell B1, use the date to find the tab and then say find the value of cell G12.

View 4 Replies View Related

Getpivotdata Using Date As A Reference

May 22, 2006

Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format

View 2 Replies View Related

Lookup And Reference For Category And Date?

Jul 21, 2014

I'm having serious difficulties with getting together a formula for a little bit complicated lookup.

I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.

In the second table I also have categories in the first column, dates in the second column und values in the third.

I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.

Example
Table1
Red 07.08.2005 ???

Table 2
Red 18.04.2005 three
Blue 11.06.2005 one
Red 06.08.2005 four

In this example the value that should be looked up is "four".

View 12 Replies View Related

Today Date Causes Circular Reference

Dec 8, 2013

Several places on my spread sheets I need to compare an action date to today's date.

I use: [Code] .....

in one cell and the fomulas to compare a date in a different cell to that cell.

I get the message "Circular References" for the cell the the above "today()" formula.

I do not get circular references in the cell that refer to the today cell, just the today cell.

View 4 Replies View Related

How To Reference Cell With Date In Formula

Jul 22, 2011

I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")

It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:

A2 contains 9/9/2011
B2 contains 9/22/2011

=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")

but it doesn't work.

View 6 Replies View Related

MAX Date - Cell Reference Not Changing For Each Row

Aug 10, 2013

I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....

Code:
=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))

View 4 Replies View Related

Cell Date In Formula Reference

Jan 16, 2008

Using cell contents in formula to Populating cells with values from closed workbooks
I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.

View 2 Replies View Related

Conditional Formatting Based On Percentage And Insert Cell Based On Percentage

Apr 3, 2013

I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.

For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.

I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.

View 2 Replies View Related

Date Countdown With Reference To Specific Cell?

Jun 25, 2014

I have this formula counting down days until my next fire drill:

[Code].....

However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?

My initial idea was a formula like this:

[Code] .....

(however this code doesn't actually work ...).

View 5 Replies View Related

Extract Reference Numbers In Given Month With Date?

May 18, 2014

I have attached an example spreadsheet which should be relatively intuitive over me trying to explain it.

I am wanting to extract all CJ numbers with the corresponding dates from Sheet 1, into Sheet 2. It has to factor in, engineer name (as stated on sheet 2), the Month (as stated in sheet 2 "May") with the "Attended site" date in sheet 1....

If you see the worksheet it should give you the whole picture with a fair amount of clarity.

Book1.xlsx‎

View 11 Replies View Related

Change Cell Reference Depending On Date

Jun 11, 2009

The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.

NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.

View 2 Replies View Related

Sumproduct Formula With Date Reference Not Working

Mar 7, 2012

I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).

I have 52 colums ie one for each week.

=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000

View 7 Replies View Related

Date - Cell Reference To Get Period Of Days Between

Aug 29, 2013

I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498

Code:
Dim MyODateRng As String
Dim MyODate As String
MyODateRng = Sheets("Requests").Range("B3").Value

[Code]....

So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.

View 7 Replies View Related

Parameter To Reference Certain Cell To Input Date Into URL?

Apr 3, 2014

I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)

ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.

What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.

View 2 Replies View Related

Add And Multiply Values Based On Date Reference

Apr 27, 2009

I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.

Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.

The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.

In the next post I will include the Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [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.

View 10 Replies View Related

Hiding Columns With A Macro By Date Reference

Jan 28, 2010

I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.

View 9 Replies View Related

How To Do A Weighted Median?

Nov 2, 2006

Is there any way to do a weighted median in Excel?

Consider this example: ....

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved