Tracker
HOME    TRACKER    Excel

# Calculate The Profit And Loss

## I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss. I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it. Where: Market position is either 1 or -1 Entry price is the price where the position is reversed. For example: H3=1 and I3=100.56, then H4=-1 and I4=100.88, then H5=1 and I5=100.53 I need to calculate the profit as follows: I4 - I3 (because market position of 1 expects the price to go up) and then, I4 - I5 (because market position of -1 expects the price to go down) I want to calculate this as a running total to the bottom of the column. This is the formula I was working with, but it doesn't work: =IF(OR(\$J4="",ISERROR(MATCH(-\$J4,\$J4:\$J\$55,0)),),"",\$J4*(OFFSET(\$K4,MATCH (-\$J4,\$J4:\$J\$55,0)-1,)-\$K4))

Related Forum Messages:
VBA To Calculate Profit & Loss Of Each Race Bet
I had a member make the MACRO in the attached spreadsheet some time ago, when i run it on the spreadsheet a runtime mismatch error occurs. I cant find the mismatch.

Inventory And Profit/Loss
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.

In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.

When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.

I would like to be able to keep track of inventory by vendor as well as profit by vendor.

Using the Consolidation PT does not seem to work.

I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.

I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?

Averagif/averageifs :: To Compute The Realised Profit Or Loss
How to compute the realised Profit or Loss for each of the shares transacted for the year ended 31 December 2008 based on average cost of each share.

using AVERAGEIF, if appropriate. [Pls refer to the attachment.]

How To Calculate Max Profit Per Quarter
Formula to get the Quarter number for the highest profit per Product, like in B16:F16.

The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.

I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.

Auto Calculate Profit By Part #
I have attached a chart I'm working on. Each item is assigned a part number and it automatically takes away from the inventory each time an item is sold. I was wondering if the bottom half could work with the top to determine profit.

Example:

Customer purchases Part # 3 (C15) Quantity 1 (D15) for \$450 (E15). Is there any way to set it up so whatever part number they enter...it subtracts their sold price from the cost (C5 through C11) to show the profit in (F15).

Data Validation To Calculate Profit Margin
I have a price sheet for my store in Excel. I want to make the spreadsheet easy for my sales people to use to calculate differing profit levels. I'd like to embed a drop down box at the top (I can do that part) that they can select, say, 10%...20%...30% markup. I know how to do that basic formula. I don't understand how to link that formula to a number selected in a drop down box. I don't know how to make the prices change based on the value selected in the drop down box. I can't do vlookup because it's about 1500 lines long. Also, it doesn't have to be drop down box based--that's just the idea in my head. I tried just making a macro that would run when they hit the button, but when the macro runs it switches the focus back to the top, very annoying if you were looking at prices on cell D811.

To Calculate That Loss Or Gain As A Percentage Of Their Original Weight
I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?

Precentage Profit Formula
In cell "C4" how would I write the formula to calcuate my precentage profit based on the given cost and sell pricing.

How Do I Write Formula To Show A Profit Margin
I need to work out the profit margin from goods i sell.

I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6

I need to work out the margin between A5 and A6.

Hedging Simulation, Solver To Obtain A Maximum Profit
Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.

I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.

Totalling A Win Loss Column
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?

Formula For Calculating Win/loss Streaks....
I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?

Pivot: Gain/loss Over Time
I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate- i.e. how many customers subscribing to X in a given month/year.

The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (-1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:

Formatting Dates To Won-loss Values
I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "Won-Loss", which appears as '1-0' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '1-0' to 'Jan-'00. I would like to insert a new column and convert the field back to text (i.e. Jan-00 to 1-0). I have attempted several formulas, however no luck as of yet.

Friction Loss Calculator For Fire Hose
I am attempting to create a friction loss calculator for fire hose, I am using a known formula that calculates the loss based on volume and diameter for pipe. the difference that I have with fire hose in lieu of pipe is that the hose diameter changes with the pressure drop.

I have 3 variables that i input, pressure, beginning diameter and length. however
as the water flows through the line the pressure changes in turn the diameter changes, I would like to set up my spreadsheet so that the initial variables inputed yield the correct diameter, and then reference back to the initial equation and recalculate based on the yeilded diameter, and recalculates,

I can determine the friction loss at 1 foot, in turn determine the diameter at 2 foot, but I wish the spreadsheet to work the calculation over the entire length.

Find In The Win, Loss, Draw Columns When There Is No Entry
I m trying it out I find in the Win, Loss, Draw columns when there is no entry the formula leaves a 1 when there should be a 0 in the Draw column.

Find The Loss/gain Difference Between An Initial Value
I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.

Prevent Loss Of Copied Data On Clipboard
Using a macro I want to paste from the clip board.
I can manually do this by opening the clipboard, selecting the data, which pastes to my specified location.

The macro copies to the clipboard okay, but loses the data that I want to paste because just before pasting I have to clear data which was previously been filtered - I have to do this at this point. The macro bogs down. If I end the macro there, I can manually open the clip board and choose the data I copied from there and paste it that way. I'd like the macro to do this for me.

Running Total Of Weight Loss & Percentage
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.

Calculating The Average Gain & Loss For Numbers In One Column?
I wanted to know how I can calculate the average gain and average loss for data that is inputted into one column? For example;

Column A
\$12,000
-\$5,000
\$6,500
-\$1,000
\$8,000
-\$4,000

Average Gain ??
Average Loss ??

Rank A List Of Teams Based On Their Win/loss Record
I want to rank a list of teams based on their win/loss record. The list is something as follows.

Team A0-3
Team B0-2
Team C2-0
Team D1-2
Team E2-1
Team F2-1
Team G0-2
Team H3-0
Team I1-1
Team J1-1
Team K2-1

The column with the actual score contains the formula, =INDEX('2009'!\$C\$3:\$BL\$3,MATCH(\$A4,'2009'!\$B\$1:\$BJ\$1,0)). I would like to rank the list to something like,

Team H3-01
Team C2-02
Team E2-13
Team F2-13
Team K2-13
Team I1-16
Team J1-16
Team D1-27
Team B0-29
Team G0-210
Team A0-311

Calculate IRR & NPV
i got a problem to calculate IRR and NPV for my company cash flow. i so confuse how to calculate cos the initial investment (expense) is pay in installment by yearly basis. i hope anyone can help me to solve this problem.

I m not sure whether what i'm doing is correct or not. thanx

Sub Calculate
I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

How To Calculate Age
to calculate the age from the format date of birth shown below.

SQL Data  S1Date of Birth2Jun  9 1947 12:00AM3Jan  1 1957 12:00AM4Jan  1 1958 12:00AM5Jan  1 1956 12:00AM6Jun  4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4

Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.

The first column is the Total Payment and the second is Inducements.

Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.

Calculate How Many Of The Same Value In A Row?
If I have this serie of values in the A column:

34
33
33
33
30
29
26
26
20
19
17
17

And want this results in the B column:

1
3
3
3
1
1
2
2
1
1
2
2

Those numbers will indicate how many of the same are in a row.

What's the easiest way to accomplish this?

Calculate Net Pay After Tax
building a spreadsheet. I have the total price in cell f6. In cell C6 I need price with no taxes. D6 should be the pst of 8% and E6 should be the gst of 5%.

How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..

-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible

-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list

-- How can i make the result visible in kind of list ?

I would be nice if somebody knows a good solution..

Formula To Calculate Age
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

Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this

VBA Calculate Pause
Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

Calculate Attendance
I got struck in preparing absenteesm report from 22nd Sep 08 to 21st Oct 08. I've to put the dates on which a employee was absent ...

Calculate Totals
I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios.

We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.

I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.

Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list.

how can i calculate the variance?

i have the upper limit (=MIN(1,(mean+half width))
i have the lower limit (=MAX(0,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
i have the half width (t value * SQRT of Var/N)

i just don't know how to get the VAR/N

Function To Calculate IRR
My code below comes up with #VALUE! error msg

Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal

For i = 1 To NumPer
Values(i) = EachVal
Next

PIRR = Application.WorksheetFunction.IRR(Values())

End Function

Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell.
Ive tried using the 'count' and 'count if' but both return values of zero.

There are words in the cell, rather than numbers.

I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.

Calculate Button
I am trying to create a Radio button in a cell that calculates the entire workbook rather than pressing F9.

Calculate Age Down To Milliseconds

My husband sent me this program in Excel where you type in your birthdate and not only does it give you your age to the millisecond, but it also gives you a list of traits the person has. For example, I was born on November 20, 1987 and this popped up:

Age in years 19.61
Age in months 235
Age in days 7157
Age in hours 171760
Age in minutes 10305599
Age in seconds 618335921
Age in Milli seconds 61833592095
Age in weeks 50097
You born on Friday

Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.

Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 1-5 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 1-5 and a winner whenever you draw)

The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations.

To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities:

First - Second
1-1
1-2
1-3
1-4
1-5
1-W (1 winning combination)
2-1
2-2
2-3
2-4.................................

Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.

Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.

Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.

Add 2 Row After Each 9 Lines And Calculate A
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.

I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.

Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.

The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.

Formula To Calculate How Much Tax To Pay
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)

Formula - How To Calculate For The 35 Min At Say 15.24
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.

Calculate The Hours
How to calculate the Hours
Check the attached file

How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D.

=IF(C7

Calculate Retrospectively
I need to create a formula to work backwards from a closing balance to establish an inventory value on First In, First Out principles. The attached spreadsheet probably explains it better.

Calculate Age In Months
I need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.

This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.