• 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.

Percentage calculation issue

T

Thomas Kuriakose

Active Member
Respected Sir,

I am not able to understand how to get 15% in G11 of the attached sheet. The column G is the total of values from column B to F.

The percentage profit for all units is 15% and hence the for the total should also be 15%.

Kindly let me know where I am going wrong for this.

Thank you very much,

with regards,
thomas
 

Attachments

  • Percentage Issue.xlsx
    10.1 KB · Views: 7
sorry, it is simple maths. The % is right. Why do you say it must be 15% (all other 15% are hard coded values).
If you use the same formula in those cells, you'd get
17,65% 17,65% 17,65% 17,65% 17,65%
I think the 15% is just incorrect.
 
Respected Sir,

Thank you very much for this information and your support.

For the unit (1 to 5) cells, the percentage is entered by the user to alter PNC and NPT values to arrive at the desired profit.

In this example since all the percentage profit is 15%, the requirement is to get 15% on the total also.

Kindly let me know how to arrive at this.

Thank you very much,

with regards,
thomas
 
I find this rather strange logic. If the other "15%" fields are hard coded, you might as well just enter 15% in the last cell for the grand total as well.
From a spreadsheet design point of view this is just bad practise. I'm sorry to say this. In my book PNC and NPT should be simply calculated values (and not manipulated).
But I believe I spot a design error. The profit % is calculated on the manual entry of 15%. In the total you have made a sum. You may not do that: it is inconsistent. You must apply the same formula in the cell G10 as in G9. Where the 15% is hard coded.
In formulas, when this value is fixed, the best practise is this use the 15% inside the formula. Like this: G8/(85%)-G8 (because you use 100%-15%).
 
Respected Sir,

Thank you for this insight.

I will try to use this, but we have other scenarios where each unit could have a different percentage and the column total will not give the correct percentage.

Thank you once again,

with regards,
thomas
 
Respected Sirs,

This is perfect.

Thank you so much for this solution. I struggled to get this result.

Much much appreciated,

with regards,
thomas
 
You must log in or register to reply here.
Back
Top