Created attachment 74411 [details] Esempio errore Buongiorno sono felicemente passato a Libreoffice, ma nei file di calcolo creati con Microsoft Office trovo una funzione in più che in Libre office non esiste. Invio questa segnalazione in caso possa esservi di aiuto per migliorare il Vostro programma Nella formula "subtotale", in Microsoft Office se metto il numero 109 sulla funzione, al posto del numero 9, il calcolo viene eseguito senza tenere conto dei numeri nascosti. Allego file con un esempio dell'errore. Grazie Good morning I happily switched to Libreoffice, but in the calculation file created with Microsoft Office find a function in more than in Libre office does not exist. Send this alert in case it might help you to improve your program In the formula "subtotal" in Microsoft Office if I put the number 109 on the function, instead of the number 9, the calculation is performed without taking into account the hidden numbers. I attach a file to an example of the error. thanks
The function numbers used by Excel and Calc differ. Excel uses the function numbers 1-11 and 101-111. The first group includes hidden cells whereas the second group doesn't but both groups are otherwise the same. Calc only recognises 1-11 but these correspond to 101-111 in Excel. In other words, the following are equivalent: Excel =subtotal(109,A1:A5) Calc =subtotal(9,A1:A5) Simply mapping 101-111 to be the same as 1-11 would avoid getting Err:502 and the inability to switch between the two packages.
(In reply to comment #1) > The function numbers used by Excel and Calc differ. > > Excel uses the function numbers 1-11 and 101-111. The first group includes > hidden cells whereas the second group doesn't but both groups are otherwise > the same. > Calc only recognises 1-11 but these correspond to 101-111 in Excel. > In other words, the following are equivalent: > Excel =subtotal(109,A1:A5) > Calc =subtotal(9,A1:A5) > > Simply mapping 101-111 to be the same as 1-11 would avoid getting Err:502 > and the inability to switch between the two packages. No. Not exactly : Type function 1 to 11 exclude cells *hidden by filter* in both Excel and Calc. Type function 101 to 111 exculde *all hidden cells* including cells hidden with Format > Row > Hide.
Hi, The problem is that SUBTOTAL function should allow function numbers from 101 to 111, according to OASIS Standard : [Open Document Format for Office Applications (OpenDocument) Version 1.2 Part 2: Recalculated Formula (OpenFormula) - 29 September 2011; page 165] This is an unpleasant inconvenience for Excel compatibility
If this is a standard compliance issue then it is a bug not an enhancement. Importance changed accordingly. Best regards. JBF
*** Bug 87043 has been marked as a duplicate of this bug. ***
Oasis specs: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018580_715980110
*** Bug 93171 has been marked as a duplicate of this bug. ***
Created attachment 117738 [details] printscreen LO 4.4.5 LO 4.4.5 - bug is fixed. Closing as worksforme, because I don't know the commit.