cap of amounts

Hi,

If you mean you want to know the sum of the numbers above £1500 then

Formula:

Please Login or Register  to view this content.

If you mean something else then upload an example and tell us what result you expect.

Thanks!

What I was trying to get to was:
- in column B the c30,000 rows of sales amounts with a range of £20-10,000
- in cell A1 an entry of £1,500 (or another amount for modeling/sensitivity scenarios)
- In column C the sales amount: actual if <£1,500; or capped/expressed as £1,500 (or whatever value in A1

Thx

Are you saying that you just want to SEE the values in column B that are less than the value in A1?

If so why not just Autofilter column B.

If you want to extract the subset <A1 then use an Advanced Data filter.

If I understand correctly the following formula will populate column C with the values you wish:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Perhaps this will do
Data Range

	A	B	C	D
1	1500
2		Original	Capped Amount
3		715	715	=MIN($A$1,B3)
4		4047	1500
5		1004	1004
6		2146	1500
7		7169	1500
8		7377	1500
9		9605	1500
10		9973	1500
11		4860	1500
12		863	863
13		3596	1500
14		3469	1500
15		245	245
16		5654	1500
17		439	439
18		2108	1500

You're Welcome and thank you for the feedback. I would be remiss if I didn't point out that newdoverman's MIN function yields the same values and probably uses less time/computing power. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

@JeteMc
Thank you for the mention.