Hi
I am currently creating a database of customers and products sold. I am after a formula that will look at all the customer names in a given column, and return the customer name that appears most often. I managed to do this with Excel 2007 using the following formula;
=INDEX(A1:A27,MATCH(MAX(INDEX(COUNTIF(A1:A27,A1:A27),0)),INDEX(COUNTIF(A1:A27,A1:A27),0),0))
Unfortunately Calc doesn't like it.
Any suggestions please?
Thank you.
[Solved] Calculating Most Reoccurring Text
[Solved] Calculating Most Reoccurring Text
Last edited by Hagar Delest on Tue Jun 10, 2008 2:23 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
Reason: tagged the thread as Solved.
Regards
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista
- Hagar Delest
- Moderator
- Posts: 32654
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Calculating Most Reoccurring Text
Have you tried to open the file with Calc or have you pasted the formula in the Calc file ?
Basically, comas in Excel are replaced by semi-colons in Calc. Changing the comas in your formula should be worth the try.
Basically, comas in Excel are replaced by semi-colons in Calc. Changing the comas in your formula should be worth the try.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Calculating Most Reoccurring Text
Hello Hagar
Thanks for the suggestion, but replacing the commas with semi-colons did not work
I cut and pasted the formula from Outlook 2007.
Thanks for the suggestion, but replacing the commas with semi-colons did not work
I cut and pasted the formula from Outlook 2007.
Regards
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista
Re: Calculating Most Reoccurring Text
This office includes a database component. A spreadsheet is no database.
Your formula can not work with Calc nor Excel.
After replacing all commas I analysed the formula with the formula wizzard (Ctrl+F2).
It includes an INDEX call
INDEX(COUNTIF(A1:A27;A1:A27);0)
with a number as first argument (return value of COUNTIF) and a row index of zero.
Your formula can not work with Calc nor Excel.
After replacing all commas I analysed the formula with the formula wizzard (Ctrl+F2).
It includes an INDEX call
INDEX(COUNTIF(A1:A27;A1:A27);0)
with a number as first argument (return value of COUNTIF) and a row index of zero.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Calculating Most Reoccurring Text
Hi
Thanks to everybody for their help above.
I have worked out how to do what I wanted using "PilotData", which appears to be the OO equivalent of Excel's "Pivot Tables".
Villeory; you stated that the formula I pasted from Excel cannot work. Sorry mate, but it has been doing the job I required of it in Excel 2007 for the past few months.
Thanks to everybody for their help above.
I have worked out how to do what I wanted using "PilotData", which appears to be the OO equivalent of Excel's "Pivot Tables".
Villeory; you stated that the formula I pasted from Excel cannot work. Sorry mate, but it has been doing the job I required of it in Excel 2007 for the past few months.
Regards
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista
Myrm
"Glöm inter vår Tobias" (In memory of Tobias Enroth 1999-2007)
OOo 3.0.X on MS Windows Vista