Calculating Gross P/l On 2007 Worksheet

Jun 13, 2008

I have a trading statement sheet that contains P/L Gross
e.g
3.00
16.00
(12.00)
12.50
18.00

now using that format with the loss(1.00) being braketed how do I use countif to tell me how many wins and how many losses.

I am also looking for a formular to calculate the average win/loss

View 14 Replies


ADVERTISEMENT

Excel 2007 :: Formulas Not Automatically Calculating?

Jul 3, 2012

Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.

- Calulation set to auto in Excel Options.

- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.

- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.

View 2 Replies View Related

Excel 2007 :: Calculating IRR Using Rate Function?

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

Net To Gross Calculation - Payroll

Jul 7, 2007

I have created a spreadsheet to work out deductions from gross weekly pay to give a net amount. I would like to be able to get from a net amount back to a gross amount but am unsure of how to do this.

Deductions are tax and national insurance. A person has a tax code for example 885 meaning they can earn up to 8850 a year before being taxed of 8850/52 a week so anything after 8850 is taxable. There are two tax rates (10% )and (18%). The band for 10% is 10500 per year or 10500/52=201.92 a week. This means any taxable pay above 201.92 is taxable at 18%.

To work out National insurance deductions for an employee, weekly gross amount- 100 x 10% giving the National insurance to be deducted. So Gross - Tax - National Insurance = Net amount.

Attached is a spreadsheet I created to work it out. Firstly does this look okay? Secondly is it possible to get back to the gross amount given ONLY a net amount and a tax code?

View 4 Replies View Related

List Of Top Five Gross Profits

Feb 14, 2014

I've got a list of companies and what I need to do is find a way of showing the top five in order. I've found a way of showing the top five gross profits but I can't find a way of also showing which company this corresponds to as some of the values apply to more than one. Ideally I would like to do it with a formula or two.

I've attached how I sort of want it to look.

Attached Files : Top 5.xlsx‎

View 2 Replies View Related

Excel 2007 :: Calculating Man Hours Committed To Breakdowns

May 7, 2014

I'm creating an engineering report and were trying to capture the number of man hrs we are committing to breakdowns the section I'm interested is shown below:-

CraftJob Start TimeJob Finish Time
PL RD02.45 03.15

There are two personnel (shown as initials) in the craft cell with the start and finish times of the job. Can I determine the number of personnel by counting the sets of initials i.e. PL + RD =2 then multiply that number by the time worked in this case 30mins (this will vary) so time committed would be 2*30=60mins.

Excel 2007

View 14 Replies View Related

Excel 2007 :: Calculating Sales Close Rate?

Jun 13, 2014

I want to set up an Excel spread sheet where I can put my total number of sales appointments for the month and the percentage of those I closed. I've done this before, but for the life of me I can't remember the formula I used & I know it's a very simple one! I'm using Office 2007.

View 1 Replies View Related

Excel 2007 :: Calculating Innings Pitched From Other Worksheets

May 12, 2009

i have an excel 2007 spreadsheet where i keep baseball stats. On the pitching side have a column for the pitchers innings pitch. The data kooks like this:

1.0
1.1
1.2

where the numbers after decimal points represents thirds of an inning.
In my spreadsheet i have 4 workheets of different divisions that have a breakdown of each team the team i am keeping stats for plays.

i manually enter the innings pitched in the totals for the season as i cannot figure out how to add these numbers from the individual worksheets to calculate the right number. once the number after the decimal point is greater than two than another full inning is recorded not a continuation of the number.

Example pitcher has already pitched 2.2 innings and he pitches 1.2 innings today making a total of 3.4 which should really be 3.1 one last one pitcher has pitched .2 innings and pitches .1 today making a total of .3 innings pitched but it should be 1.0 innings pitched. proper formula to sum the correct totals in the totals worksheet. i am at a loss and ihave been working on this problem for a little while with no luck.

View 5 Replies View Related

Excel 2007 :: Macro Not Calculating Unless Value Entered Into Formula Bar

Apr 12, 2014

I'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.

For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.

[Code] .....

View 2 Replies View Related

Weighted Average Gross Margin

Mar 1, 2012

I'm trying to calculate a weighted average gross margin but am having difficulty because I've instances of positive cost of sales and negative revenue. On a line by line basis I've had no problems calculating the margin but when it comes to calculating the weighted average margin as a whole I come unstuck. Looking at the data below (A1:D14) you can see that the margin should be somewhere between 90-99%.

Total RevenueCost of SalesGross ProfitGross Profit %4,805(449)4,35690.6%4,992(330)4,66293.4%3,467(246)3,22192.9%4,974(317)4,65893.6%3,873(130)3,74396.6
%3,756(258)3,49893.1%3,955(128)3,82796.8%2,872(59)2,81397.9%1,401(1)1,40099.9
%(10,987)21,45810,47195.3%3,042(172)2,87094.3%2,748(40)2,70998.6%2,682(172)2,51093.6%

View 4 Replies View Related

Formula For 2 Low Net & 1 Low Gross Score In A 5 Man Golf Team

May 25, 2008

I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel.

I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or -1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on.

I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole.

A sample column would have 4,0,3,0,4,0,3,0,5,-1 in it.

View 9 Replies View Related

Excel 2007 :: Automatically Change The Worksheet Tab Names With Cell Value In Each Worksheet?

Feb 14, 2012

I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?

View 3 Replies View Related

Gross Margin Formula That Maintains Consistent Markup Price Across Varying Costs

Jan 8, 2014

So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?

So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.

View 5 Replies View Related

Smart Filter To Only Show Contract Items That Had Major Impact On Gross Profit

Mar 10, 2014

seeattached data set.

Contract Item
Gross Profit
GP %

[Code]....

It is Gross Profit in Dollars and % for a particular project. Each row is a contract Item. The Overall Gross Profit for the month in dollars is 34,114.86 In preparing a short commentary to discuss this 34,114.86 I need to discuss atleast some contract items. If there were no negative results I have often in the past simply written. The top 5contributors to this Gross Profit were contract items 1 -6, out of 42, delivering 80% of the result. I might also go into what actually those contract items were and why they presented such a decent Gross Margin, ie. Costs came in well under budget(estimate). But with negative results, the bottom 4 contract items represent a large dollar loss of-34k which is 100% basically of the Gross Profit itself. If these had even just broken even we would have a GP double the current amount. So they obviously deserve as much comment.

Q1
In excel orsimilarly in say Crystal Reports what is a good way of further sorting these 42records either in a table or chart, whereby say the top 80% contract items that had a major impact on the gross profit are shown and the rest hidden. Ie. All thelittle sub 1000 or sub -1000 amounts.

Q2
How wouldother people here write a commentary on this data set? Can you script something to illustrate?

View 3 Replies View Related

How To ADD ROWS To 2007 Worksheet

Jan 18, 2009

how to add rows which was so simple in earlier Excel versions, but now is bafflegab.

Even after several efforts to comply with what follows when at INSERT, all I got was to move cell I'm in one to the right with all other cells in the row following suite.

OF COURSE I had specified "entire row".

View 13 Replies View Related

Excel 2007 :: How To Autofill 1 On Other Worksheet

Nov 14, 2012

How to autofill "1" on the other worksheet, the situation as follow,

Sheet 1 is data input sheet.
Name
Start date
End Date

Ann
2-1-2013
5-1-2013

Billy
7-1-2013
9-1-2013

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

Sheet 2 is the Calendar and the autofill result should be like this.
Date
Ann
Billy

1/1/2013

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

View 2 Replies View Related

Excel 2007 :: Can't Add Columns To Worksheet

Jul 29, 2013

how to add many columns to the rightmost part of the worksheet carrying on from Col AA?

I keep getting error 'Cannot shift objects off sheet.' and followed instructions to change a setting to All or use control-6 but this made no difference.

View 7 Replies View Related

Excel 2007 :: How To Unprotect Worksheet

Aug 5, 2013

How can I unprotect MS excel 2007 sheet. I have forgotten the password.

View 1 Replies View Related

Worksheet Parser ( 2007 To 2003)

May 22, 2008

I'm struggling constantly with the Excel 2007 I use, and the Excel 2003 that another group uses.

I'm dealing with large files; 200000 to 300000 rows often.

I wish there was a setting or way to automatically force excel 2007 to create a file in 2003 format.....meaning I want 260000 rows to automatically be divided up into 4 sheets limited to 65K each. 260001 rows would be put into 5 sheets, so that I could save it as an excel 2003 file.

Is there also a way to do this from access 2007 exported to excel?(I know that's an access question and probably outside the bounds of this board).

View 9 Replies View Related

Excel 2007 :: How To Protect The Worksheet With Password

Dec 30, 2012

I know how to protect a worksheet with a password so that no one can amend the contents or view the formulas in the cells. And then we can un-protect the worksheet by clicking on the "review" tab in excel 2007 and so on. But recently I came across an excel sheet which was password protected and my query is that the "Review" tab was greyed out. What kind of protection was this that clicking on "review" tab option is also unavailable. And how does these kind of sheets get unlocked then.

View 2 Replies View Related

Excel 2007 :: Show Total From Different Worksheet

Oct 13, 2013

I'm fairly new to Excel (2007).

I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.

What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.

View 7 Replies View Related

Excel 2007 :: How To Lock Author Of The Worksheet

Sep 13, 2009

It is possible to lock the Author of the Worksheet in File --> Properties --> Summary Tab..?

I am using Excel 2003 & 2007..

View 8 Replies View Related

Excel 2007 :: Cannot Get Worksheet Name To Return With Formula

Nov 8, 2011

I am working on a sheet that will copy a specified number of sheets, rename them, set a cell within the equal to a cell from another sheet and (the problem) set cells on my input sheet equal to cells in the newly copied and renamed sheets.

I want the cells in sheet "Data Input" to look something like "='newsheet'E26"

I can make it return the actual number in the cell and return the formula in the cell, but not make the cell in "Data Input" reference the cell in the new book.

Here is what I have:

Code:
Sub CopySheetsTest()
Dim i As Integer
Dim wks As Worksheet

[Code]....

View 4 Replies View Related

Excel 2007 :: Extracting Criteria Into Another Worksheet?

Feb 15, 2012

Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.

I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.

[URL]

On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.

View 1 Replies View Related

Excel 2007 :: How To Insert Animated GIF Into Worksheet

Apr 27, 2012

How do I insert an animated gif into a worksheet in excel 2007?

View 8 Replies View Related

Excel 2007 :: Menu Bar At The Bottom Of Worksheet

Dec 31, 2012

I am using Excel 2007 on Windows Vista Business 32 bit. I have several workbooks. Each one contains 33 worksheets. 31 of the worksheets are for each day of the month, the other two are for yearly totals and revision history. Recently, a menu bar has appeared at the bottom of my worksheet. At the left is an icon that states that no macros are being recorded, click here to start recording. The center of the bar is blank, but placing the mouse over the bar brings up a customization menu.

At the right side of the bar are three icons dealing with page layout. As I scroll down the worksheet entering data for the day, the cursor goes below the bar and the screen does not move up as it used to do. Also, by using the arrows keys to scroll down, I do not see the bar containing the worksheet names. Luckily, I have a macro that allows me to save the current days worksheet and move to the next one, otherwise I would not be able to do that. When I open a previous month, the bar is there also, so it must be a flag that I can't find or don't know about. Is there a way to get things back to normal?

View 9 Replies View Related

Excel 2007 :: How To Lock Individual Cells In A Worksheet

Jul 23, 2014

How to Lock Individual Cells in a Worksheet excel 2007 .... i.e.

A2:A8

I want this selected area locked with password.

View 1 Replies View Related

Excel 2007 :: How To Change All Reference Cells In Worksheet

Aug 26, 2010

Here is my situation:

I created one worksheet called: Assumptions. In this worksheet I have 6 different columns representing 6 different cases.

I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B columns for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.

Any quick way to do the changes?

My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.

I was thinking there was a way to highlight ALL the reference cells used in my Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.

View 12 Replies View Related

Excel 2007 :: Login System For Two Users On Worksheet

Mar 7, 2014

I am creating a login system for two users on a excel worksheet. I have the two users on a drop-down list, Maria and Andrew, which then requires a password entry. This is linked onto a data validation to make sure that the password matches the data table.

After, I then click on a macro, which, when successful, takes me from sheet 1 to sheet 2.

My problem is that I need to have it so that Maria is taken from Sheet 1 to Sheet 2, and Andrew from Sheet 1 to Sheet 3.

View 1 Replies View Related

Saving Worksheet In Html Format Office 2007

Jan 20, 2008

I have been using code to save a collection of my reports in html format that has work very well until I was forced into using Vista and office 2007. Here is a snippet of the code that no longer works - at least I suspect this is the main problem.

View 4 Replies View Related







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