*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #590 Jun 11, 2012

*www.energyefficientbuild.com*.

*user tabus said:*

*multiple if-clause problem*

*Hi everyone,*

*The problem I encountered seems ridiculously easy, but after tens of tries, I still can't figure out how to fix it:(*

*Here we go:*

*col.A - empty*

*col.B - consecutive numbers: 1 onwards, achieved through "arrayformula"; ocassionally it gets text value instead of number*

*col.C - depending on neighbouring cell's value in col.B*

*if 0<value<51 then "text 1"*

*if 50<value<101 then "text 2"*

*if 100<value<151 then "text 3"*

*and so on...*

*So, the table should look like:*

*col.B col.C*

*1 text 1*

*2 text 1*

*... text 1*

*50 text 1*

*51 text 2*

*52 text 2*

*... text 2*

*100 text 2*

*101 text 3*

*Looks really simple, right?*

*So I use this formula in col.C:*

*=arrayformula(if(ISNUMBER(B3:*B)=true;if(and(B3:B>0;B3:B<51);"text 1";if(and(B3:B>50;B3:B<101);"text 2";if(and(B3:B>100;B3:B<151);"text 3";"here another if-clause")));"n/a"))

*The issue:*

*As long as the value in col.B is between 0 and 50 (incl.), the formula works OK, but as soon as it turns into 51, I don't get any the "text 1, 2 or 3" but "here another if-clause" instead.*

*What's wrong??*

*It seems as if "B3:B" is read: "check the last value in col.B"*

*How do I fix it?*

*Any help appreciated!*

*----*

*Thanks for your quick reply,*

*Although the solution you've come up with does seem to work, it is unfortunately not really what I was trying to achieve:(*

*Well, probably my description wasn't clear enough, but for the sake of clarity I simplified the formula presented in the 1st post*

*So, let me now explain the problem in detail.*

*col.B*

*-it is filled up automatically with numbers from 1 to ..., they increase gradually by 1*

*-the values in col.B actually stand for "gid" number, used later on for producing specific urls*

*col.C*

*-here I take the corresponding value from col.B and produce an url, according to pattern: "https://docs.google.*com/spreadsheet/pub?key= "&"...key value... "&"&single=true&gid="&B3:B &"&output=html&gridlines=false"

*-due to "importrange" restrictions, specific "...key value..." can only be used 50 times in 50 separate urls*

*-so my idea was to change "...key value..." (i.e. use another doc) when gid number turns from 50 to 51, then 100/151, and so on*

*that's why I wanted to make separate "key values" dependent on the value from col.B (i.e. gid number)*

*and again, the formula I presented above works until the value in col.B is <51 - a nice url is produced, but since 51 it only shows the option "here another if-clause"*

*I can't get it work on given if-clauses*

*If it's clearer now, can help me out with that?*

-------------------------------------------------------------------------------------------------------

based on my understanding of what the OP is asking for, let us have a look at the following solution

Hi Yogi, again, many thanks for your next solution.

ReplyDeleteI'm sorry to say however it's not what I was looking for.

I made the worksheet public, so please, have a look at:

https://docs.google.com/spreadsheet/pub?key=0AhFEFv15COC1dHBYM2czZlV4MEdpUHl3UWltamFMSEE&output=html

The "url_OK" tab is what it should look like, the "url_notOK" shows when it stops working. The crucial point is between rows 52 and 53.

Thanks in advance!