2007 Right Function With Embeded Find Function
Mar 26, 2009
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
View 2 Replies
ADVERTISEMENT
Apr 6, 2007
Formulas do not work now. Is there an updated version of the More Function for Excel 2007 that I need to get?
View 9 Replies
View Related
Jun 25, 2013
How to use the Month function in VBA but am not getting the results I expect after reviewing other websites.
I'm running MS Excel 2007.
Simple example:
the worsheet cell, A1 has "5/14/2013" , cell format is DATE
I want to get the month as a number. I know that year(cells(1,1)) results in 2013 so in the immediate window, I try
?month(cells(1,1))
?month(range("A1"))
Both result in Run-time error '13' Type Mismatch.
What am I misunderstanding about the Month function?
View 3 Replies
View Related
Dec 10, 2009
This is probably quite a simple request but here goes:-
=SUM(h2,e2-f2)
I would like excel to perform this calculation but only if another cell contains a date. I couldn't figure out how to do that so I added another column next to that cell and added =ISNUMBER(A8) to return a true or false statement. I was then going to hide this column but still refer to it.
transaction cleared amount amount balance
date date in out
A B C D E F H
1 $500.00 BALANCE BROUGHT FORWARD
2 1/1/10 TRUE 1/2/10 TRUE $5.00 =SUM(h2,e2-f2)
3 1/2/10 TRUE FALSE $300.00 =SUM(h3,e3-f3)
4
5
6
7
I don't know if this makes sense of if anyone can suggest another way of working this out. New to excel so sorry if this is basic stuff.
View 9 Replies
View Related
Sep 14, 2009
I have a workbook that I am working with and I need to formatting the cells so that they will have multiple formulas and I am not sure how to make it work.
Sheet1 Column A is titled 'accounts'. There are 2700 rows under Column A that are being used. Column A is already formatted with a Vlookup formula so I can't add another formula in these cells.
What I need to do is highlight many ranges of these 'accounts' cells depending on their account numbers. I need to input this formula in a separate location on the spreadsheet. For example; If 'accounts' is >=110031100000 AND <=110031100099 then highlight those accounts in 'RED'. and If 'accounts' is >=1200454000000 AND <=120045400099 then highlight those accounts in 'green' and so on...I am not sure how to word the if statement. The formula must reside in column D or greater and not in A.
Am I going about this the wrong way. What I am trying to do is isolate the cells (accounts) that I have referenced on another worksheet so that I can visually notice any accounts that may have been excluded.
View 2 Replies
View Related
Jan 6, 2010
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
View 14 Replies
View Related
Jan 28, 2010
I have this COUNTIFS expression buried in a larger formula:
COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1")
and it always returns a value of zero or some positive integer.
I'm now looking for a little "function" that I can wrap around this expression that will:
A) indicate any non-zero result as 1 or TRUE
and
B) indicate any zero result as 0 or FALSE
Something like...
AREYOUNONZERO( (COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1") )
I'm sure there's a fairly efficient way to do this... but I'm totally stumped at the moment!
View 7 Replies
View Related
Aug 11, 2012
I am using excel 2007. I am trying to calculate the internal rate of return, without creating an array. I figured out that I can use the rate function to give me the same answer as the IRR function if I have one payment stream of say 36 months @ 1000. Where I am an running into trouble is if I have second payment stream of 6 months extension of 1500. Rate # pay 36 amt 75 PV = 5000 FV 500 , How to handle the the last 6 months ?
This is the situation
asset costs is 5000
monthly lease is 75 for 36 months
extension for 6 months is 125
end of lease buyout is 500
what is the internal rate of return without creating an array.
View 4 Replies
View Related
Nov 25, 2011
I'd like to know if it's possible (Excel 2007) to insert a function/button into a cell, which when clicked, will paste whatever is on the clipboard?
You can see a screenshot of my document here, and I'd like to insert a paste funtion in the red circle. Is it possible?
[URL]
View 9 Replies
View Related
Mar 19, 2013
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
I have formulas such as above in my Worksheet. So the above is in Cell H5
For the Range $C$45:$F$80, I am inserting rows (So moving down the data) and copying data from $C$5:$F$40 as values into the new space in $C$45:$F$80
I am doing the above using a macro, but when I run it I want this
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
to stay static, but instead I end up with
[CODE=D5-VLOOKUP($C5,$C$85:$F$120,2,0)[/CODE]
So my Table Array $C$45:$F$80 changes to $C$85:$F$120
How can I keep it as $C$45:$F$80
The other references in the formula seem to stay as I want them.
View 2 Replies
View Related
May 15, 2014
I need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2
I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I am running Excel 2007.
View 3 Replies
View Related
Nov 26, 2011
I have never worked on workdays before so I do not know most of the available formula and play around. I googled across and found this one: [URL] .......
Where Bob has given an ARRAY formula.
Following is my layout in A1 to C3 grid (for testing purpose):
Start Date11/1/2011HOLIDAYEnd Date11/30/201111/3/2011NETWORKDAYS2011/22/2011
The formula resides in B3.
Is there any other development in 2007+ versions of Excel which can handle 6 working days in a week?
View 9 Replies
View Related
Feb 28, 2013
excel 2007. Here is the situation:
I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:
A
B
C
D
[Code].....
So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.
The formula I have at the moment is this:
{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}
How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?
View 3 Replies
View Related
Mar 26, 2013
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
View 2 Replies
View Related
May 15, 2014
I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.
I'm using Excel 2007 - Windows.
Here is the code :
Sub Macro1()
Dim i As Integer
Dim j As Integer
j = 4
For i = 1 To 4000
If ActiveSheet.Cell(i, 12).Value = 1 Then
ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)
[code].....
View 2 Replies
View Related
Oct 10, 2011
I recently upgraded from Excel 2003 to 2007, and the worksheet change procedure that i have embedded in my worksheet no longer fires when the criteria are met. If and if I fix it to work in 2007, will it still work in 2003?
Here is my procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Excel.Range
If Not Intersect(Target, Range("C19:R19")) Is Nothing Then
For Each rngCell In Intersect(Target, Range("C19:R19"))
If rngCell.Value = "BLACK" Then
MsgBox "Please select a shading style", , "Shading Style Required"
End If
Next rngCell
End If
End Sub
View 2 Replies
View Related
Feb 8, 2012
I'm using Excel 2007. When I try to scroll with the mouse wheel, it doesn't do anything. If I hold down the control key and scroll with the mouse, it zooms in and out. So that works fine, but I can't do the basic scrolling up and down the document with the mouse wheel. There is no "Tools, Options" menu in 2007 so I don't even know where to find this type of option. The options available from the Office button are completely different.
View 4 Replies
View Related
Dec 8, 2008
So I have 2 worksheets. One has a list of Player Names. The other has a list of Plays that any of these players could have been involved in (or they may not have been involved in).
I am trying to use the find function to find anyone of those player names from sheet 1 in each of the plays. My functions currently stands at
=FIND(('Dinas Roster'!$A$2:$A$21),I6,1)
Where Dinas Roster is the list of players and I6 is a particular play and would go to I7, I8....which are the plays,
This isn't working as I am getting all #Value. If I just do it for 1 Player (i.e. Dinas Roster'!$A$2) it works and returns a number on each line that player shows up in...
View 12 Replies
View Related
Mar 7, 2007
I need a find anywhere function.
Example
text to find is in ZZ1
area to find the text is A1:BB500
It could be anywhere in that area.
I don't want to do the find command 1000+ times for all the data i need to search for.
Vlookup is just column A, i need column A:BB
I have no idea what column or row it would be in.
basically, look for text from sheet2A1 anywhere in sheet1
View 9 Replies
View Related
Sep 28, 2006
I'm looking for something simple that will let me type in a 5 digit numeric value and then when it finds the associated value, will add +1 to the associated col/row.
Such as, type 11111 in the box, click the button...When it finds the data on say row 8, col A it will then add +1 to row 8, col G.
I'm not sure of the best way to do this...I'm not exactly new to excel, but it has been a VERY long time (10 years!) since I've used it in this aspect...Just trying to help a friend out.
View 9 Replies
View Related
Jun 12, 2007
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.
Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If
View 9 Replies
View Related
Aug 2, 2014
I am using Office/Excel 2007 and Windows 8
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
A
B
C
D
E
F
G
[Code] ..........
results for each formula used
95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)
95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))
3 formula: =COUNTIF(B1:G2,B1)
View 9 Replies
View Related
Jan 30, 2014
I want the create the following summation function (probably with the use of FIND).
You have 3 columns:
- Column A containing values; a, b, c, d, e
- Column B containing values: 1, 2, 3
- Column C containing ad randum values f.e. lengths
I want the achieve a summation of the lenghts with the following conditions 'a' in column A AND '1'in column B.
Apart of this a summation of the lengths with the conditions 'a' in A AND '2' in B.
[Code]....
View 2 Replies
View Related
Jan 8, 2009
In Sheet 2 i have a 1000 of data contains the birth date of following customers
The result i want in Sheet 1 is
particular on todays (Say on 27-11-2008) date how many customer are having birthday, supoose there 10, or 8 wahtever should show me the list.
I tried this formula
=INDEX(Sheet2!$B:B,MATCH($B$3,Sheet2!$H:$H,0))
but by this formula it only show one customers birthdate what i want if there 10 differenrt customer those same birthdate it should display all the 10 date and name in diff rows
View 12 Replies
View Related
May 11, 2009
Attached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".
View 11 Replies
View Related
Dec 20, 2009
Need a function that returns the days from the last week of a month?
View 3 Replies
View Related
Feb 16, 2010
I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View 10 Replies
View Related
Nov 4, 2005
I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there.
View 9 Replies
View Related
May 8, 2012
I have a column of values (text) to choose from. I need to randomly assign them to names.
There are more values then names.
Each name has to have a value.
Each value has to be used only one time.
Not every value has to be used.
Column B contains names, column Q contains values.
Now. So far I've come up with below:
Code:
Sub randomize()
Dim Random As Integer[code].....
"Random" returns exactly the amount of values in column Q, so this works ok.
"i" counts iterations correctly, meaning as many times as names in column B.
I tried useing Match to rule out already used value but this code returns "unable to get Match property of the WorksheetFunction class" error.
View 6 Replies
View Related
Jan 7, 2013
I have a worksheet with dates on and have user form to display dates within a range. I have created some code, but the find function errors and says it cannot find this value on the sheet, but it is definatly there. This is my code: I have added an asterix to where it errors and says it cannot find the value
Code:
Private Sub SearchButton_Click()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim FoundFrom As Range
Dim FoundTo As Range
With Worksheets("Calender")
[code]....
View 2 Replies
View Related
