Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 22 Jul 2014 20:57:33 +0200How to Sum only if range totals greater than 3https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.
If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.
I hope this makes sense, any advice is appreciately very much.
netsharkTue, 22 Jul 2014 20:57:33 +0200https://ask.libreoffice.org/en/question/37366/Dynamic formula variableshttps://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.
=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)
Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work.
=IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.
Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.
BrianSat, 14 Sep 2013 01:19:51 +0200https://ask.libreoffice.org/en/question/22598/Does Writer support nested IF statements?https://ask.libreoffice.org/en/question/8989/does-writer-support-nested-if-statements/LibreOffice 3.6.4 English (US) on Windows.
Why do I get "** Expression is faulty **" if I try to use nested IFs in writer?
Example:
`<Table2.B4>-<Table2.B3>` is about 1300
=if((<Table2.B4>-<Table2.B3>)<1095; 0.00; 0.15)
gives me
0.15
but
=if((<Table2.B4>-<Table2.B3>)<1095; if((<Table2.B4>-<Table2.B3>)<730; 0.00; 0.1); 0.15)
gives me
** Expression is faulty **InstigaterWed, 12 Dec 2012 21:04:03 +0100https://ask.libreoffice.org/en/question/8989/