With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Monday, June 11, 2012
yogi_Set Up In Column D 50 URLs Based On Sheet Numbers In Column B And Spreadsheet Keys In Column C
Yogi Anand, D.Eng, P.E.Google Spreadsheet Post #590 Jun 11, 2012www.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:
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"))
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.
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.
-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