Calculate price based on amount with different price on different amounts?

[behedwin]

I have a sheet with column NAME and AMOUNT

In AMOUNT i define how many users a customer have.
So it could be like this:

NAME	AMOUNT
Customer A	10000
Customer B	3000

I want to calculate a customers price. And our price model is like this:
For the first 500 users, users pay 1 dollar.
For user 501-3000 users pay 0.75 dollar.
For user 3000-7000 users pay 0.5 dollar
For users 7000 and above users pay 0.25 dollar.

How can i caluclate a users price based on this set of rules?

I am interessted in either splitting the AMOUNT into these sections, so automatically take the first 500 and put in one column and so on.
Or just have a formula that can calculate everything, is this possible?

 

[GuzmanServices]

Assuming your data starts on Cell A1, the PRICE Column header would be in C1 and the C2 would have the following formula:

=IF(B2>7000,500*1+2500*0.75+4000*0.5+(B2-7000)*0.25,IF(B2>3000,500*1+2500*0.75+(B2-3000)*0.5,IF(B2>500,500*1+(B2-500)*0.75,B2*1)))

Results were calculated as follows:

Re

 

[Dave Patton]

With older versions, use the SumProduct formula.
With the named arrays, the table is not required.

Use Name Manager and create new names
array of bracket information New aB Value {0;500;3000;7000}
array of rate differential aR Value {1;-0.25;-0.25;-0.25}

Commissions 2023.xlsm
	A	B	C	D	E
1		Amount			Older versions
2		10,000	5,125.00	5,125.00	5,125.00
3		3,000	2,375.00	2,375.00	2,375.00
4
5	Bracket	Rate
6
7	0	1
8	500	0.75
9	3,000	0.5
10	7,000	0.25
2c

Cell Formulas
Range	Formula
C2:C3	C2	=SUM((B2>$A$7:$A$10)*(B2-$A$7:$A$10)*($B$7:$B$10-$B$6:$B$9))
D2:D3	D2	=SUM((B2>aB)*(B2-aB)*aR)
E2:E3	E2	=SUMPRODUCT(--(B2>aB),(B2-aB),aR)