View original (French)

Percentage that does not exceed 100%

Solved
Katia -  
 Katia -
Hello,

I have a large spreadsheet with percentage calculations. Sometimes they are at 400% or 600%. How can I ensure they do not exceed 100%? If possible, without complicated formulas, maybe we can do it easily in the settings?

Thank you very much!
Katia

6 answers

  1. Anonymous user
     
    Hello,
    You could try this formula:
    =IF((E6/F6)>1,"",E6/F6)
    If the number in E6 is greater than the number in F6, the calculation will not be made. You can also put a note such as "not significant" or something else between the two quotes in the formula:
    =IF((E6/F6)>1,"not significant",E6/F6)
    Best regards,
    Arthur
    0
  2. rEVOLV3r Posted messages 223 Registration date   Status Member Last intervention   28
     
    Hello,

    Have you analyzed the reasons why the percentages exceed 100%?
    Hiding and manipulating certain data is never ideal.

    Have a nice day.
    0
    1. Anonymous user
       
      Indeed, if it is, for example, the number of voters compared to the number of registrants...
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      I'm not putting +1 so the post stays in its place, but yes.
      If you have unexpected 600%, either you have incorrect data or your formula is incorrect.
      You need to correct one and/or the other and not artificially change the result.
      eric

      Edit: unexpected in bold
      0
  3. Anonymous user
     
    More seriously, a result can exceed a forecast; we don’t know anything about the data; the question is purely mathematical.
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      a result can exceed a forecast
      That's why I specified 'unexpected'
      eric
      0
  4. Anonymous user
     
    I therefore withdraw my posts, deemed irrelevant.
    Sincerely,
    Arthur
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Yes, they were.
      Apart from the first one, which should only be applied if justified. Here, it is the requester who is the judge.
      0
  6. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    So, Katia? We're waiting for your explanations: what is it about?
    Is it about elections, a salary increase? About the variation of CO2 levels in the atmosphere?

    --
    Retirement is nice! Especially in the Antilles...
    Raymond (INSA, AFPA)
    0
    1. Katia
       
      Je ne sais pas si mes calculs sont bons, mais cela concerne le % d'évolution. Par exemple pour la vente d'unité d'une année par rapport à une autre. 36 unités par rapport à 265 ça me fait plus de 600% d'évolution. Et j'ai toujours pas réussi à ne pas dépasser 100%. J'ai déjà une formule =SIERREUR((D5-B5)/B5;""), je ne sais pas comment y ajouter la formule d'Arthur à donnée..
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      If you sold 36 in 2017 and 265 in 2018, that's a 636% increase (6 times more).
      A 50% increase means you sold half as much again,
      a 100% increase means you sold double, etc.
      Why change a number you don't understand to put something incorrect?
      eric
      0
  7. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Katia, I confirm what eriiic explained to you:
    An evolution can indeed be less than, equal to, or greater than 100%
    • if you sell 40 items in 2017 and 38 in 2018, the evolution is (38-40)/40=-5%: decrease
    • if you sell 40 items in 2017 and 40 in 2018, the evolution is (40-40)/40=0%: stability
    • if you sell 40 items in 2017 and 42 in 2018, the evolution is (42-40)/40=+5%: slight increase
    • if you sell 40 items in 2017 and 80 in 2018, the evolution is (80-40)/40=100%: doubling of the figure
    • if you sell 40 items in 2017 and 280 in 2018, the evolution is (280-40)/40=600%: explosion in sales

    Therefore, there is no reason to cap your percentage at 100%, as that would make no sense...

    --
    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    0
    1. Katia
       
      Sure, I'm going to leave my calculations as they are without capping the percentage, thank you all!
      0