View original (French)

Percentage that does not exceed 100%

Solved
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
    1. rEVOLV3r
      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 Contributor
        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
    2. 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 Contributor
        a result can exceed a forecast
        That's why I specified 'unexpected'
        eric
        0
    3. Anonymous user
      I therefore withdraw my posts, deemed irrelevant.
      Sincerely,
      Arthur
      0
      1. eriiic Contributor
        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
    5. Raymond PENTIER Contributor
      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 Contributor
        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
    6. Raymond PENTIER Contributor
      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