 • 549K Members
• 6,834 Online
• 657K Conversations
SOLVED

Highlighted

# Excel formula help

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.

Milena

16 Replies
Highlighted
Solution

# Re: Excel formula help

@_Milena_ , perhaps

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

# Re: Excel formula help

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

# Re: Excel formula help

Thank you so much :)

Highlighted

# Re: Excel formula help

@_Milena_ , you are welcome

Highlighted

# Re: Excel formula help

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

Thank you

Highlighted

# Re: Excel formula help

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)``

Highlighted

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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?

Highlighted

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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?

Highlighted

# Re: Excel formula help

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

Again, thanks for your help Sergei and apologies if i haven't make all clear since the beginning

Regards,

Milena

Highlighted

# Re: Excel formula help

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.

Highlighted

# Re: Excel formula help

That is brilliant, thank you Sergei!

Highlighted

# Re: Excel formula help

Milena, you are welcome, glad to help.

Related Conversations
Excel Help - Stacking IF Formulas