calculating return in a range

Hi,
I have a range of stock prices. with indication for buy or sell. and i
want to calculate the return of the range for example :  

buy	522.73
buy	527.9
buy	528.81
buy	532.33
buy	535.1
buy	536.23
buy	537.03
buy	538.57
sell	537.54
sell	532.86
sell	538.14
sell	533.08
buy	531.22

I want to calculate the return for the first buy signal. the range is
from the start to the first sell signal 537.54- 522.73
then the sell range untill the first buy signal 531.22- 537.54 
and so on....


-- 
kman
------------------------------------------------------------------------
kman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=495926

0
12/24/2005 8:06:12 PM
excel 39879 articles. 2 followers. Follow

3 Replies
630 Views

Similar Articles

[PageSpeed] 50

See my answer in your OTHER post. Not necessary or desirable to post in more 
than one group.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"kman" <kman.20k92n_1135455001.2491@excelforum-nospam.com> wrote in message 
news:kman.20k92n_1135455001.2491@excelforum-nospam.com...
>
> Hi,
> I have a range of stock prices. with indication for buy or sell. and i
> want to calculate the return of the range for example :
>
> buy 522.73
> buy 527.9
> buy 528.81
> buy 532.33
> buy 535.1
> buy 536.23
> buy 537.03
> buy 538.57
> sell 537.54
> sell 532.86
> sell 538.14
> sell 533.08
> buy 531.22
>
> I want to calculate the return for the first buy signal. the range is
> from the start to the first sell signal 537.54- 522.73
> then the sell range untill the first buy signal 531.22- 537.54
> and so on....
>
>
> -- 
> kman
> ------------------------------------------------------------------------
> kman's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=3337
> View this thread: http://www.excelforum.com/showthread.php?threadid=495926
> 


0
Don
12/24/2005 10:45:21 PM
Another response to try out, as posted just
to your earlier post in .worksheet.functions
(as Don has advised, please do not multi-post ..)

------
Another play to try ..

A sample construct is available at:
http://www.savefile.com/files/9864468
Calculating return in a range_kman_wks.xls

Assuming the data as posted is in cols A and B, row1 down
Put in C1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Put in C2: =IF(A2="","",IF(A2=A1,"",COUNTIF($A$1:A2,A2)))

Put in D1: =IF(C1="","",A1)
Copy D1 down to D2

Put in E2, array-enter the formula (i.e. press CTRL+SHIFT+ENTER):
=IF(A2="","",IF(A2=A1,"",B2-INDEX(B:B,MATCH(1,($C$1:C2=MAX(IF($D$1:D2=A1,$C$
1:C2)))*($A$1:A2=A1),0))))

Select C2:E2, copy down as far as required
Col E should return the desired results
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"kman" <kman.20k92n_1135455001.2491@excelforum-nospam.com> wrote in message
news:kman.20k92n_1135455001.2491@excelforum-nospam.com...
>
> Hi,
> I have a range of stock prices. with indication for buy or sell. and i
> want to calculate the return of the range for example :
>
> buy 522.73
> buy 527.9
> buy 528.81
> buy 532.33
> buy 535.1
> buy 536.23
> buy 537.03
> buy 538.57
> sell 537.54
> sell 532.86
> sell 538.14
> sell 533.08
> buy 531.22
>
> I want to calculate the return for the first buy signal. the range is
> from the start to the first sell signal 537.54- 522.73
> then the sell range untill the first buy signal 531.22- 537.54
> and so on....
>
>
> --
> kman
> ------------------------------------------------------------------------
> kman's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=3337
> View this thread: http://www.excelforum.com/showthread.php?threadid=495926
>


0
demechanik (4694)
12/26/2005 1:15:51 PM
> Put in C1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Formula above is a little superfluous (clean-up overlooked earlier, sorry)
Simply put in C1: =IF(A1="","",1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
12/27/2005 12:48:06 AM
Reply:

Similar Artilces:

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

use displayed value in calculation
quick question - if I perform a simple calculation of =(a1+b1) in the m1 cell, and then need to use that result in another calculation in the n1 cell =(m1*3.14), why do I get a value of 0.00 returned? michae What values are in cells A1 and B1? A small number, showing two decimal places, could display as 0.00 michael wrote: > quick question - > > if I perform a simple calculation of =(a1+b1) in the m1 cell, and then > need to use that result in another calculation in the n1 cell > =(m1*3.14), why do I get a value of 0.00 returned? > > > > michae > -- ...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

create chart from non adjacent range
I need to know how to do this Select one area, then hold CTRL while selecting the next area, etc. Then run the Chart Wizard as usual. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rita" <rita@discussions.microsoft.com> wrote in message news:18C9672C-0EAB-401C-8798-4C51158EAF8B@microsoft.com... >I need to know how to do this ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Calculating Time Difference
Hi, I am trying to develop a query where my client wants to track how much time in minutes does a nurse spend in various activities (staff education, patient/family education, answering drug relation questions etc. etc.) to assist nurses in other units. What query type should I use? How do I write the query? There is a start time (when the nurse recieves the call) and completion time (when the nurse hangs up). Thanks Mrinalini <drmrinalini@gmail.com> wrote in message news:1176645431.981118.294870@e65g2000hsc.googlegroups.com... > Hi, > > I am trying to develop a query wher...

what tab for calculating percent and decimal?
I want to find the percent for each column. Which tab do I click to get the drop down choice for percent. I found it once and forgot to remember it. I also remember seeing the dropdown had decimal as well. I am just starting to explore using this for my grading system at school. Today is my first day exploring this site. try format>cells or right click>format>>>> -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Heidi" <Heidi@discussions.microsoft.com> wrote in message news:4BBF5660-5CD7-4756-9972-D980105...

Return receipt stuck in my Outbox.
I have a return receipt somehow 'Stuck' in my outbox. It can't be sent because I am not connected to the Server it is attempting to send from. Is there any way to delete this thing. Every send receive cycle returns an error and I just want to get rid of if it to stop the error. Create a new .pst and make it the default. Then open the old .pst outbox and delete the item. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Kevin J. Danker <kjdinoc@msn.com> asked: | I h...

formula to return 1st of the month following 60 days
Hello - I need, please, a formula to return the following. So, if someone started working with us on 10/15/09, the 1st of the month following 60 days of employment would be 1/1/10. That's what I need. THANK YOU! What about 1st of a month say 10/1/2009 'If you expect 1/1/10 then use =DATE(YEAR(A1),MONTH(A1)+3,0)+1 'if you expect 12/1/09 then use the below version =DATE(YEAR(A1-1),MONTH(A1-1)+3,0)+1 Regards Jacob "Jessica" wrote: > Hello - > > I need, please, a formula to return the following. So, if someone started > ...

Calculate meeting timings
Hi, I had one requirement. I want to calculate my total meetings time i spent througout the week, month or year. This will help how to manage my time between my work and meeting. Any solution to this??? ...

Unlocking named ranges
I am working with a spreadsheet created by a former employee. He used named ranges a lot. I am trying to understand the worksheet. When I see an equation in a cell like =Sum(Shipments) I feel I know what it means. But how do I learn what the actual cell reference is ie = Sum(D2:G2).? TIA Insert a new, empty worksheet into the workbook. Then you can use The Insert-> Name-> Paste menu option and click on the Paste List button to get a listing of all the names and the definitions "JohnL" wrote: > I am working with a spreadsheet created by a former employee. He used...

YTD calculation
I run spreadsheets with monthly columns for actual, budget, and variance. I have final YTD columns. I currently go in and add the latest month to get this total. It would be simpler if I could have a YTD cell with a value like 3 for March. If I then had text in column 1 it would sum 2,5 and 8 for actual, 3,6 and 9 for budget and 4,7 and 10 for variance. Changing just the YTD cell value to 4 for April would change this calculation. Anyone have a suggestion? Thanks. Michael =MONTH(TODAY()) will return 11 for November or 4 for April -- Regards, Peo Sjoblom "Michael" <...

Automatic Dynamic Chart Ranges
Hi: I have an XY plot that refers to a set range of values on my worksheet In this case, it is 8 rows long. I chose 8 rows because that is th maximum number of data points that I will ever need. I have set up th chart values so that they are dynamically calculated based on VLOOKUP from other data. Sometimes there are only 2 data points, sometimes or 6. The problem is that no matter how I set up the formulas, Exce does not like the cells without values in them being included in th chart series (I have hardwired it to include all 8 rows). I thought i might ignore a blank ("" in ...

Calculating dates & creating reports
I need to design a database that will calculate due dates for evaluations. It will have 5 dates. 4 are calculated. These dates need to be on a form along with the employee's name and other vital info. The user should be able to enter that Start Date and have all the other dates calculate automatically. There are approximately 55 employees! The first date (Start Date) is entered by the user. Example: User enters Start Date: 2/01/07. #2 Next Date is 91 days from 2/1/07, which calculates to 5/03/07. #3 Six Month Date is 182 days from Start Date, which calculates to 8/02...

print only range that contain information in excel 2007
i have a large worksheet to cover a large number of possible inserted entries, all cells show blank until entries are copied and pasted in the worksheet. The amount of entries vary. Is there a way to set the workseet to print only the range of cells with information in them ...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Select Records that fall in an external set of ranges (subquery)
HiI have not done subqueries before so I am a bit confused. I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges. 200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query...

Excel VBA
Hi, Is there any way to freeze (prevent) a number of worksheets from recalculating when a VBA macro is running? I have a spreadsheet with about 10 worksheets, most of which are linked to each other in some way. One of my macros only needs to use the formulas (and related results) from about 7 of these, so the other 3 are updating all the time but since the results from these are not needed then they are slowing down my code. Is there some VBA function for temporarily disabling a worksheets calculation function? I would need my code to look something like this: Code: -------------------- ...

Percentage tax calculation 07-01-05
Hi all, i need to set the tax calculation at fixed value of 20% but i doesn't see this features in crm. any suggestion? Thanks in advance, Joe On Fri, 1 Jul 2005 13:37:17 +0200, "Joe Fischietti" <joefischietti@removemelibero.it> wrote: >Hi all, >i need to set the tax calculation at fixed value of 20% but i doesn't see >this features in crm. >any suggestion? We have an add in that will do this. It allows tax rates to be set by percentage on quotes, orders and invoices. The tax rate is set for each product with a configurable default and may be over...

Ranges
Hi, I have some records on a spreadsheet, I want to select them with a macro the range is A1 to m1 down to however many records there happen to be? how do I specify this for my excel macro? Thanks, Jim. Jim try this to get the end Dim iLastRow As Long, liLastCol As Long On Error Resume Next iLastRow = 1: iLastCol = 1 With ActiveSheet.Range("A:M") iLastRow = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByRows, xlPrevious).Row iLastCol = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByColumns, xlPrevious)...

Using a named range as a data source for a chart
Hi, I'm trying to build a chart that that has a named range as its data source. The named range refers to a table that is 2x3 and is called geographydata. Is there any way that I can have the source data for the graph to be geographydata? Thanks! Michael You can enter the name, preceded by the worksheet name and exclamation point, for the data range, but Excel will convert this name to its cell address. If you want the chart to be dynamic as the name changes its size, you have to define a name for each series' data. This will not dynamically change the number of series in the ch...