View previous topic :: View next topic 
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... 

Back to top 


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 

Back to top 


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? 

Back to top 


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. 

Back to top 


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". 
I made a slight adjustment to the formula having reread 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 


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! 

Back to top 


