Forum Discussion
Solved
Help Formula outcome of two cells in combination with dropdown is value over more columns
Hello everyone,
I am new here and from the netherlands and my english is not that great so don't be hard on me haha.
I am struggling with something I want but don't know how to do it.
Here's the problem:
A16 (50 mm) * C16 (50 mm) = E16 (0,0025) m2/pc (=SUM(A16*C16)/1000000).
This outcome has 6 different outcomes depending on the color refering in F16.
Depending on what is chosen in F16 I have a pricelist in an other tab "wm60" where I have to look up
which price is similar and otherwise i have to take the price in the column below.
| width in mm | length in mm | m2/ pc | 600 | 1200 | 2400 | 4800 | 9600 |
| 50 | 50 | 0,0025 | |||||
| 65 | 65 | 0,0042 | |||||
| 70 | 70 | 0,0049 |
So if the outcome is 0,0030 for example i have to take the price from 65*65=0,0042
And to make it even more difficult i want the outcome of the criteria above to display the prices in
G15 (600) H16 (1200) I16 (2400) J16 (4800) and K16 (9600)
Easy Peasy not?
Who can help me?
Gr Ben
Goedendag.
I have revised your sheet and inserted formulas in the area ='Calculator'!G16:G26. These first look in an auxiliary area ('wm60'!C$235:E$255) that I created where the price data is. Then it is determined which line in the respective price table comes into play. By comparing the table headings and the quantities in the area ='Calculator'!G15:K15, the correct quantity price is then extracted for each quantity area and multiplied by the quantities.
Unfortunately, exactly for the example you chose, the price table was not available. Therefore, I am not quite sure whether I have done everything correctly. So please check again very carefully and critically.
=IFERROR(LET( aerea,FILTER('wm60'!C$235:E$255,'wm60'!A$235:A$255=F16), price_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,2),,1)), row_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,3),,1)), header,INDEX(price_aerea,1,0), ro_w,XLOOKUP(E16,row_aerea,price_aerea,,1), IFNA(ROUND(XLOOKUP(G$15:K$15,header,ro_w,,-1)*G$15:K$15,2),"x")),"")I hope the calculation is correct and the table helps you now. If you need more help, please let me know.
10 Replies
Hi, I don't think I understood all of your problem. Nevertheless I tried to make an example for you. The price list in wm60 is trimmed for XLOOKUP with FILTER to your color code. If you don't search for an exact match in XLOOKUP (parameter 5 = 0) but "Exact match. If none found, return the next larger item." (parameter 5 = 1) then you can pull the desired price from the price list.
Look at the example and try to solve your problem with the inspiration. Otherwise answer again and I will try to help.
Thank you so much for your help, super!
I don't understand the formula so if you don't mind i wanna send you the original.
Hope this clarefy it for you and solves my problems
You see in "list" how i have manually calculate the BE... cardboard, maybe it's easy for you to implement this in the calculator?
P.S. the formula is in dutch
Hope you can help.
Nevertheless I am grateful for your help!
Goedendag.
I have revised your sheet and inserted formulas in the area ='Calculator'!G16:G26. These first look in an auxiliary area ('wm60'!C$235:E$255) that I created where the price data is. Then it is determined which line in the respective price table comes into play. By comparing the table headings and the quantities in the area ='Calculator'!G15:K15, the correct quantity price is then extracted for each quantity area and multiplied by the quantities.
Unfortunately, exactly for the example you chose, the price table was not available. Therefore, I am not quite sure whether I have done everything correctly. So please check again very carefully and critically.
=IFERROR(LET( aerea,FILTER('wm60'!C$235:E$255,'wm60'!A$235:A$255=F16), price_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,2),,1)), row_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,3),,1)), header,INDEX(price_aerea,1,0), ro_w,XLOOKUP(E16,row_aerea,price_aerea,,1), IFNA(ROUND(XLOOKUP(G$15:K$15,header,ro_w,,-1)*G$15:K$15,2),"x")),"")I hope the calculation is correct and the table helps you now. If you need more help, please let me know.
