Jan 15 2022 12:03 AM
quantity of books to be ordered | ||||||
specs | 1000 | 2000 | 3000 | 5000 | ||
Internal papers type | ||||||
paper 170 grams 4 colors | $22.00 | $20.50 | $19.00 | $17.00 | ||
paper 135 grams 4 colors | $20.00 | $18.00 | $17.25 | $16.15 | ||
Paper 90 grams 4 colors | $18.00 | $17.00 | $15.95 | $15.25 | ||
Paper 80 grams 1 color | $14.00 | $13.50 | $12.25 | $11.75 | ||
Book's cover type | ||||||
Hard Cover Br. 2000 grams with sulfan | $55.00 | $50.00 | $46.00 | $43.00 | ||
Normal Cover Br. 300 grams with sulfan | $33.00 | $31.00 | $29.50 | $28.00 | ||
The function is = Type of internal papers cost wit refernce to number of printed books + Type of the book"s cover chossed | ||||||
Jan 15 2022 12:53 AM
Jan 15 2022 02:56 AM
Thank you for your hint Robbins @Doug_Robbins_Word_MVP
The question is how can make a function or a scenario so I let the user that help him choose the number of pages, type of cover, and the quantity of the printed books as those are the major factors to evaluate the price of the printed books, note that the data are applicable to expand to several tens of items.
Jan 15 2022 04:05 AM
The way I would go about it would be to create a userform containing either radio buttons or comboboxes for each of the variables:
Number of pages
Paper type
Cover type
and then, depending upon the selections made, compute the cost based on those selections
Jan 15 2022 07:08 AM
SolutionFor 2-way lookups reverting to INDEX/MATCH is often simplest.
The alternative formula takes advantage of Excel 365 to create a more verbose formula
= LET(
qtyLevel, XMATCH(qtyOrdered,volume,1),
paperOption, XMATCH(paperOrdered,paperType),
coverOption, XMATCH(coverOrdered,coverType),
cost, INDEX(paperCost, paperOption, qtyLevel)
+ INDEX(coverCost, coverOption, qtyLevel),
IFERROR(cost, "")
)
Jan 15 2022 07:08 AM
SolutionFor 2-way lookups reverting to INDEX/MATCH is often simplest.
The alternative formula takes advantage of Excel 365 to create a more verbose formula
= LET(
qtyLevel, XMATCH(qtyOrdered,volume,1),
paperOption, XMATCH(paperOrdered,paperType),
coverOption, XMATCH(coverOrdered,coverType),
cost, INDEX(paperCost, paperOption, qtyLevel)
+ INDEX(coverCost, coverOption, qtyLevel),
IFERROR(cost, "")
)