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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Lower​

[/TD]
[TD]

Higher​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

Count​

[/TD]
[TD]

Term​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

24​

[/TD]
[TD]

25​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD]

1​

[/TD]
[TD]

25
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

25
​

[/TD]
[TD]

50​

[/TD]
[TD]

30​

[/TD]
[TD]

20​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

40​

[/TD]
[TD]

3​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

51​

[/TD]
[TD]

100​

[/TD]
[TD]

250​

[/TD]
[TD]

200​

[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD]

80​

[/TD]
[TD]

2​

[/TD]
[TD]

200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

25​

[/TD]
[TD]

2​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.