06-06-2019 06:56 AM
06-06-2019 06:56 AM
Although i have been using Excel every day, the time has come today to realise i know nothing about formulas :)
I'm struggling to make the attached calculator works as follows:
- i need the "sea freight" to get the value either from "weight" or "cubic", whichever is greater, but i need the cubics to be multiplied by 1000 in advance. Then, in order to calculate the cost, it should multiply it by 0.01
- if the weight is 1000 kg or under and the cubic is 1 or under, the minimum of $10 to apply
I have spent hours reading and searching but couldn't find a way to do that.
Thanks in advance for your time
09-12-2019 03:40 AM
Something went wrong with my calculator
I have been using =MAX(8/13,MAX(B25,B26*1000)*0.023)
But i have just noticed that even when B25 and B26 are blank, there is still a value in B29
Can anyone help me please
09-16-2019 02:25 AM
In the attached examples, the Sea Freight should be:
8.13 x 3.8 = 30.894
28.45 x 3.2 = 91.04
9.75 x 3.6 = 35.1
It should take either the weight or the cubes multiplied by 1000, whichever is greater.
No idea where these numbers came from...
09-16-2019 03:25 AM
Let start from the scratch. First example
Weight - 2800
Cubics - 3.2*1000 = 3200
Max from above 3200. Sea Freight is $10 w/m, exchange rate is 1.23 GBP/USD - I took that from your first screenshort. Thus 3200*10*1.23 = 39360 GBP. After that you multiply on some magic coefficient, perhaps we shall divide on 1000.
But how 8.13 x 3.18 appears for that sample?
09-16-2019 04:02 AM
Yes, i guess we could devide the weight by 1000, instead of multiplying the cubes by 1000.
The idea is that the calculator should consider 1 cbm = 1000 kg
Regarding the first example, i'm after the following result:
USD 10 / 1.23 = GBP 8.13
Weight 3200 / 1000 = 2.8
So 8.13 x 3.8 = 30.894
If weight is under 1000 AND cubics are under 1.0, then a minimum of GBP 8.13 should be applicable
It was working perfectly untill i decided to mess it up by getting a "0" value in B29, when B25 and B26 are empty...
Again, thanks for your help Sergei and apologies if i haven't make all clear since the beginning
09-16-2019 04:40 AM
Milena, when I'd suggest to modify a bit your sheet.
First, remove Merging cells for the exchange rate, instead select A2:D2, Ctrl+1 and Center Across Selection.
In A2 just type the number with current exchange rate (1.23), Ctrl+1 and apply custom format as here
With this we may take exchange rate to formula from this cell.
The same for B1 and D1, let keep here only number formatting them with text through custom format. For B2
With this formula in B29 will be
Exchange rate and min Sea Freight we take from the cells and not hardcode them within the formula.
Please check in attached file.