Andy T
Lv 4
Andy T asked in Computers & InternetSoftware · 6 years ago

Is there a way in Excel to not count value past a certain number, when adding (etc)?

Say I want to add various sets of numbers, but don't want the totals (where I'm looking for the largest total) to be influenced too much by an outlier.

If I didn't care if any given value was over 100 (that it was "overkill"), would there be a way to add them up, and only count every number 100-or-more as just 100? (even if I did the adding separately, just an equation to "change" any 100+ to just 100)

2 Answers

Relevance
  • Jim
    Lv 7
    6 years ago

    if you add another column you could do this with an if then else to assign a value.

  • 6 years ago

    If your numbers are in A1:A100.....

    =SUMIF(A1:A100,"<101")

    +COUNTIF(A1:A100,">100")*100

    I have had to split it in two so that Yahoo will accept it, but you should join it:

    =SUMIF(....)+COUNTIF(....)*100

Still have questions? Get your answers by asking now.