 # excel pricelist

Occasional Contributor

# excel pricelist

I have made a calculation sheet with 7 variables. (length, hight, with, material, coating yes/no, etc.

so i can calculate the price of a product.

now i need a list of 50 products with all their own variables. listed in a tabel with the calculated price at the end.

how can i let excel calculate these 50 products without me having to enter al the variable seperatly for every product???

10 Replies

# Re: excel pricelist

I have made a calculation sheet with 7 variables. (length, hight, with, material, coating yes/no, etc. so i can calculate the price of a product.

now i need a list of 50 products with all their own variables.

listed in a tabel with the calculated price at the end.

how can i let excel calculate these 50 products without me having to enter al the variable seperatly for every product???

You have 50 products with their specifications listed in a table and created an equation based on the product specifications which are as follows: (length, height, width, material, coating yes/no, etc.), in the products table.

Your question: How would you convert your equation into an excel formula?

is that an accurate comprehension of your narrative and question?

correct.

# Re: excel pricelist

You'll have to share the equation and the product table if you need help in converting

# Re: excel pricelist

how can i share it?

# Re: excel pricelist

when you click reply this will come up: click open full text editor, then this will come up: click browse to attach workbook

# Re: excel pricelist

@Yea_So first sheet is the calculation, second sheet is the tabel with first 10-15 products.

# Re: excel pricelist

You're on the right track except.

you based your dim/calculation tables based on input values of one product.

to work around the above, establish a range of valid values, and use the lowest value in the established range of valid values for example coating has two valid values, its either a zero or a one that's an easy rule to make in data validation since it is just a true or false value, however the other calculation input ranges might be more than that so what are the valid range values for those: also I noticed:

=(\$C82*4+\$D82*2+\$G82*\$B\$72/1000+\$I82*\$B\$74*\$B\$72/1000+\$K\$82*\$B\$75)*\$B\$76

reminder: precedence rule multiplicatiion/division calculates first before addition/subtraction

are you sure about the above equation?

# Re: excel pricelist

ok, thank you for your help.
and i am sure of the equation.

# Re: excel pricelist

@Ron_Beudeker So, if you need the amounts in column J of the "list" sheet to be filled with the outcome of the "Calculatie" sheet, cell B17, you can make use of INDEX and MATCH. Combine that with a few named ranges and you can easily fill the table as demonstrated in the attached file. I didn't dare to touch all the other calculations below row 70 in the first sheet. I trust you know what you are doing, though it seems overly complicated.

# Re: excel pricelist

I have a question:

Why on RVS316 column Vlakke lasflens are dependent on the calculation to the far right

while on staal column Vlakke lasflens are not dependent on the calculation to the right? Is this intentional or is it  an unintentionally omission?  cheers