• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help for calculating Profit by using formula [SOLVED]

ThrottleWorks

ThrottleWorks

Excel Ninja
Sir, I have 4 columns.


Product id, quantity, date, amount.

This is a data about purchase & sell.


I want to calculate profit/loss on the base of this data.

But there are multiple conditions before getting the result.


For example, I have product Id abc123, I bought 100 @ 10 on 3rd may 2013.

I sold it @ 12 on 25th may 2013. So my profit will be 200.


I have amount column which will give me figure as 1000 for purchase & -1200 for sell.

So I will calculate P&L on the base of this.


I will explain the problem in below examples.

For the same product id abc123, , I bought 100 @ 10 on 15th April 2013.

I sold it @ 12 on 25th may 2013.


In this case I will not calculate profit & loss cause the purchase & sell was not done in the same month.

I will calculate P&L only if transaction is complete in that particular month only.


Another condition is


For the same product id abc123, , I bought 500 @ 10 on 5th May 2013.

I sold 400 @ 12 on 25th may 2013.


But since I have not sold entire product, I will not calculate the profit.

Can anyone help me in this please.
 
Hi Sachin ,


Are these the only two rules ?


1. The sale quantity should equal the purchase quantity


2. The sale month and year should be the same as the purchase month and year.


Is this correct ?


Can you copy + paste a few rows of data ?


Narayan
 
Yes Sir, the sale quantity should match purchase quantity.


Yes Sir, your point no 2 is correct.


so in any month every product will have opening qty as 0 and both sale & purchase will be same. Then only i will calculate P&L.


Product Id Qty Date amount

abc12 100 1/5/2013 1000

abc12 -100 5/5/2013 1200


so the profit in this case is 200


Product Id Qty Date amount

abc12 200 1/5/2013 1000

abc12 -100 5/5/2013 1200


we will not calculate profit


Product Id Qty Date amount

abc12 100 1/4/2013 1000

abc12 -100 5/5/2013 1200


we will not calculate profit


Sir thanks a lot for the help & your valuable time.
 
Sir, I think I am able to solve it now.

I am using =month, = year, concatenate, if, sumifs to get the total.


I am verifying the results, I will request for your help if I get stuck again or if I am wrong.


Thanks a lot for the help & your valuable time.


Have a nice day.
 
Hi Sachin ,


Will the data be sorted in any order , or will the product IDs be in random order , both with respect to IDs and dates ?


It would be nice if you could copy + paste around 10 rows of data , since otherwise what ever testing I do will be with what I think is correct data.


Narayan
 
Sir the data will be sorted in date order.

But product IDs can be in any order.


Sir I am not able to paste here, I tried may be my mistake in pasting.


Thanks a lot for the help & your valuable time.
 
Sir, thanks a lot for the help.


I will definately download this file at home.

We do have access to this link here.


Once again thanks a lot for the help & your vaulable time.


Have a nice day ahead Sir.
 
Sir, I have downloaded the file.


How much effort you have taken for my problem !


I am studying the formula, thanks for the help.


Good night Sir.
 
Sir,


I am trying the formula you have given.


I forgot to tell you that in my case numbers in column "Total Price" (column D in your help file) are in both positive and negetive sigs.


In the help file numbers are in "Total Price" are in positive only.


I tried changing the formula, but could not do it.


Could you please help me on this if you get time.


The current formula from the help file


"=IF(SUM(IF($A$2:$A$15=A2,IF(TEXT($B$2:$B$15,"yyyymm")=TEXT(B2,"yyyymm"),1,0)))<>2,"",IF(SUM(($A$2:$A$15=A2)*(TEXT($B$2:$B$15,"yyyymm")=TEXT(B2,"yyyymm"))*($C$2:$C$15))<>0,"",IF(SIGN(C2)=1,"",-SUM((($A$2:$A$15=A2)*(TEXT($B$2:$B$15,"yyyymm")=TEXT(B2,"yyyymm"))*((SIGN($C$2:$C$15)*($D$2:$D$15))))))))"
 
Hi Sachin ,


The best thing to do would be to send me a sample file at my email ID :


narayank1026 [at] gmail [dot] com.


Narayan
 
Yes Sir, I will do it (do not have access to mail here, will be able to send once I reach home).


Thanks a lot for the help, have a nice day.
 
Sir, I have just mailed you the file.


Sorry for late reply, was facing problem while accessing net at home.


Have a nice weekend Sir.
 
Good morning Sir, this is working now, thanks a lot for the help & your time.


Sorry for late reply, I was facing some problem with my net at the home.


Have a nice day.
 
You must log in or register to reply here.
Back
Top