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.
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
The error in line 16 is corrected. Sorry happened while experimenting. The minimum order value was per size? I understood that correctly. Otherwise I didn't understand the note "Sometimes i have 8 sizes for a customer and they want 600 pieces, that should be possible." not understood.
The Likes have arrived 🙂
The minimum order is 100m2 or 200m2 or 300m2 or 400m2 depending on the colors they want printed
(WM60_J100:N113) per thickness (CB1.4OFF/CB1.8OFF) they can't be mixed together and should be seperatly calculated. G27 calculates all quantities together in stead of seperatly.
They can get better prices according to D100:H100 for CB1.4OFF (600-1200-2400-4800-9600)
If they choose 1800 pieces ( because this is possible because it's in steps of 600 pieces as always) the price should be from 1200 pieces because the next better price is 2400 pieces and there are no prices for in the table, only 600-1200-2400 etc etc.
(For CB1.8OFF it's the same as above but the prices are below D147:H147)
And column I16:I26 with header 1800 should be 1200 like in wm60 columns.
Here something goes wrong but i don't know what. It's not the price of 1200 pieces?
Sorry I am troubling you but you are so close of nailing it, hope you don't mind
Gr Ben