Formula To Calculate Value After Doing Data Match?

Dec 13, 2012

Refer the attached work sheet and any formula to calculate the values once the criteria is met.

To calculate % after multiple look ups.xls‎

View 7 Replies


ADVERTISEMENT

VBA To Autofill Formula Over Column Data Calculate And Replace Original Data

Nov 14, 2008

Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.

I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008

All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'

Sub Create_formula_result()

Dim Limit As Long
Dim r As range
Set r = range("A1")

r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))

End Sub

View 9 Replies View Related

Formula To Calculate Data

Dec 3, 2013

East
Item #
Cases in East
Dropped 1
Pending
Wave 1 - Qty
Dropped 2
Wave - 2 Qty
Dropped 3

[Code]...

I have above data where in I need to formula to calculate the 428 in dropped 1 should get calculated in Dropped 2 & dropped 3 as per the plan in wave 1 & wave 2

View 3 Replies View Related

Excel Formula To Match Data

May 28, 2013

I've a data looks like below..

Name
Yes/No
Marks
Pass/Fail

Susheel
60
Pass

Sanjay
30
Fail

Susheel
Yes
10
Pass

Mohan
21
Pass

I want to populate the data in main sheet for unique name...based on some criteria.
1- Name Should match in both file
2- If "Pass" then it Should be "Yes"

View 5 Replies View Related

Formula To Calculate Number Of Work Weeks The Data Has Been Collected?

Dec 27, 2013

I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.

View 3 Replies View Related

Index Match Formula For Compiling Data

Jul 7, 2014

I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.

View 2 Replies View Related

Macro/ Formula To Match 2 Rows Of Data

Apr 17, 2009

I have multiple rows of data which I have sorted by two identifiers. Where a set of data matches on both of these identifiers I would like to further compare the two rows based on a set of 8 criteria (in coulmns C-J). Where the rows match across both identifiers and across the 8 sets of criteria I would like to merge these into 1 row and copy to another sheet, where there is a mismatch on 1 of the 7 sets of criteria or if a row's 2 identifiers do not match another row- I would like the data to remain as is (or move to another sheet for non matches).
Please see below example.
For Pair 1 & 2: these match across the 2 identifiers and across all 7 sets of criteria so these would be shaded white and moved to a different sheet (or the addition of match at the end would also suffice).
For pair 3: these match on IDs but not on all 7 criteria again "no match" would suffice here.
For the final row it does not match on ID therefor I would need to "no match".

View 9 Replies View Related

Formula To Identify Data Type And Calculate Monthly And Cumulative Figures?

Jun 7, 2014

I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.

If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.

View 2 Replies View Related

Formula To Look At Data In Multiple Columns And Check For A Match

Nov 15, 2012

Any formula that can look at the data in multiple columns and check for a match - returning a value such as YES or NO. Below is an example of my sheet.

A
B
C
D
E
F
G

1
Name
Number
Name
Number
Name
Number
Match?

[Code] ..........

Basically the formula would be in Column G and would look at B, D, & F to see if they match or not. In the example above - row two - they all match. So a True, 1, or Text option such as YES would be great. Same as row 3 only you can see they are different.

View 5 Replies View Related

Index / Match Formula That Effectively Reformat Data

May 10, 2013

I need a formula that effectively reformats data.

Original data:

Country
Name
2010
2011
2012

A
John
5
6
7

B
James
3
4
5

Into this format:

Country
Name
Year
Value

A
John
2010
5

[Code] ...........

View 5 Replies View Related

Formula To Reorder Data - Match/Sort/lookup

Jan 4, 2007

I have a data entry sheet for attendance that shows the individuals name, date and hours attended. Each individual attendance is addedd to a new row. I need to transpose this data and display it in a report table, with the names in column A and showing hours worked for each day of that week. The formula needs to accomodate the growing data entry sheet as records are added (I'm using a date picker to change the week in the report sheet).

I've tried match, lookup and index and have confused myself thoroughly! Below is a sample of the data entry sheet (on left) and report table (on right)

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1I1J1K1L1M1=
ABCDEFGHIJKLM1SurnameDateHours**Surname1-Jan2-Jan3-Jan4-Jan5-Jan6-Jan7-Jan2Smith1-Jan-076**Smith63600363Jones1-Jan-076**Jones66000304Rogers1-Jan-076**Rogers60600005Hathaway1-Jan-076**Hathaway60600006Pearson1-Jan-076**Pearson60000007Wills1-Jan-076**Wills60000008Smith2-Jan-073**********9Jones2-Jan-076**********10Smith3-Jan-076**********11Rogers3-Jan-076**********12Hathaway3-Jan-076**********13Smith6-Jan-073**********14Rogers6-Jan-073**********15Rogers7-Jan-076**********16Rogers8-Jan-076**********17Rogers5-Feb-076**********18Smith6-Feb-076**********19Jones6-Feb-076**********20Rogers

View 9 Replies View Related

Build Index-Match Formula To Retrieve Number From Data Sheet?

Aug 5, 2014

I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.

View 6 Replies View Related

Match And Calculate Average

Mar 15, 2013

I need to calculate the average based on SKU patterns.

For example, if the SKU begins with "CZD", then take the average SKU that meets that pattern.

SKU
Items Sold

CZD80
45

[Code] ......

View 8 Replies View Related

Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies View Related

Array Match Formula Fails But Simple Match Works?

Feb 5, 2014

In sheet 1, I have

Col A Col B
John Sedgwy - R

In sheet 2, I have the following names

Col A Col B
Peter Walker
John Sedgwicky

When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}

gave me an error but when I did an individual match to both John and Sedgw, it works.

View 4 Replies View Related

Reference Cell Only If Match Is Found Else Calculate Sum

Dec 14, 2012

I am struggling with a formula, that will display a certain cell only if a match is found, else produce a sum. It will be easier to look inside the document rather than explaining it on here. Described as well as I can inside the document

(None of the data used is factual. I created it myself to replicate)

Overtime for Payroll.xls

View 2 Replies View Related

Adding A Third Match To An Index Match Formula

Oct 6, 2009

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

View 3 Replies View Related

VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

Oct 8, 2009

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

View 2 Replies View Related

Excel 2003 :: Calculate Sum Based On Partial Text Match And Latest Date

Jun 19, 2014

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00

View 14 Replies View Related

Nested IF (index And Match): Calculate A Charge In Cell K15 Based On Values In B9 And C10 Referencing The Table I8:R9

Jan 21, 2010

I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).

View 2 Replies View Related

A Formula Which Is Calculate 4-5 Dates Formula In One Cell

Oct 7, 2009

i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.

View 3 Replies View Related

Formula To Calculate GST

May 6, 2013

Formula to calculate GST? I have a Non-capital and a Capital column, then a Claimable GST column.

I currently have '=SUM(E5/11)' in the Claimable GST column (G5).

If E5 is a zero value I need the sum of F5/11 in G5.

View 2 Replies View Related

Formula To Calculate TAT?

Feb 22, 2014

I need to calculate a TAT formula for the below case

P1 -- 1 Hour
P2 -- 2 Hour
P3 -- 4 Hour
P4 -- 8 Hour

Shift Start time : 6:30pm
Shift End Time : 3:30am

Suppose a request comes at 9:30pm and it is Priority 1 (P1) then it should be completed at 9:30pm + 1 hour = 10:30pm. I have given the below formula to execute this

=IF(A2="P1",1,IF(A2="P2",2,IF(A2="P3",4,IF(A2="P4",8,0))))

=B2+(TIME(D2,0,0))

The problem here is if the request is P4 then its 8 hr TAT and the formula will calculate the time as 5:30am. Since the shift time end at 3:30am itself the actual TAT should be the next day 8:30pm.

formula to calculate the TAT which includes the start time and end time and also excludes the weekends and holidays.

View 14 Replies View Related

Formula To Calculate A %

Nov 2, 2009

I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0
in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).

View 3 Replies View Related

Formula - How To Calculate For The 35 Min At Say 15.24

Dec 23, 2008

i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.

for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.

the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.

so i need to know how to calculate for the 35 min at say 15.24 ph.

View 14 Replies View Related

Formula To Calculate How Much Tax To Pay

Nov 25, 2006

Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure

The FIRST £1520 should be multiplied by 0.1 (in cell C19,)
The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)

View 9 Replies View Related

Formula To Calculate Age

Jun 20, 2006

i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A

View 9 Replies View Related

Introducing Formula With VBA But It Does Not Calculate

Mar 23, 2013

I am introducing a formula with VBA but it does not calculate it. When I get out of the execution of the macro, hit F2 then Enter, the formula calculates now. Obviously I don't want this because then I would have to do F2-Enter to all the cells. I need that when VBA pastes the formula and the formula sends me the result instead of sending me the #NAME? error.

The formula is: =if($A$2<>$A$3,TODAY())

I have tried the next instructions with no success:

VB : ActiveCell.Formula = "=if($A$2<>$A$3,TODAY())"

VB : ActiveCell.Value = "=if($A$2<>$A$3,TODAY())"

VB : Activecell.FormulaR1C1 = "=if($A$2<>$A$3,TODAY())"

And so on. Some of them would send me a compile error, and those that paste the formula give me the #NAME? error.

View 4 Replies View Related

Formula To Calculate Days?

Apr 22, 2014

I am currently using the following formula which has been working great for what we needed it to do:

=IF(ISERROR(NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1),"Not Completed",NETWORKDAYS(O34,Q34,Holidays!C$2:C$20)-1)

The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.

As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.

I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.

Sample.xlsx

View 4 Replies View Related

Formula To Calculate Usage?

Apr 25, 2014

how I have the data arranged... What I need to do is for each type I need to be able to calculate usage, so I will need to make sure it is subtracting say COKE usage the week of APRIL 1ST where beginning inventory on april 1ST was 99 and on April 7th the invnentory was 72, of course the usage is 99-72/7 will give me per day but I need the formula to be able to know which date will correlate to know which inventory to subtract from... So each time I add a new inventory in the sheet it will always be able to find where the data is it needs to subtract from to get the proper usage... Of course do this without manually entering the usage each week...

View 1 Replies View Related







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