SOLVED

Excel formula help

Copper Contributor

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

 

16 Replies
best response confirmed by _Milena_ (Copper Contributor)
Solution

@_Milena_ , perhaps

=MAX(10,MAX(B19,B20*1000)*0.01)
Your desired result seems to be the MAXimum of the 3 values representing each argument of this formula:
=MAX(10,B19*.01,B20*10)

@Sergei Baklan ,

 

Thank you so much :)

@_Milena_ , you are welcome

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

@_Milena_ 

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)

 

@Sergei Baklan 

 

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

@_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.

@Sergei Baklan 

 

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 :(

 

 

@_Milena_ 

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?

@Sergei Baklan 

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

@_Milena_ 

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?

@Sergei Baklan 

 

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

 

 

 

 

@_Milena_ 

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.

image.png

In A2 just type the number with current exchange rate (1.23), Ctrl+1 and apply custom format as here

image.png

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

image.png

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.

@Sergei Baklan 

 

That is brilliant, thank you Sergei!

 

 

@_Milena_ 

Milena, you are welcome, glad to help.

1 best response

Accepted Solutions
best response confirmed by _Milena_ (Copper Contributor)
Solution

@_Milena_ , perhaps

=MAX(10,MAX(B19,B20*1000)*0.01)

View solution in original post