0

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.

enter image description here

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?

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285

2 Answers2

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)

POC

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.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • Thank you @Forward Ed, this is quite helpful. I will go figure out now why the formula doesn't work for values below the first cutoff :) But this definitely helps –  Sep 26 '17 at 12:49
  • @pnuts, Thank you for that, I wanted automatability, was not sure if I would get that using IF-ELSE in the array formula coz i tried it in my original testing –  Sep 26 '17 at 12:50
0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • why does it need the CSE? wont it work without the CSE? – Forward Ed Sep 26 '17 at 00:30
  • 1
    when the quantity is less than the first cutoff, formula throws an error. – Forward Ed Sep 26 '17 at 01:02
  • 1
    Gotcha! It has been suggest 8) – Forward Ed Sep 26 '17 at 01:12
  • Thank you @pnuts, gave me necessary food for thought. Forward Ed, it would work without the CSE, my main intention was to create an automated table (with ability to add/delete rows when needed) that could do these calculations. As mentioned in my question, I did try {=IF(A4 –  Sep 26 '17 at 12:47