Formula Involving Replacement.

Feb 20, 2009

I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......

View 4 Replies


ADVERTISEMENT

Formula Involving Four-Digit Military Time

Jul 22, 2013

In A1, I've got a time for an event expressed in military time without colons, like 1130.

In B1, I've got two more times expressed the same way, like 1230-1300. These are the time the event was actually began and ended.

I've separated these two times into four different columns, the first two (C1 and D1) containing the raw numbers 1230 and 1330, and the second two (E1 and F1) containing those numbers formatted as times (12:30 and 13:00) via the TEXT function =TEXT(C1,"00:00")

Getting the difference between these two times (expressed in minutes) is not difficult. What is proving to be difficult (however simple it seems) is checking to see if C1 (time event began) is within an hour of A1 (time event was scheduled.) I was able to get this to work with times not involving 0000 or having, say, 0130 as an event time and 2300 as the time the event was started, but those two cases (switching 0000 to 2400, and moving between "days") is causing a hangup. Checking if A1

View 1 Replies View Related

Formula - Travel Itinenary Involving Text And Number

Mar 3, 2009

Am doing some preliminary planning for a trip in Australia and am struck on a formula that will produce information like "Day 1" Day 2 and 3" etc.

I have done out this information manually and it appears in column E of attached copy of spreadsheet ...

View 9 Replies View Related

WORKDAY Formula Replacement W/o Use Of Analysis Add-In

Jun 17, 2008

My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

View 9 Replies View Related

Non Volatile Replacement For INDIRECT In SUMIFS Formula

Dec 4, 2013

Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.

My original formula was:

=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")

However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:

=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))

However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.

View 3 Replies View Related

COUNT Involving Several Columns

Sep 7, 2009

I need to find a formula on cell A2 on the attached file. The formula should show me the count of Customers who buy SKU 1 only.

I tried to use filters across SKU (2-16) where there is "0" and then count all customers who have values >0 on Column B. But this takes a long time and I have to do similar tables quite a few times. I was just hoping if anyone can come up with a more efficient solution to it.

View 6 Replies View Related

Sum Of Elapsed Time Involving Comma

Aug 12, 2014

I have been currently working with Data in which involves time over 24 hours and I am trying to find the sum of these numbers.

An example would be the following :

4,889:38:33 +4,711:28:37 + 4,851:11:26 .

Of course, I have removed the comma and changed format, this did correct the issue. However, any way to find the sum with the comma still involved.

View 4 Replies View Related

Loop Involving Multiple IF Statements

Feb 8, 2012

I want to loop through a range of cells changing the value of each cell by increments of 0.01 until a value of 10 is reached.

If the loop encounters a blank cell I want it to start the loop at M110 again

If the starting value of cell M117 is greater than 10 then I want the loop to use -0.01 increments to reach 10

If the starting value of cell M117 is less than 10 then I want the loop to use +0.01 increments to reach 10

This is what I have written so far...

Sub Test()
Dim i As Single
i = 0.01

Range("M110").Select
Do Until Range("M117").Value = 10

[Code] .......

View 1 Replies View Related

VBA Macro Involving Rows And Columns

Mar 2, 2012

I have have a problem in my vba code

if my activecell is on example range("W78") then it will go to range("I5")

I made a code but it does not work, i cant use offset cause i have may columns but they all refer to the same column "I"

this is my example: Range("I & activecell.row").value

View 4 Replies View Related

Multiple Condition Involving Date

Feb 27, 2008

I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:

A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300

the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.

View 9 Replies View Related

VLookup - Involving Two Worksheets And Then Summing Data

Feb 10, 2014

I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4V▲,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.

As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:

[Code] .....

I also need to do a similar vlookup or match, etc with this formula too:

[Code] .....

Attached File : sample payroll.xlsm‎

View 7 Replies View Related

Row() Replacement...

Apr 15, 2009

I have a formula to get unique values from a column....Can anyone help me to re write with the funcations in attached file...

Formula...
=IF(ROW()-3>$B$22,"",VLOOKUP(ROW()-3,$B$4:$C$22,2,FALSE))

Macro is not allowed...

View 10 Replies View Related

Replacement For COUNTIFS

Jun 4, 2009

I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.

Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?

View 10 Replies View Related

Compile Error - Variable Not Defined Involving Multiple If Statements

Jan 27, 2014

I am trying to put two subs into a macro. They work individually, but when I put them together, I get a "Compile Error: Variable Not Defined" Message. I was told I have to define the variable in the sub multipleif(), but I am not certain as to how to do this. The error message highlights the line I have bolded below.

VB:
Option Explicit
Private Sub CommandButton1_Click()
Dim mth As Variant, txt As Variant, des As Variant, wdt As Variant, I, Cell

[Code]....

View 5 Replies View Related

Excel 2003 :: Counting Occurrences In Two Columns Involving Range?

Jan 29, 2014

I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.

formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?

View 2 Replies View Related

Random Sampling Without Replacement

Jun 5, 2009

I'm trying to take a random sample without replacement. The sample is also partially representative, e.g. if a certain proportion of the data are Class A and a certain proportion is Class B, that proportion is maintained in the sample. After the proportions are set, the observations within each class are randomized.

Is there anyway to sample a number of rows, where the number of rows is based on the value in another cell, without having to use a lookup function or index match? Sorry if the question is a little unclear; please let me know if you need any clarification.

View 10 Replies View Related

Running Average With Replacement

Mar 15, 2009

In the attached spreadsheet, I want to do a running average of the "To" column (column G) for each company throughout the 8 year period. The only problem is I want to do it with replacement based on column D, meaning if the same brokerage firm comes out with a new announcement, it will replace the value of the old one in the running average calculation.

Here is an illustration of what I mean:

Looking at Company A between row 2 and 12, the value for Consensus in row 2 should be the average of Column G in row 2, row 3 is the average of Column G in row 3...row 11 is the average of Column G in row 2 to row 11, but row 12 will be the average of Column G in row 2 to 12 be excluding column 5's value because both column 5 and 12 are issued by the same brokerage.

I want excel to replace new recommendations by brokerages over the old ones in the running average calculation.

View 6 Replies View Related

Replacement For Using Indirect (Volatile)

Oct 30, 2009

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

View 6 Replies View Related

Replacement Of Suffix Character

Mar 1, 2007

We have two characters : One debit shown as Dr
Another Credit shown as Cr

we have an excel sheet containing a problem as under:
row 1 row2
column 1 Sales 345670 Dr
column 2 Receipt 340000 Cr
column 3 TDS 5670 Cr

We have replaced the Suffix so that it looks as under:

Sales 345670
Receipt 340000 Cr
TDS 5670 Cr

Wewant the last two lines to have figures in the negative ie - 340000
- 5670
i.e. cr suffix change to negative figure -340000

View 9 Replies View Related

Cell Replacement (Substitution)

Feb 17, 2008

I have a cell with a url in it, Say in A1 I have the following url:
mrexcel.com/forum/forumdisplay.php?f=10

I want Cell B1 To copy what's in cell A1 but delete everthing after the backslash"/" In B1 want it to show: mrexcel.com

What I have now doesn't work, which is:
SUBSTITUTE(A1,"/*","")

An attempt to use a wildcard (*) to remove everything after the backslash. Doesn't work
What's the correct formula that will allow me to do this automatically?

View 9 Replies View Related

Replacement Of COUNTIFS Within 2003

May 29, 2008

Assist me in getting the following statement into Excel 2003 format - it is working OK in Excel 2007.

=COUNTIFS('Sheet 1'!$AB:$AB,"True",'Sheet 1'!$F:$F,'Sheet 2'!B13,'Sheet 2'!$J:$J,">="&H2,'Sheet 1'!$J:$J,"

View 9 Replies View Related

Delete Picture & Add Replacement

Dec 11, 2006

I have some code to insert a picture based on the value of a cell. The user would select the name of the item by selecting the cell and then clicking a macro button.

In my code I need to delete the previous picture first. I used the following
Sub get_system_picture()

Dim pic As Shape
Dim picname As String

picname = ActiveCell.Value
On Error Resume Next

'exit sub if selection is not a system number
If IsEmpty(ActiveCell) Then
MsgBox "Please select a system number first!"
Exit Sub
End If

If Not ActiveCell.Column = 2 Then
MsgBox "Please select a system number first!"
Exit Sub
End If

The problem is this also deletes my macro button! Any suggestions?

View 9 Replies View Related

Excel 2003 :: Counting Occurrences Involving Two Criteria In Separate Columns?

Nov 13, 2013

I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.

I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.

View 2 Replies View Related

VBA Replacement For Index And Small Function

May 10, 2012

I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.

Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.

IF(ISERROR(INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2)),"",INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2))

Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.

These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.

The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.

What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.

View 4 Replies View Related

Application.filesearch Replacement For 2007

Jun 26, 2008

I am trying to search a folder that is selected by the user for all .jpg files, and then place all those pictures onto a certain spreadsheet within the excel file. It also formats the pictures. Following is the
Private Sub BatchProcessThumb2x3()
Msg = "Select a file containing the photos you want to insert."
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) "" Then Directory = Directory & ""

On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = "*.jpg"
.SearchSubFolders = False
.Execute

' Select begining range
Worksheets("Thumbnail (2x3)").Select
Range("B4").Select
' Loop through all files and process them

For i = 1 To .FoundFiles.Count..............

View 9 Replies View Related

Sumproduct Replacement. Calculations Slow

Aug 9, 2006

I was wondering if is possible to replace SUMPRODUCT with code. Right now I have formulas like these throughout my sheet and I think they are causing it to be slow:

=SUMPRODUCT(--($B$4:$B$1002<=B4),--($M$4:$M$1002="PROD"),--($O$4:$O$1002="O"))

I also have the following code in my worksheet. I don't know if anything here is causing it to take so long to "calculate cells." Also my code just stopped coloring the row ranges and I not sure what cause it to stop working.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O:O"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'Begin coloring row ranges based on these requirements
If .Row > 3 Then
If Me.Cells(.Row, "O").Value = "" Or Me.Cells(.Row, "O").Value = "O" Or Me.Cells(.Row, "O").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If.................

View 6 Replies View Related

Macro To Do A Global Replacement Dependent Of Languages

Nov 22, 2006

How could I have a macro which translates formulas on file open since Excel doesn't do it itself for functinos from add-ins like YEARFRAC?

View 9 Replies View Related

Application.filesearch Replacement For Office 2007

Jun 20, 2007

In the following thread, richphillips wonders about Application.Filesearch
Application.filesearch In Office 2007

Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function

Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
Debug.Print CurDir
strFolder = BrowseForFolderShell(, , , 0)
If strFolder = "" Then
MsgBox "You Cancelled"
Exit Function
End If............

View 9 Replies View Related

Replace Method Error When Replacement Too Long

Oct 4, 2007

I have the following code written:

If InStr( Cells(i, 3).Value, "Other") > 0 Then _
Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value

This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?

View 2 Replies View Related

Lookup Matching Values & Return Replacement

Aug 11, 2008

Im trying to work out how to write some code to find and replace from a list.

I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.

Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.

I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...

View 8 Replies View Related







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