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

Author Message
ashscott
General User

Joined: 17 Dec 2010
Posts: 25

 Posted: Fri Dec 17, 2010 3:16 am    Post subject: The Simplest Question Ever Asked On These Forums Hi there, Just learning to use calc/spreadsheets, I'm trying to use the IF function to do something it was obviously not designed to do so need your help. I want to point to a cell that can have up to 10 variables in it, and respond to each potential variable differently, for example: =IF(B6=6;(M6-D6)),IF(B6=5;(M7-D6)),IF(B6=4;(M8-D6))...... At the moment I'm trying to use IF over and over but that doesn't work, and it only works for one iteration if I include an IF function in the third part of an already existing IF function.... Hope that makes sense.....IF not, just message me Thanks in advance
jala54
Newbie

Joined: 17 Dec 2010
Posts: 3

 Posted: Fri Dec 17, 2010 3:31 am    Post subject: This works fine, just remember to separate with semicolon( =IF(B6=6;(M6-D6);IF(B6=5;(M7-D6);IF(B6=4;(M8-D6)...... ))))) (corrected) =IF(D9=1;E9;IF(D9=2;F9;IF(D9=3;G9;IF(D9=4;H9;IF(D9=6;I9;0))))) JLast edited by jala54 on Fri Dec 17, 2010 4:18 am; edited 2 times in total
ashscott
General User

Joined: 17 Dec 2010
Posts: 25

 Posted: Fri Dec 17, 2010 4:02 am    Post subject: It seems that only your second example works (stacking the closing brackets of each "IF" function at the end). I get an "Error 502" whenever I attempt to close the "IF" function brackets within each equation...Using version 3.1.1 on XP. Thanks for the help by the way, at least I can make it work
jala54
Newbie

Joined: 17 Dec 2010
Posts: 3

 Posted: Fri Dec 17, 2010 4:14 am    Post subject: Yes, sorry You can also do it like this, to make it more readable: 1 =IF(\$D\$9=1;E10+F10;0) =IF(\$D\$9=2;E11+F11;0) =IF(\$D\$9=3;E12+F12;0) =IF(\$D\$9=4;E13+F13;0) =IF(\$D\$9=5;E14+F14;0) =SUM(D10:D15) J
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Fri Dec 17, 2010 5:54 am    Post subject: =SUM(OFFSET(\$E\$9:\$F\$9 ; \$D\$9 ; 0)) from E9:F9, D9 rows down, column offset =0 works with thousands of rows, in version 3.3 over a million of rows._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
ashscott
General User

Joined: 17 Dec 2010
Posts: 25

 Posted: Mon Dec 20, 2010 10:02 pm    Post subject: Awesome, Thanks very much, have another (more advanced Issue posted now about conditional formatting). Thanks for your help
 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