[Solved] Calc will not accept modification to Excel formula

Discuss the spreadsheet application

[Solved] Calc will not accept modification to Excel formula

Postby mole » Sun Apr 13, 2008 8:25 pm

SOLVED Welcome beginner. Please answer all of the questions below which may provide information necessary to answer your question.
-----------------------------------------------------------------------------------------------------------
Which version of OpenOffice.org are you using? 2.2
What Operating System (version) are you using? Xp home
What is your question or comment? I imported a MS Works spreadsheet into Calc and then tried to alter a monetry value in an existing formula from 0.06806 to 0.07933 and this is what happened:

Existing formula =IF(A35="";"";0.13901*75+0.06806*(E35-75))
New Calc formula: =IF(A37="";"";0.13901*75+0.07933F43*(E37-75))

The Calc formula is incorreect and will not insert into the cell. I know little about spreadsheets so can someone please help?
Last edited by mole on Tue Apr 15, 2008 4:36 am, edited 2 times in total.
mole
 
Posts: 3
Joined: Sun Apr 13, 2008 11:46 am

Re: calc will not accept modification to Excel formula

Postby Villeroy » Sun Apr 13, 2008 9:12 pm

I have no problems entering this formula
Code: Select all   Expand viewCollapse view
=IF(A35="";"";0.13901*75+0.07933*(E35-75))


However, if the constant numbers in your formulas are not really constant (changing from time to time) then you should consider this:
Code: Select all   Expand viewCollapse view
=IF(A35="";"";$Settings.$A$1*$Settings.$A$2+$Settings.$A$3*(E35-$Settings.$A$2))

where A1:A3 on sheet "Settings" contain the constant values. The idea is: Change all parameters in one single place to avoid mistakes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: calc will not accept modification to Excel formula

Postby mole » Mon Apr 14, 2008 9:37 am

Thanks Villaroy. I should have mentioned that the extra reference inserted into my formula by Calc, ie A43, was shown white on black because presumably it was telling me something. If I point to empty cells, which surround the cell with the formula, the A43 changes to a different reference. As you have had no difficulty in entering the modified formula it suggests that I have some Calc feature set by default which I could do without! No doubt all will come clear when I have time to study how Calc works but for now I just need to update my energy cost spreadsheet to see if I can still afford to switch the lights on! I will try your suggested formula when I know enough about Calc to understand it!
mole
 
Posts: 3
Joined: Sun Apr 13, 2008 11:46 am

Re: calc will not accept modification to Excel formula

Postby Hagar Delest » Mon Apr 14, 2008 9:59 am

mole wrote:New Calc formula: =IF(A37="";"";0.13901*75+0.07933F43*(E37-75))

If the formula above is what you've typed, I guess the F43 string should be removed. Or am I missing something?

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 29001
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: calc will not accept modification to Excel formula

Postby mole » Mon Apr 14, 2008 5:32 pm

Thanks to the two guys who tried to help me. I have found the wizard and altered the energy price in the formula in the box that came up and now this appears in the spreadsheet cell without any problem. I am trying to run before I have learnt to walk as far as Calc is concerned and I can only hope that my 75year old brain is capable of progressing further!
mole
 
Posts: 3
Joined: Sun Apr 13, 2008 11:46 am

Re: calc will not accept modification to Excel formula

Postby Hagar Delest » Mon Apr 14, 2008 5:45 pm

mole wrote:I should have mentioned that the extra reference inserted into my formula by Calc, ie A43, was shown white on black because presumably it was telling me something.

OK, I see! If you type a formula directly in a cell, when you use a keyboard arrows (or hit one by mistake), it moves the active cell accordingly: a colored frame shows the active cell and its name is displayed white on black in the formula. It's useful when the cells needed in the formula are not far from the result cell. But if you want to move inside the formula, better use the formula area at top of the spreadsheet.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 29001
Joined: Sun Oct 07, 2007 9:07 pm
Location: France


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests