[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
rookie
General User

Joined: 12 Jan 2005
Posts: 13
Location: Greece

 Posted: Sat Feb 03, 2007 9:42 am    Post subject: function taken from excel doesn't work on Calc Hello to the forum. I'm trying to migrate a spreadsheet from MS Excel to OO.o Calc but the function that I use in one cell doesn't work. Here is the function in the 'K11': =IF(F11=0;0;IF(F11=1;L12;SUM(L12:OFFSET(K11;F11;1)))) 'F11' should be 0 or a positive integer. For 'F11'=0 I get 0. For 'F11'=2 I get 1. Now, if 'F11' is anything else I should get the sum of the cells starting from 'L12' (that is one line below the cell hosting the function and i column to the right) to down as many cells as the integer in 'F11' indicates. The mentioned function works in Excel but is Calc I get an error about invalid name. If I change the function to: =IF(F11=0;0;IF(F11=1;L12;SUM(L12;OFFSET(K11;F11;1)))) it works fine. I.e. it only returns the sum of 'L12' and the cell 'F11' lines below. Any ideas why the ':' in the SUM function doesn't work in this case? If not, any other ways to achieve what this functions does under Excel in Calc? Thank you in advance...
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sat Feb 03, 2007 10:17 am    Post subject: Can you use the OFFSET() function to return the whole range you want, not just the last cell? Something like =IF(F11=0;0;IF(F11=1;L12;SUM(OFFSET(L12;0;0;F11;2)))) but I haven't tried it
rookie
General User

Joined: 12 Jan 2005
Posts: 13
Location: Greece

 Posted: Sun Feb 04, 2007 3:23 am    Post subject: Thank you for your reply! I've tried your suggestion but now I get an error about "circular reference". In the way you use OFFSET(), you specify the number of rows that you moved down but you specify the total column number in the moved area, correct?
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

Posted: Sun Feb 04, 2007 6:38 am    Post subject:

 rookie wrote: In the way you use OFFSET(), you specify the number of rows that you moved down but you specify the total column number in the moved area, correct?

Yes, OFFSET will give you a range, not just one cell if you want.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

Posted: Sun Feb 04, 2007 7:32 am    Post subject:

 rookie wrote: I've tried your suggestion but now I get an error about "circular reference".

=IF(F11=0;0;IF(F11=1;L12;SUM(OFFSET(L12;0;0;F11;1))))

Then I have 1, 2, 3, 4, 5 in cells L12, L13, L14, L15, L16 (I think that's the area you are trying to add up).

With 0 in F11, I get 0.

With 1 in F11, I get 1.

With 2 in F11, I get 3 (1+2).

With 3 in F11, I get 6 (1+2+3).

With 4 in F11, I get 10 (1+2+3+4).

With 5 in F11, I get 10 (1+2+3+4+5).

Is this what you want?
rookie
General User

Joined: 12 Jan 2005
Posts: 13
Location: Greece

Posted: Sun Feb 04, 2007 7:49 am    Post subject: [SOLVED] function taken from excel doesn't work on Calc

The "circular reference" error was my mistake because I didn't change the reference cell from 'K11' to 'L12' in the OFFSET(). So I do apologize for making you spent more time on my problem!

 Quote: =IF(F11=0;0;IF(F11=1;L12;SUM(OFFSET(L12;0;0;F11;1))))

Yep! This is exactly what I want! And I believe (I know more about OFFSET() now ) we could remove the second 'IF' and have something like that:

=IF(F11=0;0;SUM(OFFSET(L12;0;0;F11;1)))

Thank for your time, suggestions and solution RickRandom!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum