Converting Excel Formula to Open Office

Discuss the spreadsheet application

Converting Excel Formula to Open Office

Postby matt » Mon Dec 17, 2007 7:59 pm

I have a questionnaire that is written in excel and I need to convert a formula to open office. The formula is =COUNTIF(D1:D45,"Yes")/MAX(1,SUM(COUNTIF(D1:D45,{"Yes","No"})))
I get a #Name? error on open office when i copied and pasted the formula.
I have about 45 questions that can be answered yes, no or n/a. I need to know the percentage.

thanks
matt
 
Posts: 1
Joined: Mon Dec 17, 2007 7:53 pm

Re: Converting Excel Formula to Open Office

Postby TerryE » Mon Dec 17, 2007 8:35 pm

Calc supports the same function list as Excel, but the separator is ; and not ,. One easy way to d this conversion is to open an XLS with the formulas in it in OOo Calc and the conversion will get on automatically on load.

Also I've had problems getting inline lists to work, so I tend to use regular expressions (which can be enabled through Tools - Options - OpenOffice.org Calc - Calculate), hence the formula becomes
=COUNTIF(D1:D45;"Yes")/MAX(1;SUM(COUNTIF(D1:D45;"(Yes|No)")))
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Converting Excel Formula to Open Office

Postby acknak » Fri Jan 04, 2008 5:51 am

AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17422
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: Villeroy and 25 guests