[libreoffice-users] Re: Calc scale formula conundrum/ logic confusion
Hi,
zr1hpc wrote
Using Calc 3.4.5 on openSuse 11.2
I am trying to work out the currency value of the amount of electricity
units I have left at the end of the month (G2).
This same formula will be used for the other 11 months of the year with
only the G2 reference changing to G3, G4...etc.
For those who wish to stretch their grey matter as mine just ain't
stretching any more.
The pricing scale the energy supplier has given me is:
Unit RangeValue per unit
0-150 1.1320
150.01 - 600 1.1811
600.01 - 1.4018
Therefore the first 150 units are priced at 1.132 ea. any units still
available need to be calculated at the next rate of 1.1811, but only up to
449.90 units at the second rate. Anything more than that must be at the
highest rate of 1.4018.
Assuming my unit balance of G2 = 433 I can work out the currency value of
G2 by following the following logic, and using the scale above:
433 150 therefore the initial 150 units need to be priced at 1.1320 i.e.
150* 1.132= ZAR169.80. To this the balance of the units over 150 need to
be multiplied by the next rate of 1.1811 i.e. 433-150= 283. Since 283 is
below the max of 449.9 units allowed for this scale, therefore 283*1.1811=
ZAR334.25. The third scale is not needed as all the units have been
accounted for, however it must be available should G2 exceed 600 units
e.g. taking 601 units; the first 150 would be costed at 1.132, the next
449.9 costed at 1.1811 and the balance of 1.1 units costed at 1.4018 per
unit.
Therefore total value of 433 units is:
150*1.1320 = 169.80
+ 283*1.1811 = 334.25
_
Value ZAR504.05
This formula will give you the result :
=SUMPRODUCT(G2{0;150;600};G2-{0;150;600};{1,132;0,0491;0,2207})
0.0491 is the difference of the cost between the cost for 150units600 and
units150
0.2207 diff between cots for units600 and 150units600
Look at this post in the forum :
http://forum.openoffice.org/en/forum/viewtopic.php?f=9t=40938
Download the file of my post (the 3rd one) to see how it works.
Gérard
--
View this message in context:
http://nabble.documentfoundation.org/Calc-scale-formula-conundrum-logic-confusion-tp4037350p4037526.html
Sent from the Users mailing list archive at Nabble.com.
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc - bar chart - does not list order properly for cell values
I have just seen my post dont be sent. Gérard Fargeot wrote krackedpress wrote I use 3.5.6 on Ubuntu 10.04 64-bit I notice that when I create a listing of months and money going March 100.00 April 200.00 May 150.00 June100.00 The graphs always give me the information from bottom to top. June - - - - - May- - - - - - - April- - - - - - - - - - March - - - - - I have manually changed the data ranges starting with the last item and ending with the first, but the column or bar graphs always seem to give me the same visual of last to first on the chart/graph. I do not know what is going on with this. I am dealing with a graph showing month and income is a bar chart, but having the current month on the left and the oldest month on the right just does not work for others. Here is the Data info in the wizard. Data Range: $'Royalty Checks'.$A$5:$B$31 Data Series: column B: $'Royalty Checks'.$A$5:$A$31 Y-Values $'Royalty Checks'.$A$5:$B$31: $'Royalty Checks'.$B$5:$B$31 When I do a highlighting for bottom to top or top to bottom, the ranges and graph always is the same. When I revers the order of the beginning and end values for the cells, I still get the same order of the graph visual elements. I do remember that last year, when I created a chart like this with 3.3.x [late version] or 3.4.x [early version], it did work file. If I highlighted the top to bottom, it showed the order of the elements matching top to bottom. I placed the chart/graph next to the columns [in column C] so the value of the cell is in alignment with the bar on the chart's graphic. It is used to watch the trend of the income from that person's monthly royalty checks. Since the earlier spreadsheet was lost with a computer crash and the backup disk was damaged, I cannot go back to the old file and just update it. Just check Reverse direction Reverse_Axis.jpg http://nabble.documentfoundation.org/file/n4009465/Reverse_Axis.jpg It works for bar chart, column chart ...etc It works with text value, numeric value... Gérard -- View this message in context: http://nabble.documentfoundation.org/Calc-bar-chart-does-not-list-order-properly-for-cell-values-tp4009346p4009702.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibreOffice Questions
there is a way to change the settings in my current version of LibreOffice to enable my documents to do so automatically? Hi, Go to Tools Options LibreOffice User data and enter your first/last name. Gérard -- View this message in context: http://nabble.documentfoundation.org/LibreOffice-Questions-tp3667095p3667265.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] So what's with LibO 3.5?
Hi http://wiki.documentfoundation.org/ReleaseNotes/3.5 Message du 11/01/12 09:53 De : Onyeibo Oku A : Libre, Users Copie à : Objet : [libreoffice-users] So what's with LibO 3.5? Greetings I've been seeing libO 3.5 on this list and I'm now curious. What is in this 3.5 that should make me peep? What changed and what does the user stand to benefit from the changes? I like bleeding edge, there's got to be a good excuse to dive in first. Can someone enlighten me? I use 3.4.4 at the moment. - from twohot@device.mobile :) -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] Re: [3.4.4/Windows] How to move cell with mouse?
Hi, Message du 11/01/12 18:16 De : Pedro A : users@global.libreoffice.org Copie à : Objet : [libreoffice-users] Re: [3.4.4/Windows] How to move cell with mouse? This is a know limitation since the OpenOffice days. The good news is that an *enhancement request* is already on the Bugzilla tracker https://bugs.freedesktop.org/show_bug.cgi?id=38994 IMHO, it is not a good news. Drag cells by clic on a tiny border instead of anywhere in the cell is *not* an improvement :( Gérard -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Happy New Year all!
Hi all, 12 years after, a new millenium bug ? Open a new spreadsheet, in a cell in the middle of the screen (G15 for example) : change row height to 2 cm or more, paste the following formula, =T(STYLE(Heading))CHAR(HEX2DEC(4D))MID(The Document Foundation;3;1)REPT(RIGHT(Writer);EVEN(PI()/2))CHAR(POWER(LEN(LibreOffice);2))CHAR(32)LEFT(Calc)RIGHT(Math)MID(Draw;2;1)LOWER(SUBSTITUTE(Impress;mpres;))T(tm)MID(Base;2;2)CHAR(10)ROT13(naq )MID(SUBSTITUTE(Harry Potter;r;p);1;FACT(3))LEFT(New York;5)CHAR(ARABIC(CI))RIGHT(SUBSTITUTE(Gérard;d; );3)YEAR(EDATE(TODAY();1))CHAR(10)to LOWER(BASE(32156;33;3)) LibOPROPER(ROT13( grnz)) :) Gérard -- View this message in context: http://nabble.documentfoundation.org/Happy-New-Year-all-tp3623969p3624443.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: To sum only those values in a range that are between two number
Hi Pal,
I'm trying with this formula:
{=SUMIF('O.tanácsi f.év'.Y7:Y26;AND('O.tanácsi
f.év'.Y7:Y2625;'O.tanácsif.év'.Y7:Y26=384))}
Did you forget the magic function SUMPRODUCT ?
=SUMPRODUCT('O.tanácsi f.év'.Y7:Y26;'O.tanácsi
f.év'.Y7:Y2625;'O.tanácsif.év'.Y7:Y26=384)
Gérard (again)
--
View this message in context:
http://nabble.documentfoundation.org/To-sum-only-those-values-in-a-range-that-are-between-two-number-tp3616470p3616564.html
Sent from the Users mailing list archive at Nabble.com.
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibreOffice 3.4.4 - Error: wrong data type.
Hi, Hi, I have in the range of O23:O27 on one sheet formulas: 1. cell: =DARABTELI(K5:K24;ÉS(K5:K24=5;K5:K24=7)) 2. cell: =DARABTELI(K5:K24;ÉS(K5:K24=8;K5:K24=15)) 3. cell: =DARABTELI(K5:K24;ÉS(K5:K24=16;K5:K24=25)) 4. cell: =DARABTELI(K5:K24;ÉS(K5:K24=26;K5:K24=34)) 5. cell: =DARABTELI(K5:K24;=35) The cell K5 has the formula: =HA(CELLA(contents;'1.'.T$28)0;CELLA(contents;'1.'.T$28);.) In the cells: O23, O24, O27 everything is well. But in cells O25 and O26 I get ### instead of some result and in the status line there is an error: 'Error: wrong data type.' when those cells are active (one by one of course). What could be the problem here? -- Regards, Pal You will have more answer if you post formula in English instead of Hungarian. :) dot (.) is a special character. In the menu Tools Options LO Calc Calculate, turned off Enable regular expressions in formulas. Your formula =DARABTELI(K5:K24;ÉS(K5:K24=5;K5:K24=7)) (COUNTIF in english) seems to be false. ÉS(K5:K24=5;K5:K24=7) if validate as normal formula only checked if 1st row (K5) is between 5 and 7. If validate as an array formula, checked if *all* the cells of the range are between 5 and 7 and return an array of 1 if True, 0 if false. If you want to count how many cells of the range are =5 =7 , used SUMPRODUCT : =SZORZATÖSSZEG(K5:K24=5;K5:K24=7) It is also a very bad idea to use dot in sheetname. Always use alphanumeric characters. Gérard -- View this message in context: http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-tp3614423p3614520.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibreOffice 3.4.4 - Error: wrong data type.
quote=quot;csanyipalquot; Can I do that from LibreOffice? How can I translate formula names? Must I change the Language environment for this? Tools Options LO Calc formula, use english functions names. But some functions (add-in) are not translated. I've made a formula translator : http://user.services.openoffice.org/en/forum/viewtopic.php?f=9t=41870 You can translate functions or whole formula to a language to an other. Gérard -- View this message in context: http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-tp3614423p3614763.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] How to paste a function so Calc doesn't change addresses in it?
Hi,
Message du 26/12/11 16:25
De : Csányi Pál
A : Libre Office
Copie à :
Objet : [libreoffice-users] How to paste a function so Calc doesn't change
addresses in it?
Hi,
I'm working on a spreadsheet and I want to copy/paste a function.
When I copy/paste the function Calc increase some address in the
functions that I have in the cell and those addresses aren't proper for
my purpose.
Eg. I want to copy/paste the value of a cell:
=HA(DARABÜRES('1.'.AD6);0;HA('1.'.AD6=1;-;HA(1'1.'.AD66;+;hamis)))
but when I paste it, I get:
=HA(DARABÜRES('1.'.AD7);0;HA('1.'.AD7=1;-;HA(1'1.'.AD76;+;hamis)))
and that isn't good for me, because I want to get
=HA(DARABÜRES('1.'.AD6);0;HA('1.'.AD6=1;-;HA(1'1.'.AD66;+;hamis)))
in the new cell;
actually I would like to get:
=HA(DARABÜRES('2.'.AD6);0;HA('2.'.AD6=1;-;HA(1'2.'.AD66;+;hamis)))
but I think that that this can't be achieve with Calc, right?
How can I copy/paste the function so so the addresses in the function
remain the same as in the original cell?
Best Regards, Pál
Use absolute cell reference.
Press Shift+F4 will change relative to absolute reference for the active cell.
Your formula
=HA(DARABÜRES('1.'.AD6);0;HA('1.'.AD6=1;-;HA(1'1.'.AD66;+;hamis)))
will change in
=HA(DARABÜRES($'1.'.$AD$6);0;HA($'1.'.$AD$6=1;-;HA(1$'1.'.$AD$66;+;hamis)))
$ before sheet, column or row reference fixed them. They do not increase when
copied.
Gérard
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: The way SUM works
Is this saved on a per spreadsheet basis or is it global (do I need to change it back and forth, if I open 2 sheets can one be Precision as Shown and the other normal. steve All options defined in *tools options LO calc calculate* are stored in the document, these settings doesn't affect other files. Gérard -- View this message in context: http://nabble.documentfoundation.org/The-way-SUM-works-tp3602412p3603495.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: The way SUM works
I'm far from an expert. But, once something is selected in the tools, options., that becomes the default for all new spreadsheets. It isn't something that you need to do every time you open a new sheet. I do settlement sheets. To have the right numbers, I need precision as shown. When I install LibreOffice, I always change calc for precision as shown. All new sheets are done that way unless I would go back and uncheck it. That is my definition of Global. Don Hi, We don't have the same définition of global. You're right when you says that becomes the default for all new spreadsheets, when you create a new spreadsheet you need to define the settings. But other speadsheet (already save as) are not affected by this change. That's why i wrote it is not global. Gérard -- View this message in context: http://nabble.documentfoundation.org/The-way-SUM-works-tp3602412p3605250.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] Syntax Question
Hi, Message du 14/12/11 14:29 De : Ian Witty Whitfield A : users@global.libreoffice.org Copie à : Objet : [libreoffice-users] Syntax Question Hi All Can someone help me with some syntax please?? I have a two-sheet Calc Spreadsheet. All my data lives on the first sheet called Data and my Analysis Reports are on Sheet Two. I have the same values in any given Column in my Data Sheet, (ie all my Totals are in Column D), and - as per my last posting here - I can now ID which Row I need to use each month. So on my Analysis Sheet I want to fetch all the needed data but it must be dynamic. If my Column Identifier is at Data.H5 and my Row Identifier is at Data.M9 I can place the cell reference on my Second Sheet with '=Data. H5((M9)' [Lets say I put this into Cell M10] I have tried - '=INDIRECT(Data.M10)' and 'INDIRECT(Data.(M10))' and '=INDIRECT(Data.+(M10)) etc etc but nothing works. Can anyone give me the correct syntax for this please? It seems as soon as I cross sheets the syntax changes because a straight typed-in '=Data.M10' works fine!! What I need is - '=THE VALUE IN CELL(Data.H5)(Data.M9). Thanks a lot. (I'm using LO 3.4) Ian Whitfield. =INDIRECT(Data.Data.H5Data.M9) with Letters column identifier in Data.H5 and number row identifier in Data.M9 If column identifier is a number, you can use INDIRECT ADDRESS =INDIRECT(ADDRESS(Data.M9;Data.H5;4;;Data)) Gérard -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] [3.4.4/Win] Keyboard shortcut: Add row?
Hi, Message du 06/12/11 13:56 De : Gilles A : users@global.libreoffice.org Copie à : Objet : [libreoffice-users] [3.4.4/Win] Keyboard shortcut: Add row? Hello, Google says that CTRL++ on the numeric keypad adds a row, but... 1. It displays the Insert cells dialog 2. It doesn't feel to me as natural as eg. CTRL+Ins or CTRL+Enter Is there a way to reconfigure LibreOffice's Calc so that I'm spared that dialog, and ideally, assign a different keyboard shortcut for that task? Thank you. First option : You can insert row in 2 times, Shift+Space select the row, then Ctrl++ insert row. Second option : You can modify keyboard shortcut in Tools Customize. Gérard -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: SUMIF on certain values
Hi, razor7 wrote Hi, i'm trying to calculate sum of a column on certain values of other column. I want to sum all the values from column value only if corresponding id column value is 9, 10 or 11. My SUMIF functin looks like this *=SUMIF(D2:D187;9,10,11;E2:E187)* where D2 is id column and E2 is value column. So far i get 0 as SUMIF result but result may be 180 =SUMIF(A2:A7,9|10|11,B2:B7) With Regular expressions enabled in Tools Options LibreOffice Calc Calculate. Gérard -- View this message in context: http://nabble.documentfoundation.org/SUMIF-on-certain-values-tp3527618p3527665.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] name-define-edit causes crash in LO Calc v3.4.3
Hi, Message du 06/11/11 00:43 De : David S. Crampton A : Users discussions at global.libreoffice.org Copie à : Objet : [libreoffice-users] name-define-edit causes crash in LO Calc v3.4.3 Context: in Calc of LO v3.4.3 on Windows XP Pro. I What fails: Insert | Names | Define: brings up Define Names dialog; Initially, in the Define Names dialog, Scope=Global, Name=, Listbox contains my outstanding checks, Assigned To is greyed out. Click on outstanding checks in the listbox; Assigned To becomes active and shows the currently defined $A1:$D9. Observe that the button Add becomes relabeled to Modify. Ignoring, in this first case, the Modify button: Click into the Assigned To box. Type in the edits to rewrite $A1:$D9 to $A31:$D40. Click button OK. LO Calc crashes. Every time. Upon restart LO Calc does file recovery. Recovery is successful. Range is still defined as the original $A1:$D9. Alternate GUI method: Click Modify button to initiate the same edit. Same crash behavior. I did an amateur's search of Bugzilla prior to writing this. Hundreds of define results but not on this AFAIK. If no clues come from Users forum I will post as new bug. Regards -- David S. Crampton Searching with name ctash, bug with many duplicates are found. No need to post another bug. Plus, this bug is solved in future 3.4.4 Gérard -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] Bug: Controls in Calc change their position and size automatically
Message du 20/10/11 12:54 De : Frieder A : users@global.libreoffice.org Copie à : Objet : [libreoffice-users] Bug: Controls in Calc change their position and size automatically Hello My systems: LO3.4.3 on Win7 and LO3.3.3 on Linux Since a while I noticed, that controls like command-buttons and list-boxes change their position shape and size automatically. This phenomenon appear even if I protect their position and size. This happens for example after hiding or reshowing a column, but it appears also after closing and reopening a document. I think this is a general bug, because it appears in different versions of LO and if I remember well it even appeared in OO. Can anion confirm this bug? Is there already a bug- report? Regards Frieder It is a knowned bug : https://bugs.freedesktop.org/show_bug.cgi?id=37083 This affect all objects (drawning objects, pictures, form controls...) if anchored to a cell. Workarround : Anchor to page. Gérard -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc Formula Help
Hi, macroC wrote: So I have two columns of data, column A which has a single character (A, G, or T) and column B which has a number. I am trying to determine the minimum value in B that corresponds to an A in column A. This is the forumla I came up with after looking at tutorials and reading the documentation: =MIN(IF(A1:A7=A,B1:B7)) But this simple gives a #VALUE error. Can anyone help me figure out what is wrong with the formula? Thanks. Your formula is correct, but it is an array formula. An array formula must be validate with key combinaison Ctrl+Shift+Enter, not simply Enter. Gérard -- View this message in context: http://nabble.documentfoundation.org/Calc-Formula-Help-tp3303917p3303997.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] Problems using VLOOKUP in Libreoffice Calc
Hello,
Message du 29/08/11 01:18
De : minnesotauser
A : users@global.libreoffice.org
Copie à :
Objet : [libreoffice-users] Problems using VLOOKUP in Libreoffice Calc
I have two columns set up like so:
a | 1
b | 2
c | 3
I want to be able to use a number from column B and have the text from
column A displayed.
But when I put {=vlookup(B4,A1:B3,1,0)} in cell A4, and {=max(B1:B3)} in B4
(minus the brackets for both), I get back, correctly, 3 for the max, but
#N/A for the VLOOKUP.
Putting in the letters a, b, or c in B4 correctly returns the number it's
next to, so it seems like only the first column in the array is searched.
Is it possible to flip the way the columns are searched?
Thanks for any help.
VLOOKUP search in the 1st column of the array, so you can't get a result in a
left column.
Use INDEX and MATCH for this :
=INDEX(A1:A3,MATCH(B4,B1:B3,0))
Gérard
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] how to move text string into formula, from another cell
Hello,
Message du 20/06/11 21:41
De : Tom Cloyd
A : LibreOffice User's Help Forum
Copie à :
Objet : [libreoffice-users] how to move text string into formula, from
another cell
I just thought of a way to save myself considerable time, by calculating
part of the parameter list of a formula, then moving the result into the
formula. But...I'm not getting it to work, and I'm wondering if I'm
making some dumb error.
The original cell formula I'm trying to improve is this:
=IF($E10=x,'m6-20'.N$4,0)
Every week I have to manually alter this part of it: m6-20
That part is the name of another sheet, were the cell N$4 contains the
value I'm after. The sheet name references 'day of week', 'number of
month', and 'number of day in month'.
I'm now calculating the sheet name by using a cell containing month
number, a fixed array containing the day of the week, and a variable
array containing the number of day in month, and the result is perfect.
In the case of the formula above, I get a calculation result of
['m6-20'.N$4,0] (result is between the brackets - exactly as it appears
in the calculation cell).
Here's the calculation formula for the string:
=CONCATENATE(',K2,K1,-,K3,',N$4)
I want to move that string into position in the formula above -
=IF($E10=x,'m6-20'.N$4,0). Here's what doesn't work:
* removing the string 'm6-20'.N$4 from the formula, then clicking on the
cell where the same string is a calculated result. All that does is put
the calculated string into the cell, dropping all other parts of the
formula.
* changing the original formulat to =IF($E10=x,K4,0) or
=IF($E10=x,K4,0) - where K4 is the cell with the calculated text
string. Either of these result in an error 510.
* inserting the string calculation formula INTO the original formula:
=IF($E10=x,CONCATENATE(',K2,K1,-,K3,',N$4),0). Amazingly, this
just produces, again, the 'm6-20'.N$4 calculated string.
I don't know what to try next. Is this just not possible?
Thanks for any help!
You may used INDIRECT formula :
=IF($E10=x,INDIRECT(K4),0)
Gérard
--
Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] How can I reset all settings?
Message du 17/06/11 21:23 De : Jan Parttimaa A : users@global.libreoffice.org Copie à : Objet : [libreoffice-users] How can I reset all settings? Hi all! I have a question. How can I reset all settings and toolbars to defaults in LibreOffice? I already tried to unistall and reinstall LibreOffice and yes I restarted PC after unistalling but it didn't work. I still have non-default settings. I use Windows 7 (64-bit). Sincerely Jan Parttimaa Reset your user profile : http://user.services.openoffice.org/en/forum/viewtopic.php?p=58401#p58401 Gérard -- Unsubscribe instructions: E-mail to users+h...@global.libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LibreOffice 3.4 changes my cell formatting…
Hello, Johnny Rosenberg wrote: Right click a cell range → Format cells… → Numbers → Now type in the format description field: # ##0,00 [$kr-41D];[GREEN]-# ##0,00 [$kr-41D];±0,00 [$kr-41D] → Save and close file → Open file again → Format description: [0]# ##0,00 [$kr-41D];[0]-# ##0,00 [$kr-41D];±0,00 [$kr-41D] Why did they add this exciting feature? Workaround suggestions? Kind regards Johnny Rosenberg Color Tags are lost after saving. This bug is alredy reported : https://bugs.freedesktop.org/show_bug.cgi?id=37658 You can use Conditional formatting. Gérard -- View this message in context: http://nabble.documentfoundation.org/LibreOffice-3-4-changes-my-cell-formatting-tp3034737p3035137.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
re: [libreoffice-users] Chart how to
Hi, Cut/paste. Message du 25/04/11 17:13 De : CohoMike A : users@libreoffice.org Copie à : Objet : [libreoffice-users] Chart how to How to move a chart from the data page where it appears to its own worksheet or tab? -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Content disappears with vertical text in merged cells while autospellcheck is on.
pintero wrote: I've got a weird problem, possibly a bug in Calc: Make sure AutospellCheck is turned on and select some empty cells in a row (A1-A10 for example) Click Format-Merge Cells-Merge and center cells Now fill it with some jibberish, so it gets red-underlined. Now click Format-Cells-Alignment, enter 90 in Degrees - OK and the content just disappears magically! If you turn off the AutoSpellCheck, content reappears. It looks like checking the 'Wrap text automatically' checkbox in the Alignment window won't affect the content, but without the Wrap option Calc can't display the red-underline vertically. If you turn off the autospellcheck, the jibberish content reappears without red-underline and if you change the text and turn on the spellcheck, the text is gone again. This can't be by design.. Same problem with 3.3.0 and latest 3.3.1. Can anyone confirm this? Already reported in Freedesktop : https://bugs.freedesktop.org/show_bug.cgi?id=33622 -- View this message in context: http://nabble.documentfoundation.org/Content-disappears-with-vertical-text-in-merged-cells-while-autospellcheck-is-on-tp2632953p2633112.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://listarchives.libreoffice.org/www/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Converting text to numbers
Arda Tunccekic wrote (07-12-10 13:27) - Now type 1.22 , 2.4 , 3.51 in the first 3 rows. With or whithout the ? Using Find Replace, Regular expression checked Without the Search : .* Replace : With the Search : (.*) Replace : $1 Gérard -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://www.libreoffice.org/lists/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Converting text to numbers
Message du 07/12/10 14:15 De : Arda Tunccekic A : users@libreoffice.org Copie à : Objet : Re: [libreoffice-users] Converting text to numbers Hi Gérard, My example is with spaces.. They are numbers like 3.51 I saw this. Spaces are removed by the way i told you. No need to search them. Using FindReplace, with regular expressions checked , searching for *[:space:]?$* , replacing with nothing removes spaces. But this is can be a hard step for regular users. And again, they say me ms excel does it, what is this now :) I don't use MS Excel... On 07.12.2010 15:05, Gérard Fargeot wrote: Arda Tunccekic wrote (07-12-10 13:27) - Now type 1.22 , 2.4 , 3.51 in the first 3 rows. With or whithout the ? Using Find Replace, Regular expression checked Without the Search : .* Replace : With the Search : (.*) Replace : $1 Gérard -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://www.libreoffice.org/lists/users/ *** All posts to this list are publicly archived for eternity *** -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org List archive: http://www.libreoffice.org/lists/users/ *** All posts to this list are publicly archived for eternity ***
Re: [libreoffice-users] Data Validation Err 504
-Message d'origine- From: wfc007 Sent: Thursday, November 11, 2010 6:28 PM To: users@libreoffice.org Subject: [libreoffice-users] Data Validation Err 504 Hi. I'm having a problem (err:504) using data validation (Datos - validez in spanish). Using an iterval of cells (Criterios - Permitir - Intervalo de celdas), selecting the respective range and then after trying to enter data to a cell, it appears err:504. I attached a sample file for you to see the error. Thank you. http://nabble.documentfoundation.org/file/n1883912/Error_504.ods Error_504.ods -- View this message in context: http://nabble.documentfoundation.org/Data-Validation-Err-504-tp1883912p1883912.html Sent from the Users mailing list archive at Nabble.com. -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted Thanks for reporting this. The problem is known and solved. https://bugs.freedesktop.org/show_bug.cgi?id=30946 Gérard -- E-mail to users+h...@libreoffice.org for instructions on how to unsubscribe List archives are available at http://www.libreoffice.org/lists/users/ All messages you send to this list will be publicly archived and cannot be deleted
