Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.
I'm looking for a formula which will filter the area expiring in any given year per property. An example would be (from the attachment) - what percentage of area of Property 2 is expiring in 2010? Answer is 50.94%.
I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:
Tab #1 - Performance Column B (Email Send Date): can be a repetitive date, something like Row 2. 4/25/2014 Row 3. 4/25/2014 Row 4. 4/25/2014 Row 5. 4/25/2014 Row 6. 5/2/2014 Row 7. 5/2/2014 Row 8. 5/2/2014 Row 9. 5/9/2014 Row 10. 5/9/2014 Row 11. 5/9/2014
Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.
Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:
lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.
I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.
I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.
There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).
Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.
Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.
I have 2 columns of data being populated by vlookups
Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.
If column H has a text entry then don't count it. If column H has a number less than zero then don't count it. If column H has a number greater than zero but column I is blank then don't count it. If column H has a number greater than 0 and column I has a text entry then count it.
I've tried using many variations of a sumproduct formula and none of them work.
This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.
=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))
Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..
=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))
but this causes an error in the formula somehow that I can't figure out. I even tried this
=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))
and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.
I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
Reports!$A$1:$A4000 = column A lists multiple agent's names A5 = a specific agent's name Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number
86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)
The average I'm looking for will give me an average of all times which are greater than zero.
I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:
Where Column O has either a date or is blank. Where Column B has a unique ID number which occurs more than once.
What I need to add is one more criteria from Sheet1, Where Column P has a unique names which occurs more than once.
I tried two changes: 1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and 2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.
I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank. 2) Column E (Status) has to be equal to either "Completed" or "Successful". 3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
In the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):
It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!
create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!
I'm working on a formula for calculating shipment weight per city it goes to per weight scale of the shipment pricelist. Although we have 4 customers with customer codes which we ship with our own trucks and some customers pick up there goods.
Therefore, my sumifs formula currently looks like this:
=SUM(SUMIFS('Freight SH 13'!$R$7:$R$14176,'Freight SH 13'!$R$7:$R$14176,"
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.
I am trying to get my INDEX & MATCH formula to retreive data from my table.
This is what I can do so far: Jan-07Feb-07 100 12 250 45
=INDEX(table,MATCH(B13,balance),MATCH(C13, date))
But I am trying to get it to get another row to look up as well. I want it to look up the color then the 100 or 250, then the date. Jan-07Feb-07 red100 12 red250 45 blue100 78 blue250 1011
=INDEX(table,MATCH(B13,balance),MATCH(C13,date)) I think i need to insert another match code in the row section but cant seem to get it to work.
In cell B6 , I have an amount . and in Cell C6 I have inserted the date at which that amount has been kept . Now what i want is I want the Data from D6 To O6 to calculate as to how many Days that 100000.00 Rs or Dollar have been kept each month . Each month has its own Days like January has 31 Days . So i want each Days to appear accurately. Like for example if i insert the 4-8-2009 from January till August 4-8-2009 , how many days that amount has been kept each month .
I'm trying to calculate a price for any given mileage. The pricing strategy is as follows:
For any journey 2 miles or less, the price is £9 For any journey between 2 or 3 miles, the price is £10 For any journey above 3 miles, the price is £10 for the first 3 miles plus £2 for every additional mile.
So, for example, the price of a 1.5 mile journey is £9 and the price of a 3.5 mile journey is £11.
Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
How to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year 2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks) 3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM 4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor A B C D 1 leo 1 99 jim 2 bob 3 68 jim 3 sam 1 73 ann 4 tim 2 84 adam 5 alf 2 55 ann 6 dan 3 42 adam 7 will 2 79 jim 8 sara 1 91 adam 9 10 Rank: 1 11 Supervisor: adam 12 Agent: " "
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A" =COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year =SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
