Excel - Multiply Until Total Reached

0
votes

I want to multiply x*y until x>=20, then multiply z that value and have the results displayed as two values, the multiple and multiple*z

The question behind the formula is, how many boxes of x capacity do I need to have a total capacity of 20 liters and how much does that cost.

x = volume of bottle
y = number of bottles in a box
z = price per box

This could be done very easily by hand, but I've been playing (with little effect) in excel for a while and would like a solution.

I hope that makes sense

1
excelmath
Does integer division not work? Repeated multiplication is not the obvious way to go! How about showing the formulae you have tried? – PJTraill
try =ROUNDUP(limit/(x*y),0) – user4039065

1 Answers

0
votes

I rather think what you would like is the formula provided by @Jeeped but for:

I want to multiply x*y until x>=20, then multiply z that value and have the results displayed as two values, the multiple and multiple*z

label two arrays from 1 to 20 for columns and rows as shown, populate V1 with the price per box and in B2: 

=IF(AND($A2*B$1>20,A2>=20),"",$A2*B$1)

and in X2: 

=IF(B2="","",$V$1*B2)

with both formulae copied across 19 columns and those two sets of 20 formulae then copied down 19 rows. The result should be similar to:

enter image description here