excel pricelist

%3CLINGO-SUB%20id%3D%22lingo-sub-2755597%22%20slang%3D%22en-US%22%3Eexcel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2755597%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20made%20a%20calculation%20sheet%20with%207%20variables.%20(length%2C%20hight%2C%20with%2C%20material%2C%20coating%20yes%2Fno%2C%20etc.%3C%2FP%3E%3CP%3Eso%20i%20can%20calculate%20the%20price%20of%20a%20product.%3C%2FP%3E%3CP%3Enow%20i%20need%20a%20list%20of%2050%20products%20with%20all%20their%20own%20variables.%20listed%20in%20a%20tabel%20with%20the%20calculated%20price%20at%20the%20end.%3C%2FP%3E%3CP%3Ehow%20can%20i%20let%20excel%20calculate%20these%2050%20products%20without%20me%20having%20to%20enter%20al%20the%20variable%20seperatly%20for%20every%20product%3F%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2755597%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759010%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853540%22%20target%3D%22_blank%22%3E%40Ron_Beudeker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20narrative%20is%20ambiguous.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20a%20calculation%20sheet%20with%207%20variables.%20(length%2C%20hight%2C%20with%2C%20material%2C%20coating%20yes%2Fno%2C%20etc.%26nbsp%3Bso%20i%20can%20calculate%20the%20price%20of%20a%20product.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enow%20i%20need%20a%20list%20of%2050%20products%20with%20all%20their%20own%20variables.%3C%2FP%3E%3CP%3Elisted%20in%20a%20tabel%20with%20the%20calculated%20price%20at%20the%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20can%20i%20let%20excel%20calculate%20these%2050%20products%20without%20me%20having%20to%20enter%20al%20the%20variable%20seperatly%20for%20every%20product%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3EMy%20understanding%20of%20Your%20narrative%3A%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3EYou%20have%2050%20products%20with%20their%20specifications%20listed%20in%20a%20table%20and%20created%20an%20equation%20based%20on%20the%20product%20specifications%20which%20are%20as%20follows%3A%26nbsp%3B(length%2C%20height%2C%20width%2C%20material%2C%20coating%20yes%2Fno%2C%20etc.)%2C%26nbsp%3Bin%20the%20products%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3EYour%20question%3A%3C%2FSTRONG%3E%3C%2FFONT%3E%20How%20would%20you%20convert%20your%20equation%20into%20an%20excel%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20that%20an%20accurate%20comprehension%20of%20your%20narrative%20and%20question%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759144%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759144%22%20slang%3D%22en-US%22%3Ecorrect.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2759148%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2759148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853540%22%20target%3D%22_blank%22%3E%40Ron_Beudeker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20have%20to%20share%20the%20equation%20and%20the%20product%20table%20if%20you%20need%20help%20in%20converting%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2760313%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760313%22%20slang%3D%22en-US%22%3Ehow%20can%20i%20share%20it%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2760338%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853540%22%20target%3D%22_blank%22%3E%40Ron_Beudeker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20you%20click%20reply%20this%20will%20come%20up%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1631883021335.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311050i8088FEBEC1F084CF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1631883021335.png%22%20alt%3D%22Yea_So_0-1631883021335.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eclick%20open%20full%20text%20editor%2C%20then%20this%20will%20come%20up%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1631883301134.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311051iE5049918A6A7CF46%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1631883301134.png%22%20alt%3D%22Yea_So_1-1631883301134.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eclick%20browse%20to%20attach%20workbook%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2766338%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3Bfirst%20sheet%20is%20the%20calculation%2C%20second%20sheet%20is%20the%20tabel%20with%20first%2010-15%20products.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2768864%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853540%22%20target%3D%22_blank%22%3E%40Ron_Beudeker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20on%20the%20right%20track%20except.%3C%2FP%3E%3CP%3Eyou%20based%20your%20dim%2Fcalculation%20tables%20based%20on%20input%20values%20of%20one%20product.%3C%2FP%3E%3CP%3Eto%20work%20around%20the%20above%2C%20establish%20a%20range%20of%20valid%20values%2C%20and%20use%20the%20lowest%20value%20in%20the%20established%20range%20of%20valid%20values%20for%20example%20coating%20has%20two%20valid%20values%2C%20its%20either%20a%20zero%20or%20a%20one%20that's%20an%20easy%20rule%20to%20make%20in%20data%20validation%20since%20it%20is%20just%20a%20true%20or%20false%20value%2C%20however%20the%20other%20calculation%20input%20ranges%20might%20be%20more%20than%20that%20so%20what%20are%20the%20valid%20range%20values%20for%20those%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1632175484255.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311672i9EA7220C7CE5E3A2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1632175484255.png%22%20alt%3D%22Yea_So_0-1632175484255.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ealso%20I%20noticed%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(%24C82*4%2B%24D82*2%2B%24G82*%24B%2472%2F1000%2B%24I82*%24B%2474*%24B%2472%2F1000%2B%24K%2482*%24B%2475)*%24B%2476%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ereminder%3A%20precedence%20rule%20multiplicatiion%2Fdivision%20calculates%20first%20before%20addition%2Fsubtraction%3C%2FP%3E%3CP%3Eare%20you%20sure%20about%20the%20above%20equation%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769601%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20pricelist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769601%22%20slang%3D%22en-US%22%3Eok%2C%20thank%20you%20for%20your%20help.%3CBR%20%2F%3Eand%20i%20am%20sure%20of%20the%20equation.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Ron_Beudeker 

Your narrative is ambiguous.

 

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???

 

My understanding of Your narrative:

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.

@Ron_Beudeker 

 

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

how can i share it?

@Ron_Beudeker 

 

when you click reply this will come up:

Yea_So_0-1631883021335.png

click open full text editor, then this will come up:

Yea_So_1-1631883301134.png

click browse to attach workbook

 

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

@Ron_Beudeker 

 

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:

Yea_So_0-1632175484255.png

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?

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

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

Screenshot 2021-09-25 at 14.41.01.png

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.

@Ron_Beudeker 

 

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?

Yea_So_1-1633392215646.png

 

 

Yea_So_0-1633391810367.png

 

cheers