I am new to spreadsheet formulas and question which I am asking is typical math question.
I have a value (in d3) which refers to total gram of gas required, now I have to release this much (d3) gas from number of gas Cans. Gas Cans are of two kinds bigger one contains 680 grams gas and small one contains 454 grams of gas.
Now once we open the gas CAN then whole gas will be released mean to say that we cannot release part portion of gas however we can minimize wastage if we use exact (or near to exact) amount of gas required by using some big cans and some small cans. Now I want total no. of big cans and small cans in d4 & d5 with a formula for open office.
At present I am using following formula for d4:
=FLOOR(FLOOR(D3/680)+MOD(D3, 680)/454)
And using following formula for d5:
=IF(D3 > D4*680, CEILING(MOD((D3-D4*680)/454, 454)), 0)
Above mentioned formula works fine but I want this to work for open office spreadsheet.
It has been explained in detail with example in following link:
https://docs.google.com/spreadsheets/d/1zZiKMqb33kDSOIduBMayZ0NMqibcvMF-UBYYvn7VVBs/edit
Please help me in modifying above mentioned formulas for open office. Thanks in advance.
OpenOffice uses semicolon separators rather than comma separators.
The FLOOR and CEILING functions require two arguments in OpenOffice, with the second argument being the multiple to which you are rounding. It looks like Excel will assume, if the second argument is omitted, that you are rounding to a multiple of 1. In OpenOffice you have to actually type the 1:
=FLOOR(FLOOR(D3/680;1)+MOD(D3;680)/454;1)
=IF(D3>D4*680;CEILING(MOD((D3-D4*680)/454;454);1);0)