OpenOffice..Spreadsheet question. How to set a Cell default to a minimum Number?

bobbentos

Hi all

I am currently using open office which is perfect for all my needs. I use the spreadsheet to calculate pieces of plastic and calculate the area. For example

In Cell A1 is the width
(Where I input the width of the piece in mm)

In Cell B1 is the Height
(Where I input the Height of the Piece in mm)

In Cell C1 is the Calculation of the Piece =A1*B1 . This calculation gives me the area of the Piece of plastic.

This function and calculation works perfectly well and is fine. However, the supplier of these pieces of plastic only sells the plastic in minimum areas of 0.25 Meter square. So, I need a Formula so that if the Calculation in C1 is less than 0.25 Metre Square then it defaults to 0.25 Metre Square.

Example...

The plastic sheeting is £100 Per Metre Square

Piece of plastic A is size 250mm x 300mm. This gives an area of 0.075 and based on the plastic price of £100 Per Metre Square this would suggest that the price of this piece at 250mm x 300mm is £7.50. However, like I said, the minimum price size you can buy is 0.25 Per Metre Square so the cost for this piece is actually 0.25 * £100 which is actually £25 and not £7.50! Of course, for pieces over 0.25 Per Metre square the calculation is perfect.

So, my question...What is the Formula I put in Cell C1 which says...If Area is less than 0.25 then area is 0.25.

Thanks all.....

Oh BTW, Soz to be cheeky, but is there any way of protecting Particular Cells so that calculation cannot be erased or changed by mistake?

System

Can't help with Openoffice furmula but the Excel equivalent is:-

=If(A1*B1<0.25, 0.25,A1*B1)

In other words if the test (A1*B1<0.25) is true use the first value 0.25) otherwise use the second (A1*B1).

Openoffice version shouldn't be far from this

ETA quick check online and I think you just need to change the commas to semi-colons and drop the equal sign at the start I.E IF(A1*B1<0.25; 0.25;A1*B1)
See http://www.openofficetips.com/blog/archives/2005/11/sheetcell_prote.html for cell protection

Mr_Toad

I would have thought a simple IF would do it.

=IF(A1*B1 < 0.25,0.25)

I'll have to test it to make sure it works but it's a starting point.

To protect a cell - Format > cells > cell protection. I think this works.

Of course there's nothing to stop someone removing this unless you add a password. Something that also needs testing as I've never had to do it.

I've got a conference call later, it'\ll give me something to do while I listen

Mr_Toad

!!!!!! wrote: »

Can't help with Openoffice furmula but the Excel equivalent is:-

=If(A1*B1<0.25, 0.25,A1*B1)

In other words if the test (A1*B1<0.25) is true use the first value 0.25) otherwise use the second (A1*B1).

Openoffice version shouldn't be far from this

That's the one, you need the A1*B1 on the end

That should work in Openoffice too, I'll check.

bobbentos

Thanks peeps but this just returns an error code ERR:509 in LARGE Letters ??

Any ideas?

Mr_Toad

Hmm...

It works for me in Excel, Numbers on the Mac and Libre office under Linux, it was a long conference call!!

Can you supply me with some real data and I'll give it another go.

tronator

bobbentos wrote: »

Thanks peeps but this just returns an error code ERR:509 in LARGE Letters ??

Any ideas?

Works here in Ubuntu and LO 3.5.4.2. Error 509 means "missing operator"

http://help.libreoffice.org/Calc/Error_Codes_in_Calc

Check your formula for typos. In one of your previous posts the last operator is missing. The correct formula is

=IF(A1*B1<0.25,0.25,A1*B1)

HTH

tiger_eyes

OpenOffice uses slightly different syntax to Excel. This worked for me just now in OpenOffice:

=IF((A1*B1)<=0.25;0.25;(A1*B1))

It means: "If (A1 x B1) is less than or equal to 0.25, enter 0.25; otherwise enter (A1 x B1)."

paddyrg

IIRC there's a function along the lines of "bottom()" - might be worth a look

Mr_Toad

tronator wrote: »

Works here in Ubuntu and LO 3.5.4.2. Error 509 means "missing operator"

http://help.libreoffice.org/Calc/Error_Codes_in_Calc

Check your formula for typos. In one of your previous posts the last operator is missing. The correct formula is

=IF(A1*B1<0.25,0.25,A1*B1)

HTH

Yes I missed the last operator off but if you do that and the first condition is not met it returns FALSE which is the way I tend to use it, it doesn't give you an error. All the last operator does is give the cell a value if you want something other than FALSE

Mr_Toad

tiger_eyes wrote: »

OpenOffice uses slightly different syntax to Excel. This worked for me just now in OpenOffice:

=IF((A1*B1)<=0.25;0.25;(A1*B1))

It means: "If (A1 x B1) is less than or equal to 0.25, enter 0.25; otherwise enter (A1 x B1)."

Yes that works but you don't need all the extra parentheses

=IF(A1*A2 < 0.25, 0.25,A1*A2) also works.

That said I think getting used to bracketing is a good thing for when the calculation gets a bit more complex.