IT:99 Bottles of Beer (Lotus 1-2-3)
From Stiki
The formulae listed below can be used to produce the lyrics to traditional repetitive songs. The American version produces the lyrics to 99 Bottles of Beer and the British version produces the lyrics to 99 Green Bottles Hanging on the Wall.
In order to use these formulae, copy them into a cell in the spreadsheet as required, and then copy that cell down as far as you like. The formulae will write the complete song lyrics given enough rows, followed by a blank line, and then will begin writing the lyrics again from the beginning. Note that whichever cell you have pasted the formula into, the cell above it must be blank.
Note that you should copy the version that is 'Formatted for copying the formula' as this does not include any line breaks. The version that is 'Formatted for viewing the structure of the formula' includes additional line breaks and spacing that will prevent the formula from working in 1-2-3, but aid understanding of how the formula works.
American version
This formula should be entered into cell A4 and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
@IF(A3="","99 bottles of beer on the wall",@IF(@LEFT(A3,1)="N","",@IF(A2="2 bottles of beer"#AND#A2<>"","1 bottle of beer on the wall",@IF(A2="1 bottle of beer"#AND#A2<>"","No more bottles of beer on the wall",@IF(@RIGHT(A3,1)="l"#AND#A3<>A2,A3,@IF(@RIGHT(A3,1)="l",@LEFT(A3,@LENGTH(A3)-12),@IF(@RIGHT(A3,1)="r","Take one down and pass it around",@IF(@RIGHT(A3,1)="d",@STRING(@VALUE(@LEFT(A2,@FIND(" ",A2,0)))-1,0)&@RIGHT(A1,@LENGTH(A1)-@FIND(" ",A1,0)),@ERR))))))))
|
| Formatted for viewing the structure of the formula |
|---|
@IF(
A3="",
"99 bottles of beer on the wall",
@IF(
@LEFT(A3,1)="N",
"",
@IF(
A2="2 bottles of beer"#AND#A2<>"",
"1 bottle of beer on the wall",
@IF(
A2="1 bottle of beer"#AND#A2<>"",
"No more bottles of beer on the wall",
@IF(
@RIGHT(A3,1)="l"#AND#A3<>A2,
A3,
@IF(
@RIGHT(A3,1)="l",
@LEFT(A3,@LENGTH(A3)-12),
@IF(
@RIGHT(A3,1)="r",
"Take one down and pass it around",
@IF(
@RIGHT(A3,1)="d",
@STRING(
@VALUE(
@LEFT(
A2,
@FIND(" ",A2,0)
)
)-1,
0
)&@RIGHT(
A1,
@LENGTH(A1)-@FIND(" ",A1,0)
),
@ERR
)
)
)
)
)
)
)
)
|
British version
This formula should be entered into cell A3 and then copied down. One iteration of the lyrics will occupy 397 rows.
| Formatted for copying the formula |
|---|
@IF(A2="","99 green bottles hanging on the wall",@IF(@ISERR(@FIND("no",A2,0)),@IF(@LEFT(A2,1)="A","There'll be "&@IF(@LEFT(A1,2)="1 ","no"&@REPLACE(@RIGHT(A1,33),13,2,"s h"),@IF(@LEFT(A1,2)="2 ","1"&@REPLACE(@RIGHT(A1,34),12,2,"e"),@STRING(@VALUE(@LEFT(A1,2))-1,0)&@RIGHT(A1,@LENGTH(A1)-@FIND(" ",A1,0)))),@IF(A1="",A2,@IF(@LEFT(A1,1)="T",A2,@IF(@ISERR(@VALUE(@LEFT(A1,2))+@VALUE(@LEFT(A2,2))),@IF(@LEFT(A2,1)="T",@REPLACE(A2,0,12,""),@NA),"And if 1 green bottle should accidentally fall")))),""))
|
| Formatted for viewing the structure of the formula |
|---|
@IF(
A2="",
"99 green bottles hanging on the wall",
@IF(
@ISERR(@FIND("no",A2,0)),
@IF(
@LEFT(A2,1)="A",
"There'll be "&@IF(
@LEFT(A1,2)="1 ",
"no"&@REPLACE(@RIGHT(A1,33),13,2,"s h"),
@IF(
@LEFT(A1,2)="2 ",
"1"&@REPLACE(@RIGHT(A1,34),12,2,"e"),
@STRING(
@VALUE(
@LEFT(A1,2)
)-1,
0
)&@RIGHT(
A1,
@LENGTH(A1)-@FIND(" ",A1,0)
)
)
),
@IF(
A1="",
A2,
@IF(
@LEFT(A1,1)="T",
A2,
@IF(
@ISERR(@VALUE(@LEFT(A1,2))+@VALUE(@LEFT(A2,2))),
@IF(
@LEFT(A2,1)="T",
@REPLACE(A2,0,12,""),
@NA
),
"And if 1 green bottle should accidentally fall"
)
)
)
),
""
)
)
|
See also
- IT:99 Bottles of Beer (Microsoft Excel) for the equivalent cell formulae to be used in Microsoft Excel.
