• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shorten Formula

I

Injinia

Member
Hi,


I have a very long formula as shown below, it continues on to about 60 arguments.


=IF($B$1=1;$C$2;IF($B$1=2;$C$3;IF($B$1=3;$C$4;IF($B$1=4;$C$5;IF($B$1=5;$C$6;IF($B$1=6;$C$7;IF($B$1=7;$C$8;IF($B$1=8;$C$9;IF($B$1=9;$C$10;IF($B$1=10;$C$11;IF($B$1=11;$C$12.....


Is there a shorter way to write it down?


cheers, Injinia
 
How would I then write the "index formula" to shorten the one below?


=IF($G41<>"";IF($B$1=1;'Areas'!F5;IF($B$1=2;'Areas'!F43;IF($B$1=3;'Areas'!F81;IF($B$1=4;'Areas'!F119;IF($B$1=5;'Areas'!F157;IF($B$1=6;'Areas'!F195;IF($B$1=7;'Areas'!F233;IF($B$1=8;'Areas'!F271;IF($B$1=9;'Areas'!F309;IF($B$1=10;'Areas'!F347;IF($B$1=11;'Areas'!F385;IF($B$1=12;'Areas'!F423;IF($B$1=13;'Areas'!F461;IF($B$1=14;'Areas'!F499;IF($B$1=15;'Areas'!F537;IF($B$1=16;'Areas'!F575;IF($B$1=17;'Areas'!F613;IF($B$1=18;'Areas'!F651;IF($B$1=19;'Areas'!F689;IF($B$1=20;'Areas'!F727;IF($B$1=21;'Areas'!F765)))))))))))))))))))));NA())
 
Hi Injinia ,


Try this :


=IF($G1<>"",IF($B$1=1,Areas!F5,IF(AND($B$1>1,$B$1<22),OFFSET(Areas!F5,($B$1-1)*38,))),NA())


Narayan
 
hi NARAYANK991,


=IF($G41<>"";IF($B$1=1;'Areas'!F5;IF($B$1<39;OFFSET('Areas'!F5;($B$1-1)*38)));NA())


I input the above formula, pretty much what you gave me, unfortunately doesn't work.


What would be wrong?


-Injinia
 
Hi Injinia..


can you please try the below for me.


=IF(AND(G41<>"",B1<22),INDEX(Areas!$F$1:$F$767,((($B$1-1)*38)+5)),NA())
 
BTW, naraynk991's formula and your formula are not same..

* NARAYANK991 checks the validation from 1 to 22, and you check the validation upto 39.

* NARAYANK991 used ',' in between formula's parameter, you have used ';'

* NARAYANK991 used a ',' after OFFSET's 2nd parameter, and you missed it.


I always trust NARAYANK991, but this time you(Injinia) are the questionnaire, and you are the person, who has better view of the situation and circumstances..:)
 
Hi DR,


Yo right, good thing is that I am learning and continuously improving my Excel skills :)


I was trying to expand my validation to 39, don't know whether that has an effect on how the formula should work.


Correct me if am wrong but you cannot use "," as a separator for the "IF" formula or any other formula for that matter. I tried to copy both formulas as written above to no avail, thus I ended up TRYING to use ";" as a separator but still dint get the desired result.


-Injinia
 
Hi Injinia ,


Both the comma "," and the semi-colon ";" are separators / delimiters for the individual parameters in any function.


In the English version of Office ( what is used by those whose language of work is English ) this is the comma ; in your version of Office ( I think it may be Spanish , I am not sure ) , this is the semi-colon. Please continue to use this in your formulae.


The third comment made by Debraj ( the missing ; in the OFFSET function ) must be the reason your formula did not work ; the OFFSET function expects at least 3 parameters ; the syntax is :


OFFSET(reference,rows,cols,height,width)


where "reference" , "rows" and "cols" are required ; "height" and "width" are optional. If the "rows" and "cols" parameters are not used , their position in the syntax must be marked with a comma or a semi-colon ( depending on the version of Office ). Thus , in your version , the following formula will not return an error :


=OFFSET(Areas!F5;;)


but the following will return an error :


=OFFSET(Areas!F5;)


Just BTW , the single quotes around the sheet name Areas are not required ; the single quotes are required where the sheet name contains spaces.


Narayan
 
Injinia.. you are in the right path (and right blogspot too).. please edit you formula to below and your formula will also works..


=IF($G41<>"",IF($B$1=1,Areas!F5,IF($B$1<39,OFFSET(Areas!F5,($B$1-1)*38,))),NA())
 
Hi Injinia ,


The 38 is the constant offset in your original formula ; for B1 = 1 , the formula was returning the contents of F5 ; for B1 = 2 , the result was F43 , which is 38 away from F5 , for B1 = 3 , the result was F81 , which again was 38 away from F43 , and so on. Mathematically then , getting the result was a simple matter of subtracting 1 from the value of B1 , and adding 5.


Work it out this way ; in column A , starting from cell A1 , fill in the values 1 , 2 , 3 , 4 ,...

In cell B1 , enter the formula =(A1-1)*38+5 ; copy this formula down , and see what values result in column B for the various values in column A.


Narayan
 
@NARAYANK991 : Thanks, for teach me.. about delimiters. :)


@Injinia: "*38" was the pattern of search, provided by you.. 5,43,81 and so on..
 
You must log in or register to reply here.
Back
Top