OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

function taken from excel doesn't work on Calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
rookie
General User
General User


Joined: 12 Jan 2005
Posts: 13
Location: Greece

PostPosted: Sat Feb 03, 2007 9:42 am    Post subject: function taken from excel doesn't work on Calc Reply with quote

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...
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sat Feb 03, 2007 10:17 am    Post subject: Reply with quote

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 Embarassed
Back to top
View user's profile Send private message
rookie
General User
General User


Joined: 12 Jan 2005
Posts: 13
Location: Greece

PostPosted: Sun Feb 04, 2007 3:23 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Feb 04, 2007 6:38 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Feb 04, 2007 7:32 am    Post subject: Reply with quote

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


I made a slight adjustment to the formula having re-read your original request and trying my suggestion out. In cell K11:

=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?
Back to top
View user's profile Send private message
rookie
General User
General User


Joined: 12 Jan 2005
Posts: 13
Location: Greece

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

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! Embarassed

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 Very Happy ) 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!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group