Formula suggestion

[zaska]

Hello,

I am using the formula " =D6-SUM(B2:B4) " in the below instance to find out profit . I kindly request to suggest a good alternative to find out the net profit / net loss .


<table border="0" cellpadding="0" cellspacing="0" width="286"><col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:71pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="94">Particulars</td> <td class="xl67" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Debit</td> <td class="xl67" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Particulars2</td> <td class="xl68" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Credit</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Opening Stock</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">5000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">Sales</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">10000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">Purchases</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">8000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">Interest</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">5000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">Closing stock</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">3000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">Profit</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">5000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"> </td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">18000</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">18000</td> </tr> </tbody></table>

 

[zaska]

Can anyone help to find out the net profit

 

[tc88]

Can anyone help to find out the net profit

Does the formula in your first post not give you the correct answer? What are you expecting for your answer?

 

[zaska]

Sir,

The formula in my first post gave me the correct answer but i am looking to learn more advanced formula if any , which can used above to find the profit

 

[tc88]

Sir,

The formula in my first post gave me the correct answer but i am looking to learn more advanced formula if any , which can used above to find the profit

Although I am not nearly as proficient in Excel as many of the people on this board, I am experienced enough to tell you this--don't make things more complicated than they need to be. If that formula works and gives you what you're looking for, stick with it.

 

[zaska]

Sir,

Thank you for the kind suggestion , but i feel that there is nothing wrong in trying to learn new ways of accomplishing things.

I just tried one way but there may be experts who find it in a different way using arrays or sort of something like that.

Thank u

 

[zaska]

Hi,

Can anyone kindly suggest me any Advanced formula to find out the net profit in the above situation


Thank u

 

[Ruddles]

Ref your PM asking for additional input to this thread: I'm afraid the best I can do is agree 100% with tc88's comment that if your current formula works and gives the correct answer, you shouldn't be looking to make it any more complicated than it needs to be. In fact the elegance of any programming task is often how simple it can be made, not how complicated.

When you have something more complex to calculate than a simple nett profit figure, then you can start looking for more complex formulae and methods, but until then I strongly recommend that you embrace the KISS principle.

 

[Ruddles]

However here's a modified version of your formula which I think should return the same result:-

=ABS(MIN(D6^2)^0.5-MAX(ROUND(SUM(B2:B4),2)))*SIGN(MIN(D6^2)^0.5-MAX(ROUND(SUM(B2:B4),2)))

You might want to dissect this as a learning exercise. If you go Formula > Evaluate Formula, you can step through the intermediate values as they are calculated.

 

[zaska]

Sir,

I kindly thank you for your valuable inputs. I will try to dissect the formula given by you.


Have a nice day