Forum Discussion
Help Formula outcome of two cells in combination with dropdown is value over more columns
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.
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.
You're the best! The formula is abacadabra for me haha!
So far I checked 10-12 calculations and they all are correct!
Only the BE... not but thats oke, i will create a different table for this formula and take out the 2 BE types from the dropdown. It's a simple formula that should be i a different table i guess.
Absolutly amazing! Thank you soo much!
Maybe i have one more challenge for you in this sheet!
I didn't mention that i have to calculate the total square meters for the .../OFF cardboard.
The minimum is 600 pieces and every next step should be with 600 pieces so 600-1200-1800 etc.
The outcome of for example 70x70=0,0049=2,94 m2.
The minimum is 100 m2 and every size within the same cardboard are combined and so the outcome has to be tweeked with the customer to adjust the quantity to the minimum of what the want see J100:N113 information.
So what i need is a extra column between F&G that says quantity where i can fill in the preferred quantities of that size to tweek the different sizes for example CB1.4OFF for a total of 100m2 or 200m2 and so on... And below that column the total of that type of cardboard based on what i filled in there.
&
The price outcome of that chosen quantity of that particular size and type cardboard should be the price from the column before the next column which is a better price .... for example if it's 1800 pieces it's the price of 1200 pieces and 3000 pieces should be the price of 2400 pieces.
Do you understand this?
And do you think this is possible to make?
Nevertheless if it's not you're help is very much appreciated.
I can't thank you enough!
Gr Ben
Hi Ben, in this example file you can now enter only multiples of 600 in cells G16:G26. If the sum of m² (G27) has already reached 200 m², you can only enter multiples of 1200. If entries that were made before no longer correspond to the specifications (multiples of 1200), the cell in question will be marked red. In cell G27 the m² are added up, which come from either CY = CB1.4OFF or CB1.8OFF.
In wm60 I have the price table for BE.... prepared. I speculated wildly that they would look the same as the others. If you need more space, you can just add rows. It won't interfere with the formulas. You then only have to correct the entries there at D331:E350.
Your default, that always the smaller unit price is used, was already implemented before. You get the better purchase price only if you exceed the quantities.
I hope that was so in your sense. If so, you can leave me a like.