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

tiger_eyes

Mr_Toad wrote: »

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.

Thanks for the advice - I have a simple mind so resort to brackets to make sense of everything. My point was more that the commas don't seem to work in OpenOffice; it returns an Err:508. The formula only works with the semi-colons.

System

As I said in my ETA to my original post

Mr_Toad

tiger_eyes wrote: »

Thanks for the advice - I have a simple mind so resort to brackets to make sense of everything. My point was more that the commas don't seem to work in OpenOffice; it returns an Err:508. The formula only works with the semi-colons.

It's a good habit to have when things get complicated.

I'm using Libre Office and it changes the semicolon to a comma.

I thought Open and Libre were pretty much the same, perhaps not.

bobbentos

Der !!!!!!!!!!!!!!

Ok Peeps, I have to hold my hands up! I am a Dummy! Here's Why............

Well, here's me asking you questions, to which you all answered CORRECTLY. So, I then go away and copy & paste your formula's into my calculation cell. The formulas you gave me were CORRECT!

So, can anyone guess what I had done wrong?

Answer below.....

Well, in my EXAMPLE (Note the word example) I just told you guys that my width & heights were in A1 & B1 etc, when in my real spreadsheet they weren't. They were actually in C7 & D7. That meant that when I was simply COPYING & PASTING your formulas from here I was using your formulas containing A1 & B1 Boxes! Sorry to befuddle you all!

However, after all your kind help I do still have a small problem with the formula. Here it is...

Because we are ordering this stuff in Metric see the example below...

Width 650
Height 1830
Area 773175

So we can either input... NOTE THE DECIMAL PLACES IN THIS EXAMPLE

Width .650
Height 1.830
Which now gives an area of 1.18 WHICH IS HOW THE SUPPLIER ONLY ACCEPTS ORDERS.

So, before, I asked you guys for help on here my own calculation (which worked ok) went...

Cell C7 Width 650
Cell D7 Height 1830
Cell E7 The Formula I've always used is =((C7*D7)/100000)

Where the bit in Red brings it to proper Metric sizes.

With this in mind, can somebody please amend the formula from earlier on in the post to return an area in the Format 1.18 instead of 773175.

Sorry to bother you all.

Stompa

In Excel you'd just use:

=MAX(A1*B1,0.25)

so in OO probably:

=MAX(A1*B1;0.25)

Stompa

bobbentos wrote: »

Because we are ordering this stuff in Metric see the example below...

Width 650
Height 1830
Area 773175

So we can either input... NOTE THE DECIMAL PLACES IN THIS EXAMPLE

Width .650
Height 1.830
Which now gives an area of 1.18 WHICH IS HOW THE SUPPLIER ONLY ACCEPTS ORDERS.

So, before, I asked you guys for help on here my own calculation (which worked ok) went...

Cell C7 Width 650
Cell D7 Height 1830
Cell E7 The Formula I've always used is =((C7*D7)/100000)

Where the bit in Red brings it to proper Metric sizes.

With this in mind, can somebody please amend the formula from earlier on in the post to return an area in the Format 1.18 instead of 773175

I've no idea where your 773175 comes from as 650*1830 is 1189500, so you just need to divide by 1000000 to get 1.18.

Mr_Toad

bobbentos wrote: »

Der !!!!!!!!!!!!!!

Ok Peeps, I have to hold my hands up! I am a Dummy! Here's Why............

Well, here's me asking you questions, to which you all answered CORRECTLY. So, I then go away and copy & paste your formula's into my calculation cell. The formulas you gave me were CORRECT!

So, can anyone guess what I had done wrong?

Answer below.....

Well, in my EXAMPLE (Note the word example) I just told you guys that my width & heights were in A1 & B1 etc, when in my real spreadsheet they weren't. They were actually in C7 & D7. That meant that when I was simply COPYING & PASTING your formulas from here I was using your formulas containing A1 & B1 Boxes! Sorry to befuddle you all!

However, after all your kind help I do still have a small problem with the formula. Here it is...

Because we are ordering this stuff in Metric see the example below...

Width 650
Height 1830
Area 773175

So we can either input... NOTE THE DECIMAL PLACES IN THIS EXAMPLE

Width .650
Height 1.830
Which now gives an area of 1.18 WHICH IS HOW THE SUPPLIER ONLY ACCEPTS ORDERS.

So, before, I asked you guys for help on here my own calculation (which worked ok) went...

Cell C7 Width 650
Cell D7 Height 1830
Cell E7 The Formula I've always used is =((C7*D7)/100000)

Where the bit in Red brings it to proper Metric sizes.

With this in mind, can somebody please amend the formula from earlier on in the post to return an area in the Format 1.18 instead of 773175.

Sorry to bother you all.

Thanks for letting us know what the problem was. We've all done it at some point and it was good of you to let us know rather than just disappear.

Assuming you're talking about mm and meters, there are a couple of obvious solutions but I'd go for fixing the root cause.

It looks like you're measuring in mm but your supplier uses meters, the easy answer is to measure and input in meters 0.65 and 1.83 then you answer will be in meters which is what you need.

The other solution is measure and enter in mm then convert to meters as per the example given.

Mr_Toad

Of course i forgot to give the last example.

Using your example of cells C7 and D7

C7=650
D7=1830

the formula for the area would now be;

=IF(((C7*D7)/1000000) < 0.25; 0.25; ((C7*D7)/1000000))

This is where the brackets we were discussing are needed to ensure that each part of the calculation is done correctly.

Mobeer

Can I just check that if a piece of plastic is 2.5m * 0.2m then this can be purchased at cost of 2.5*0.2*100 = £50, and you don't have to buy 2.5*0.25*100 and then trim the size of the piece of plastic?

Seems a funny pricing policy given you could ask for something a couple of cm wide and very very long for quite a low price.