CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
tjcss
tjcss

Reputation: 860

Create rows based on array

I have a spreadsheet document that I want to make as efficient as possible. It is used to calculate the quantity-based discounts of a product. The more you buy, the cheaper the unit is.

https://docs.google.com/spreadsheets/d/1JxrvA3WPANQeIXqLt-7NFFgyxxKbqr_4rW_Q5ptAvKA/edit?usp=sharing

In the first red cell, call "tiers" I will input the quantities at which the discounts will be applied.

I want to use this array in the table directly underneath, one item per row until we run out of numbers.

I will then enter the percentage values in the right hand side and that will then populate the rest of the fields.

So all I will need to do is enter the cost price, the tiers, and the percentages and this calculator does the rest.

My question is, how do I create new rows based on this array in the correct place?

Upvotes: 1

Views: 89

Answers (1)

player0
player0

Reputation: 1

paste in B10 cell and drag down:

=B$9-B$9*C10

paste in B15 cell:

=ARRAYFORMULA(IFERROR(A15:A*VLOOKUP(A15:A, A9:B12, 2)))

enter image description here

spreadsheet demo

Upvotes: 1

Related Questions