Hi Johnny, On 03/02/13 15:59, Johnny Rosenberg wrote:
2013/2/3 Hylton Conacher (ZR1HPC) <hylton@conacher.co.za>:Brian, Thank you, and sorry for the double post of this message. The calculation you provided worked perfectly after correctly identifying the necessary cells to be used. funny though the same formula does not work just 2 columns to the left? See below and I am sure identify the cause as I have spent too many hours trying to solve it. On 02/02/13 17:22, Brian Barker wrote:At 16:35 02/02/2013 +0200, Hylton Conacher wrote:I have a formula ....as corrected below(except spaces):=IF(B6>C2;D2;0) + IF(B6-C2>C3;D3;0) + IF(B6-C2-C3>C4;(B6-C2-C3)*B4;0)I did not realise that the formula could be simplified that much :| The extra brackets and 'SUM's were to keep to the BODMAS(Brackets OF Division Multiplication Addition Subtraction) maths rule as I did not want the greater than sign to only work on a single cell but on the sum of a few cells. The formula worked flawlessly and gave me the same answer when compared to a calculator. HOWEVER, Needing to use the same scale and rules on a different column gave me an incorrect answer? I have amalgamated the data sheets onto a single sheet so many of the cell references are the same, except the cell I need to use as the base. Using a copy and paste of the formulae below: The working one: Cell J2=IF(I2>G18,H18,0)+IF(I2-G18>J18,K18,0)+IF(I2-G18-J18>M18,(I2-G18-J18)*L18,0) The non-working one: Cell H2=IF(G2>G18,H18,0)+IF((G2-G18)>J18,K18,0)+IF((G2-G18-J18)>M18,(G2-G18-J18)*L18,0) As you can see the block being used has changed from I2 to G2, the rest of the formula is much the same, bar the addition of an odd bracket pair. The Non-working formula provides the same answer for H2 whether the extra bracket pairs are there or not. FYI: G2=433 and is manually entered I2=1349.31 with formula =SUM(C2,D2)-G2 C2=1479 D2=303.31 with formula =O19 O19=303.31 with formula =SUM(G19:G20,J19:J20, M19:M20) The answers I seek is for H2. Using the non-working formula. the only answer I have been able to get is R 169.80 (which is =H18). This is incorrect as according to the sliding scale the answer should be R 504.05. What is wrong with the non-working formula as it does not seem to continue past the first IF argument? I have attached the file and hope it will appear on Nabble, as even after registering for Nabble, and confirming membership, I saw no instructions/options to upload files. Maybe its automatic? Google was as usual cryptic.Attachments are often stripped off on this list. Upload the file somewhere (Dropbox, UbuntuOne, whatever) and provide us with a link instead.
Apparently Nabble is the place but perhaps someone could enlighten us all?
Exactly what is it you are trying to do? When copying the formula you seem to have manually changed some references. That could be done automatically by adding ”$” in cell addresses: A1 – Row and column will change as you copy to another cell. $A1 – Only row will change. A$1 – Only column will change. $A$1 – The reference is absolute and won't change no matter where you copy or move it.
Thanks, I had forgotten about the $. and the formula now works i.e. it wont work with manual cell references but it does work with absolute cell references?
The easiest way to add those $ characters is to highlight the reference and repeat hitting Shift+F4 until it looks right.
I tried this but none of the proposed solutions was just right.
A simple way to debug a formula is to hit Ctrl+F2 when the cell is highlighted. An dialogue opens and placing the cursor in different places in the formula let you see the results of the different functions in the formula. That way you can study the output of every single IF statement, for instance.
Be VERY careful with Ctrl + F2.when I tried it on my system the whole spreadsheet disappeared to the left. I could only recover it by clicking on the document icon in the Taskbar.
The Ctrl+F2 you mention is probably the same as the function wizard obtained by clicking on f(x) next to the formula bar.
You can also create formulas from scratch in there. It's a good way to learn about all the many different functions (sorted by category) provided.
see above for the Function Wizard ( F(x))Going to redo the formula in the spreadsheet with the absolute reference $ and see if all is solved.
Further references to this Calc 'problem'/ 'user requires training' episode will be made on a new thread.
-- ======================================================================== Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A) http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds largest non profit NGO ======================================================================== -- For unsubscribe instructions e-mail to: users+help@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted