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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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
@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
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.

Improve this answer

4 Comments

why does it need the CSE? wont it work without the CSE?
when the quantity is less than the first cutoff, formula throws an error.
Gotcha! It has been suggest 8)
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<Cutoff,A4*PPVM)}, instead of a nested if-else, or IFS or IFERROR, but I am not sure if i can get the needed automatability with those.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.