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.
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.Everything looks great except if i change F16 in another type of board displays the price per piece, here is someting wrong i guess. And in all cases you can always multiple a size per 600 pieces. Sometimes i have 8 sizes for a customer and they want 600 pieces, that should be possible.
And the outcome of the CB1.4OFF or any kind of of ....OFF has to be calculated seperatly. It can't be mixed because the minimum is per thickness.
If you give me your adres i will send you flowers, i am so gratefull for your help! You helped so much!
By the way, how do i give a like?
Gr Ben