The Simplest Question Ever Asked On These Forums

[ashscott]

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]

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)))))

J

[ashscott]

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]

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]

=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]

Awesome,

Thanks very much, have another (more advanced Issue posted now about conditional formatting).

Thanks for your help