Jun 06 2019 06:56 AM
Hi everybody!
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
Milena
Jun 06 2019 07:13 AM
Sep 12 2019 03:40 AM
Hi all,
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
Thank you
Sep 12 2019 04:16 AM
It returns 8/13 in such case. If you'd like to have zero if both B25 and B26 are empty, when like
=MAX(8/13,MAX(B25,B26*1000)*0.023)*(B25<>0)*(B26<>0)
Sep 12 2019 04:22 AM
Oh, that all makes sence now, thank you
I have made a few more tabs and just copied and pasted the formula, only changing the "8/13" part.
Would it be better to write it manually in order to work properly?
Thank you
Milena
Sep 12 2019 06:10 AM
@_Milena_ , copy/paste is faster and more preferable, but you shall be accurate with cells references. After pasting the formula just check if it calculates on proper cells.
Sep 13 2019 07:05 AM
Hi Sergei,
Although it refers to the proper cells, it doesn't calculate correctly.
As you can appreciate the only difference is in the $ rate so i really don't understand what the problem would be :(
Sep 13 2019 02:58 PM
I'm not sure what's wrong. In all your sheets B26*1000>B25, thus for each of them formula returns B26*1000*0.023.
Or you expect another result?
Sep 16 2019 02:25 AM
Hello,
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...
Thanks,
Milena
Sep 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?
Sep 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
Cubics 3.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... :D
Again, thanks for your help Sergei and apologies if i haven't make all clear since the beginning
Regards,
Milena
Sep 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
=MAX(MAX(B25/1000,B26)*$B$1,$D$1)/$A$2*(B25<>0)*(B26<>0)
Exchange rate and min Sea Freight we take from the cells and not hardcode them within the formula.
Please check in attached file.
Jun 06 2019 07:04 AM
Solution