Tiered Pricing for Product

%3CLINGO-SUB%20id%3D%22lingo-sub-1552293%22%20slang%3D%22en-US%22%3ETiered%20Pricing%20for%20Product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552293%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3Eso%20for%20my%20Software%20i%20charge%20prices%20per%20User.%20It%20is%20a%20tired%20pricing%20model.%3CBR%20%2F%3E%3CBR%20%2F%3E0-50%20User%20%3D%203%2C50%E2%82%AC%20per%20User%3CBR%20%2F%3E51-250%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D%203%2C00%E2%82%AC%26nbsp%3B%3CBR%20%2F%3E251-500%26nbsp%3B%20%26nbsp%3B%3D%202%2C50%E2%82%AC%3CBR%20%2F%3E501-1000%20%3D%202%2C00%E2%82%AC%3CBR%20%2F%3E1001-2000%20%3D%201%2C50%E2%82%AC%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20%3DSUMPRODUCT(--(F2%26gt%3B%7B0%2C50%2C250%2C500%2C1000%2C2000%7D)%2C--(F2-%7B0%2C50%2C250%2C500%2C1000%2C2000%7D)%2C%7B3.5%2C3.5%2C-0.5%2C-0.5%2C-0%2C5%2C-0%2C5%7D)%3CBR%20%2F%3EBut%20Excel%20doesnt%20recognize%20it%20as%20a%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1552293%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552414%22%20slang%3D%22en-US%22%3ERe%3A%20Tiered%20Pricing%20for%20Product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552414%22%20slang%3D%22en-US%22%3E%3CP%3EYour%20last%20array%20is%20not%20the%20same%20size%20as%20the%20others.%20It%20appears%20you%20have%20commas%20where%20you%20intended%20decimals%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20excel%20is%20treating%20this%20as%20eight%20elements%20versus%20six.%3CBR%20%2F%3E%7B3.5%2C3.5%2C-0.5%2C-0.5%2C-0%2C5%2C-0%2C5%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20is%20what%20you%20are%20intending%3F%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(--(F2%26gt%3B%7B0%2C50%2C250%2C500%2C1000%7D)%2C(F2-%7B0%2C50%2C250%2C500%2C1000%7D)%2C(%7B3.5%2C-0.5%2C-0.5%2C-0.5%2C-0.5%7D))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553769%22%20slang%3D%22en-US%22%3ERe%3A%20Tiered%20Pricing%20for%20Product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F743201%22%20target%3D%22_blank%22%3E%40grabofit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(F2%2C%20%7B0%2C51%2C251%2C501%2C1001%7D%2C%7B3.5%2C3%2C2.5%2C2%2C1.5%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553870%22%20slang%3D%22en-US%22%3ERe%3A%20Tiered%20Pricing%20for%20Product%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,
so for my Software i charge prices per User. It is a tired pricing model.

0-50 User = 3,50€ per User
51-250     = 3,00€ 
251-500   = 2,50€
501-1000 = 2,00€
1001-2000 = 1,50€

I tried =SUMPRODUCT(--(F2>{0,50,250,500,1000,2000}),--(F2-{0,50,250,500,1000,2000}),{3.5,3.5,-0.5,-0.5,-0,5,-0,5})
But Excel doesnt recognize it as a formula

3 Replies

Your last array is not the same size as the others. It appears you have commas where you intended decimals?

I think excel is treating this as eight elements versus six.
{3.5,3.5,-0.5,-0.5,-0,5,-0,5})

 

Maybe this is what you are intending?

=SUMPRODUCT(--(F2>{0,50,250,500,1000}),(F2-{0,50,250,500,1000}),({3.5,-0.5,-0.5,-0.5,-0.5}))

@grabofit 

That could be

=LOOKUP(F2, {0,51,251,501,1001},{3.5,3,2.5,2,1.5})