Complex Pricing Formila

[Roar]

at least for me, probably not for most of you.

Pricing of widgets will be volume based
1-24 $5
24-50 - $10
51-100 - $150

But not only that it will be based on term of commitment so one year pricing would be

1-24 if 1 year = $25
1-24 if 2 year = $10
1-24 if 3 year = $5

Can you give me some pointers of how to start a formula for this?

I am thinking something like (not fancy)
If count <25, and term = 1, then $25

Does that sound like I am on the right track?

 

[Marcelo Branco]

See if this does what you need

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Lower​	Higher​	1​	2​	3​		Count​	Term​	Result​
2​	1​	24​	25​	10​	5​		20​	1​	25 ​
3​	25 ​	50​	30​	20​	10​		40​	3​	10​
4​	51​	100​	250​	200​	150​		80​	2​	200​
5​							25​	2​	20​
6​

<tbody>
</tbody>

Try to create a table like the one in columns A:E (years in C1:E1) - adjust the prices in C2:E4

Criteria (count and term) in columns G:H

Formula in I2 copied down
=INDEX(C$2:E$4,MATCH(G2,A$2:A$4),MATCH(H2,C$1:E$1,0))

Hope this helps

M.

 

[Roar]

Muito obrigado!!!!!

This will work for two situations I have. I thank you so much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.