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

Author Message
RichardM
Newbie

Joined: 03 Aug 2008
Posts: 2

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

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))))))))))))))))))))))))))))))))))))))
David
Super User

Joined: 24 Oct 2003
Posts: 5668
Location: Canada

 Posted: Sun Aug 03, 2008 4:53 pm    Post subject: 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.
RichardM
Newbie

Joined: 03 Aug 2008
Posts: 2

 Posted: Mon Aug 04, 2008 9:23 am    Post subject: 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
 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

Powered by phpBB © 2001, 2005 phpBB Group