Converting Excel Formula to Open Office

Discuss the spreadsheet application
Post Reply
matt
Posts: 1
Joined: Mon Dec 17, 2007 7:53 pm

Converting Excel Formula to Open Office

Post by matt »

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
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Converting Excel Formula to Open Office

Post by TerryE »

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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Converting Excel Formula to Open Office

Post by acknak »

[Moved new question to it's own new topic: http://user.services.openoffice.org/en/ ... =32&t=1362]
AOO4/LO5 • Linux • Fedora 23
Post Reply