Array formula basics instead of nested if-else

问题:

I have scoured the site for array formulas but am troubled by some basic issue of an array formula that i am not able to figure out.

the following table lists prices and various cutoffs for volume discounts for a product. IMO a formula like, {=IF (A2'<'Cutoff,A2*PPVM)}

in which cutoff refers to all the values and "price" refers to the price points should do the trick. I do not want to do IFS or IF-ELSE because I might want to add a row/delete a row later.

Essentially, if i have 5000 products, the price would be 5000*22.5, and so on and so forth. Can anyone suggest the basic structure of an array formula that can be used for this?

回答1:

Simmilar to PNUT's answer but yet different. I worked on the assumption that a value at the cut off received the value of the cutoff. ie 250 get 30 for a price. The other thing I assumed was that quantities had to be integers. IE you cannot sell half a nail.

I used the following formula in F3

=INDEX(B:B,IFERROR(MATCH(D3-1,A:A,1),1)+1)*D3

I used the folllowing formula in E3 just to verify what price was being pulled from the table:

=INDEX(B:B,IFERROR(MATCH(D3-1,A:A,1),1)+1)

The kicker is if you have quantities greater than 22000 or the last number of your table, it will give you 0 for the price and therefore cost.

回答2:

Assuming the quantity in B1 and Cutoff etc in A2... you could use:

=INDEX(B:B,MATCH(B1,A:A)+1)*B1

entered with Ctrl+Shift+Enter.