Making calculations upon multiple conditions without using many ugly IF's

Good day, Experts.

I need to make calculations depending on max / min values in A column.

Values in A column are numbers of cartons.
Data in Column B - list of juices with different volumes.
In column G - I need to calculate correct pallete height depending on the below conditions:

1. Cartons with 1L juices are 25 cm in height:
For instance if quantity of 1L cartons on the pallete is less or equal 24 then pallete height = 3.
The formula in this case is 0.25*3+0.15 (* - 0.15 is height of just pallete)

2. if quantity of 1L cartons on the pallete is less or equal 30 then pallete height = 4.
The formula: 0.25*4+0.15

3. if quantity of 1L cartons on the pallete is less or equal 40 then pallete height = 5.
The formula: 0.25*5+0.15

Cartons with 330 ML juices are 20 cm in height:
If quantity of 330 ML cartons on the pallete is less or equal 70 then pallete height = 7
The formula: 0.2*7+0.15, If q-ty is less or equal 80, we just change 7 to 8

Cartons with 330 ML and 1L cartons sometimes are mixed on the pallete. In this case quantities are indicated for each Carton. I couldn't solve this problem.

Is there any way to get short formula without IF statements?

Greetings! I'm new here, but hope this helps! There is always more than 1 way to do complete this:

Create a table on another sheet and do a lookup:
=LOOKUP(A2,Sheet2!A:A,Sheet2!B:B)

WIth a table of
24 / 3
30 / 4
40 / 5
70 / 7
80 / 8

This is easy for small amounts of data.

Hi Kirkules,

Unfortunately my task is slightly more complex. I need to get the height of each pallet according to the height of each carton. Height of pallet is determined by rows.
For example there are 3 rows with cartons containing 1L bottles.
The height of each carton is 25 cm. Then 3 * 25 - 75 cm or 0.75 m. We need to add the height of pallet - 15 cm.
And finally we get 0.90 meters height. (correct calculations are shown in my example)

The problems is that cartons are of different height.
Cartons with 330 ml bottles are 20 cm in height. And they can be mixed with 1L.
I need to solve the double task: to shorten the cosmic length formula and also consider variable cartons on one pallete.

Hi, Bo_Ry,

Amazing! This is exactly the algorithm that I was looking for! Thank you so much!