CODEWITHSUNDEEP
excel
dk.
dk.

Reputation: 2080

How do I compute an array of mins in Excel?

Lets say my data has a column of numbers in A. I want the sum of A unless the number in A is greater than some threshold. Conceptually I want SUM(MIN(A:A, 1000)), but this will return just the single smallest number.

In R there is pmin, which is a parallel minima, so I could write SUM(PMIN(A:A, 1000)) and each value in A is compared against 1000.

In Excel, I could create a new column B that was MIN(A1,1000) and drag it down. But I don't want to create a new column. I want to compute the sum in a single equation. Any solution?

Upvotes: 0

Views: 106

Answers (1)

tblznbits
tblznbits

Reputation: 6776

If you don't want to make a new column, you'll need to do it in two parts. The first part sums all of the values that are less than or equal to 1,000, while the second part adds 1,000 for each value that is over that value. It should be obvious that you would just replace 1,000 with whatever threshold you'd like to impose. Here is the code:

SUMIF(A:A, "<=1000") + 1000 * COUNTIF(A:A ">1000")

Upvotes: 1

Related Questions