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

I have far too many IF's..

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


Joined: 03 Aug 2008
Posts: 2

PostPosted: Sun Aug 03, 2008 3:50 pm    Post subject: I have far too many IF's.. Reply with quote

For a small project I'm trying to multiply numbers with their base multipliers. I have 38 different items, and the script compares the name of the current item with the list, and when it hits a match of names, moves over to the corresponding base number and uses the multiplier on it to work out the new number, thus placing it in the cell.
There are roughly 2500 lines of multipliers, so understandably I don't really want to go down them and do this one by one..

Thanks for your help in advance, and here is the rather long IF that I tried. It should work, but I get error 512, which either means too many IF's or too much typing, I don't know which.

Code:
=IF(A2="G$1";PRODUCT(D3;H$1);IF(A2="G$2";PRODUCT(D3;H$2);IF(A2="G$3";PRODUCT(D3;H$3);IF(A2="G$4";PRODUCT(D3;H$4);IF(A2="G$5";PRODUCT(D3;H$5);IF(A2="G$6";PRODUCT(D3;H$6);IF(A2="G$7";PRODUCT(D3;H$7);IF(A2="G$8";PRODUCT(D3;H$8);IF(A2="G$9";PRODUCT(D3;H$9);IF(A2="G$10";PRODUCT(D3;H$10);IF(A2="G$11";PRODUCT(D3;H$11);IF(A2="G$12";PRODUCT(D3;H$12);IF(A2="G$13";PRODUCT(D3;H$13);IF(A2="G$14";PRODUCT(D3;H$14);IF(A2="G$15";PRODUCT(D3;H$15);IF(A2="G$16";PRODUCT(D3;H$16);IF(A2="G$17";PRODUCT(D3;H$17);IF(A2="G$18";PRODUCT(D3;H$18);IF(A2="G$19";PRODUCT(D3;H$19);IF(A2="G$20";PRODUCT(D3;H$20);IF(A2="G$21";PRODUCT(D3;H$21);IF(A2="G$22";PRODUCT(D3;H$22);IF(A2="G$23";PRODUCT(D3;H$23);IF(A2="G$24";PRODUCT(D3;H$24);IF(A2="G$25";PRODUCT(D3;H$25);IF(A2="G$26";PRODUCT(D3;H$26);IF(A2="G$27";PRODUCT(D3;H$27);IF(A2="G$28";PRODUCT(D3;H$28);IF(A2="G$29";PRODUCT(D3;H$29);IF(A2="G$30";PRODUCT(D3;H$30);IF(A2="G$31";PRODUCT(D3;H$31);IF(A2="G$32";PRODUCT(D3;H$32);IF(A2="G$33";PRODUCT(D3;H$33);IF(A2="G$34";PRODUCT(D3;H$34);IF(A2="G$35";PRODUCT(D3;H$35);IF(A2="G$36";PRODUCT(D3;H$36);IF(A2="G$37";PRODUCT(D3;H$37);IF(A2="G$38";PRODUCT(D3;H$38))))))))))))))))))))))))))))))))))))))
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Aug 03, 2008 4:53 pm    Post subject: Reply with quote

Have you thought of using VLOOKUP() for example?

Enter [type] a value that you would want from the list in column G into A2. Then, in the cell where this formula is to appear put this:

=D3*VLOOKUP(A2;G1:H38;2;0)

David.
Back to top
View user's profile Send private message
RichardM
Newbie
Newbie


Joined: 03 Aug 2008
Posts: 2

PostPosted: Mon Aug 04, 2008 9:23 am    Post subject: Reply with quote

Thanks David, that worked brilliantly. I had looked at VLOOKUP but wasn't sure what I was doing with the index, though now I know how to use it, so thanks for that too Smile
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