Re: [libreoffice-users] calc formula are displayed as ###
I see the same effect when the column is too narrow to display the formula. Display is If the column width is increased so the entire formula can be displayed, then the formula will be displayed. The fact that the formula does not display over adjacent empty cells, like text does, I would think of as a required feature. Not calling it a bug because I don't know and couldn't understand the programming behind the Display Formulas setting. The formula() function does not behave the same way and will display the formula over adjacent cells if they are empty. On Wed, Jul 13, 2022 at 1:49 AM Uwe Brauer wrote: > > > Hi > > Using LO 7.2, in scalc I selected > > Tools-->Options-->Scalc-->View--Display_Formulas > > Which I activated > > However then the formula in that colum are displayed as ### (before > turning this option on, the result was displayed) > > Is this a BUG or a feature. > > If it is a feature I fail to see its benefits. > > Regards > > Uwe Brauer > > > > > -- > I strongly condemn Putin's war of aggression against the Ukraine. > I support to deliver weapons to Ukraine's military. > I support the ban of Russia from SWIFT. > I support the EU membership of the Ukraine. > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > > -- Alan Boba CISSP, CCENT, ITIL v3 Foundations 2011 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Den sön 30 maj 2021 kl 18:05 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: > On 2021/05/29 23:36, Johnny Rosenberg wrote: > > Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) < > > hyl...@conacher.co.za>: > > > >> Hi Johnny, > >> > >> On 2021/05/28 23:44, Johnny Rosenberg wrote: > >>> Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < > >>> hyl...@conacher.co.za>: > >>> > Hi, > > I have LO 7.0.6.2 and am battling with understanding which formula to > use as well as the syntax for that formula. > > I am aware of the availability of vlookup, hlookup, Index/Match > formula > and have settled I think on the right one i.e. Index/Match > > Below is a portion of my spreadsheet that is divided as below with a > blank column between each year. What I want to calculate is the date > the > Max rain occurred. I am OK with the formula to obtain the MAX but I > need > help in constructing a formula to get the corresponding date. > > I had though the best would be Index and Match but no matter how I > enter > it I cannot get the date listed under the Date column of 2020 or 2021, > never mind actually retrieving the year from the same column as the > date > the originated. > > =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first > date > under Highest Monthly > > >>> > >>> Strange. I give a completely different result, so obviously my > >> spreadsheet > >>> isn't identical to yours. > >>> In which cell did you put this formula and what result did you expect? > >>> > > I have looked aver tutorial and their mothers trying to find out what > ranges to insert into the Index(Match()) formula with ZERO success. > > I am manually entering the dates under each year when the max value is > revealed by my formula. Would love to have it automated but my entire > spreadsheet covers over 400 rows and more than 52 columns resulting in > 26 tabs of graphs from the Data sheet. > > Is my data in the wrong order i.e. should the rainfall value column be > before the Date it occurred? > > I do not understand what ranges need to consist of when using > >> Index/Match. > > Can someone point me to a decent tutorial explaining the different > terms > i.e. Reference, Row, Column, Range, Search Criterion, Lookup array. > > 20202021Highest Monthly > Date2020 Rain Date2021 Rain DateRain > 01/19 9,5 01/15 3 2020/01/19 9,5 > 02/16 1,5 02/14 3,5 2021/02/14 3,5 > 03/25 3,5 03/14 19 2021/03/14 19 > 04/11 20 04/26 7 2020/04/11 20 > 05/28 27,505/20 43 2021/05/20 43 > 06/11 26 #N/A0 0 > 07/09 85,5#N/A0 0 > 08/28 35 #N/A0 0 > 09/02 21 #N/A0 0 > 10/28 15 #N/A0 0 > 11/06 25 #N/A0 0 > 12/26 2 #N/A0 0 > > If you want the entire spreadsheet it is available on direct request, > but ultimately I would like to understand how it works. > > This will at least enable to use the formula successfully on newer > versions on LO. > > Regards > Hylton > > > >>> I'm not sure what you are trying to do here, so I'll just guess. Just > >>> ignore me if I'm totally wrong. > >>> So first, I tried to create a spreadsheet following the text above, so > >> this > >>> is what my spreadsheet looks like: > >>> Row 1 and two are just headers. > >>> Column A is dates for 2020 formatted as Month/Day. > >>> Column B is amount of rain in some unit, not sure which one, so I > assume > >>> mm, since that's what we use where I live, and it doesn't matter for > this > >>> question anyway. > >>> Column C is empty. > >>> Column D is dates for 2021 formatted as Month/Day. > >>> Column E is the amount of rain for 2021. > >>> Column F is empty. > >>> Column G is the column that contains the dates for each month for the > >>> respective year with the highest amount of rain, and it's also the > column > >>> that you wish to automate, is that right? > >>> Column H is the highest value of rain in columns B and E for each row. > >>> > >>> Right so far? > >>> > >>> If so, you want column G to display the date for each maximum value in > >> the > >>> B and E column per row, right? Column H is already figured out, so for > >>> instance, H3 contains the following: > >>> =MAX(B
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Bravo Remy, On 2021/05/29 20:29, Remy Gauthier wrote: Hello, I created something that ressembles what Johnny created, and I understand your data is formatted like so: Data1 Data2 blank Data3 Data4 blank Data5 Data6 You want Data6 to be the maximum of Data2 and Data4 (and possibly more columns as well), and you want to have Data5 equal to the date on which the maximum occurred. As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where x is the row number. If you want to use MATCH to find the date, then you shoud proceed like so: The MATCH() function takes 3 arguments: - Value to search - Array where to search - How to search This function will return where the value you are looking for is located in the search array. The "how to search" argument tells the function if the values in the array are sorted (1 or -1) or not (0). In your case, the values are not sorted so you will need to use 0. Note that if you use 1 or -1, the search will return the closest match,and will not fail if the value you are looking for does not exist in your search array; if you use 0, however, the search will fail if your value is not in the array. I will usually always use zero (exact match) regardless of how the data is sorted. The only time I will make use of the 1 or -1 values is when I need to interpolate in a series of data points and I need to find where the interpolation will take place in the dataset. The first argument will be Data6. The second argument will be Ax:Ex, where x is the row number. You can use the entire row like this because: - Date values will always be greater than the rain values you have - Empty cells do not count The third argument will be zero since you want an exact match in an array that is not sorted. The output of the MATCH() function will be the column number of the maximum (since the first cell of the search range is Ax). The date is 1 to the left: one column less, so MATCH()-1 will give the column where we can find the date. To extract the date with INDEX(), you must use the same range as used to MATCH() the value. The arguments are: - Cell range - Row in the range (1 if you only select one row of data) - Column in the range The cell range will be Ax:Ex (exactly what was used in the MATCH() function), the row will be 1 (only 1 row of data), and the column will be the result of the MATCH() we did minus 1. This means the formula in Gx will be: =INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1) I hope this helps. Rémy. Works like a charm. I mostly understood your explanation too. and will keep reviewing it until it sinks in I copy and pasted your formula and then 'reinvented' it to cover the correct ranges and lines in the Data sheet. Now the job is to incorporate the year into the date. Regards Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
On 2021/05/29 23:36, Johnny Rosenberg wrote: Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: Hi Johnny, On 2021/05/28 23:44, Johnny Rosenberg wrote: Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: Hi, I have LO 7.0.6.2 and am battling with understanding which formula to use as well as the syntax for that formula. I am aware of the availability of vlookup, hlookup, Index/Match formula and have settled I think on the right one i.e. Index/Match Below is a portion of my spreadsheet that is divided as below with a blank column between each year. What I want to calculate is the date the Max rain occurred. I am OK with the formula to obtain the MAX but I need help in constructing a formula to get the corresponding date. I had though the best would be Index and Match but no matter how I enter it I cannot get the date listed under the Date column of 2020 or 2021, never mind actually retrieving the year from the same column as the date the originated. =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date under Highest Monthly Strange. I give a completely different result, so obviously my spreadsheet isn't identical to yours. In which cell did you put this formula and what result did you expect? I have looked aver tutorial and their mothers trying to find out what ranges to insert into the Index(Match()) formula with ZERO success. I am manually entering the dates under each year when the max value is revealed by my formula. Would love to have it automated but my entire spreadsheet covers over 400 rows and more than 52 columns resulting in 26 tabs of graphs from the Data sheet. Is my data in the wrong order i.e. should the rainfall value column be before the Date it occurred? I do not understand what ranges need to consist of when using Index/Match. Can someone point me to a decent tutorial explaining the different terms i.e. Reference, Row, Column, Range, Search Criterion, Lookup array. 20202021Highest Monthly Date2020 Rain Date2021 Rain DateRain 01/19 9,5 01/15 3 2020/01/19 9,5 02/16 1,5 02/14 3,5 2021/02/14 3,5 03/25 3,5 03/14 19 2021/03/14 19 04/11 20 04/26 7 2020/04/11 20 05/28 27,505/20 43 2021/05/20 43 06/11 26 #N/A0 0 07/09 85,5#N/A0 0 08/28 35 #N/A0 0 09/02 21 #N/A0 0 10/28 15 #N/A0 0 11/06 25 #N/A0 0 12/26 2 #N/A0 0 If you want the entire spreadsheet it is available on direct request, but ultimately I would like to understand how it works. This will at least enable to use the formula successfully on newer versions on LO. Regards Hylton I'm not sure what you are trying to do here, so I'll just guess. Just ignore me if I'm totally wrong. So first, I tried to create a spreadsheet following the text above, so this is what my spreadsheet looks like: Row 1 and two are just headers. Column A is dates for 2020 formatted as Month/Day. Column B is amount of rain in some unit, not sure which one, so I assume mm, since that's what we use where I live, and it doesn't matter for this question anyway. Column C is empty. Column D is dates for 2021 formatted as Month/Day. Column E is the amount of rain for 2021. Column F is empty. Column G is the column that contains the dates for each month for the respective year with the highest amount of rain, and it's also the column that you wish to automate, is that right? Column H is the highest value of rain in columns B and E for each row. Right so far? If so, you want column G to display the date for each maximum value in the B and E column per row, right? Column H is already figured out, so for instance, H3 contains the following: =MAX(B3;E3) Then you want to automatically display the datum of which this occured, or just the year? Well, date or year is only a question about formatting, so let's just leave it to be formatted later. I guess you know how to do cell formats and styles anyway. maybe I totally misunderstood the question, but if not, you don't need neither INDEX nor MATCH for this. Here's my cell formula in G3, for instance: =IF(B3>E3;A3;D3) < So it there was more rain in 2020 than in 2021, display the date in column A, otherwise display the date in column D. If you want to fill further down to future dates and only show the values when the rest of the row is completely entered, you could add another test, like this: =IF(OR(A3=""
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Hello, I created something that ressembles what Johnny created, and I understand your data is formatted like so: Data1 Data2 blank Data3 Data4 blank Data5 Data6 You want Data6 to be the maximum of Data2 and Data4 (and possibly more columns as well), and you want to have Data5 equal to the date on which the maximum occurred. As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where x is the row number. If you want to use MATCH to find the date, then you shoud proceed like so: The MATCH() function takes 3 arguments:- Value to search- Array where to search- How to searchThis function will return where the value you are looking for is located in the search array. The "how to search" argument tells the function if the values in the array are sorted (1 or -1) or not (0). In your case, the values are not sorted so you will need to use 0. Note that if you use 1 or -1, the search will return the closest match,and will not fail if the value you are looking for does not exist in your search array; if you use 0, however, the search will fail if your value is not in the array. I will usually always use zero (exact match) regardless of how the data is sorted. The only time I will make use of the 1 or -1 values is when I need to interpolate in a series of data points and I need to find where the interpolation will take place in the dataset. The first argument will be Data6. The second argument will be Ax:Ex, where x is the row number. You can use the entire row like this because:- Date values will always be greater than the rain values you have- Empty cells do not countThe third argument will be zero since you want an exact match in an array that is not sorted. The output of the MATCH() function will be the column number of the maximum (since the first cell of the search range is Ax). The date is 1 to the left: one column less, so MATCH()-1 will give the column where we can find the date. To extract the date with INDEX(), you must use the same range as used to MATCH() the value. The arguments are:- Cell range- Row in the range (1 if you only select one row of data)- Column in the range The cell range will be Ax:Ex (exactly what was used in the MATCH() function), the row will be 1 (only 1 row of data), and the column will be the result of the MATCH() we did minus 1. This means the formula in Gx will be: =INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1) I hope this helps.Rémy. Le vendredi 28 mai 2021 à 23:44 +0200, Johnny Rosenberg a écrit : > Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < > hyl...@conacher.co.za>: > > Hi, > > I have LO 7.0.6.2 and am battling with understanding which formula > > touse as well as the syntax for that formula. > > I am aware of the availability of vlookup, hlookup, Index/Match > > formulaand have settled I think on the right one i.e. Index/Match > > Below is a portion of my spreadsheet that is divided as below with > > ablank column between each year. What I want to calculate is the > > date theMax rain occurred. I am OK with the formula to obtain the > > MAX but I needhelp in constructing a formula to get the > > corresponding date. > > I had though the best would be Index and Match but no matter how I > > enterit I cannot get the date listed under the Date column of 2020 > > or 2021,never mind actually retrieving the year from the same > > column as the datethe originated. > > =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first > > dateunder Highest Monthly > > Strange. I give a completely different result, so obviously my > spreadsheetisn't identical to yours.In which cell did you put this > formula and what result did you expect? > > I have looked aver tutorial and their mothers trying to find out > > whatranges to insert into the Index(Match()) formula with ZERO > > success. > > I am manually entering the dates under each year when the max value > > isrevealed by my formula. Would love to have it automated but my > > entirespreadsheet covers over 400 rows and more than 52 columns > > resulting in26 tabs of graphs from the Data sheet. > > Is my data in the wrong order i.e. should the rainfall value column > > bebefore the Date it occurred? > > I do not understand what ranges need to consist of when using > > Index/Match. > > Can someone point me to a decent tutorial explaining the different > > termsi.e. Reference, Row, Column, Range, Search Criterion, Lookup > > array. > > 20202021Highest > > MonthlyDate2020 Rain Date2021 > > Rain DateRain01/19 9,5 01/15 3 > > 2020/01/19 9,502/16 1,5 02/14 3,5 > > 2021/02/14 3,503/25 3,5 03/14 19 > > 2021/03/14 1904/11 20 04/26 7 > >2020/04/11 2005/28 27,505/20 43 > > 2021/05/20 4306/11 26 #N/A0 > > 007/09 85,5#N/
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: > Hi Johnny, > > On 2021/05/28 23:44, Johnny Rosenberg wrote: > > Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < > > hyl...@conacher.co.za>: > > > >> Hi, > >> > >> I have LO 7.0.6.2 and am battling with understanding which formula to > >> use as well as the syntax for that formula. > >> > >> I am aware of the availability of vlookup, hlookup, Index/Match formula > >> and have settled I think on the right one i.e. Index/Match > >> > >> Below is a portion of my spreadsheet that is divided as below with a > >> blank column between each year. What I want to calculate is the date the > >> Max rain occurred. I am OK with the formula to obtain the MAX but I need > >> help in constructing a formula to get the corresponding date. > >> > >> I had though the best would be Index and Match but no matter how I enter > >> it I cannot get the date listed under the Date column of 2020 or 2021, > >> never mind actually retrieving the year from the same column as the date > >> the originated. > >> > >> =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date > >> under Highest Monthly > >> > > > > Strange. I give a completely different result, so obviously my > spreadsheet > > isn't identical to yours. > > In which cell did you put this formula and what result did you expect? > > > >> > >> I have looked aver tutorial and their mothers trying to find out what > >> ranges to insert into the Index(Match()) formula with ZERO success. > >> > >> I am manually entering the dates under each year when the max value is > >> revealed by my formula. Would love to have it automated but my entire > >> spreadsheet covers over 400 rows and more than 52 columns resulting in > >> 26 tabs of graphs from the Data sheet. > >> > >> Is my data in the wrong order i.e. should the rainfall value column be > >> before the Date it occurred? > >> > >> I do not understand what ranges need to consist of when using > Index/Match. > >> > >> Can someone point me to a decent tutorial explaining the different terms > >> i.e. Reference, Row, Column, Range, Search Criterion, Lookup array. > >> > >> 20202021Highest Monthly > >> Date2020 Rain Date2021 Rain DateRain > >> 01/19 9,5 01/15 3 2020/01/19 9,5 > >> 02/16 1,5 02/14 3,5 2021/02/14 3,5 > >> 03/25 3,5 03/14 19 2021/03/14 19 > >> 04/11 20 04/26 7 2020/04/11 20 > >> 05/28 27,505/20 43 2021/05/20 43 > >> 06/11 26 #N/A0 0 > >> 07/09 85,5#N/A0 0 > >> 08/28 35 #N/A0 0 > >> 09/02 21 #N/A0 0 > >> 10/28 15 #N/A0 0 > >> 11/06 25 #N/A0 0 > >> 12/26 2 #N/A0 0 > >> > >> If you want the entire spreadsheet it is available on direct request, > >> but ultimately I would like to understand how it works. > >> > >> This will at least enable to use the formula successfully on newer > >> versions on LO. > >> > >> Regards > >> Hylton > >> > >> > > I'm not sure what you are trying to do here, so I'll just guess. Just > > ignore me if I'm totally wrong. > > So first, I tried to create a spreadsheet following the text above, so > this > > is what my spreadsheet looks like: > > Row 1 and two are just headers. > > Column A is dates for 2020 formatted as Month/Day. > > Column B is amount of rain in some unit, not sure which one, so I assume > > mm, since that's what we use where I live, and it doesn't matter for this > > question anyway. > > Column C is empty. > > Column D is dates for 2021 formatted as Month/Day. > > Column E is the amount of rain for 2021. > > Column F is empty. > > Column G is the column that contains the dates for each month for the > > respective year with the highest amount of rain, and it's also the column > > that you wish to automate, is that right? > > Column H is the highest value of rain in columns B and E for each row. > > > > Right so far? > > > > If so, you want column G to display the date for each maximum value in > the > > B and E column per row, right? Column H is already figured out, so for > > instance, H3 contains the following: > > =MAX(B3;E3) > > > > Then you want to automatically display the datum of which this occured, > or > > just the year? Well, date or year is only a question about formatting, so > > let's just leave it to be formatted later. I guess you know how to do > cell > > formats and styles anyway. > > maybe I totally misunderstood the question, but if not, you don't need > > neither INDEX no
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Hi Johnny, On 2021/05/28 23:44, Johnny Rosenberg wrote: Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: Hi, I have LO 7.0.6.2 and am battling with understanding which formula to use as well as the syntax for that formula. I am aware of the availability of vlookup, hlookup, Index/Match formula and have settled I think on the right one i.e. Index/Match Below is a portion of my spreadsheet that is divided as below with a blank column between each year. What I want to calculate is the date the Max rain occurred. I am OK with the formula to obtain the MAX but I need help in constructing a formula to get the corresponding date. I had though the best would be Index and Match but no matter how I enter it I cannot get the date listed under the Date column of 2020 or 2021, never mind actually retrieving the year from the same column as the date the originated. =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date under Highest Monthly Strange. I give a completely different result, so obviously my spreadsheet isn't identical to yours. In which cell did you put this formula and what result did you expect? I have looked aver tutorial and their mothers trying to find out what ranges to insert into the Index(Match()) formula with ZERO success. I am manually entering the dates under each year when the max value is revealed by my formula. Would love to have it automated but my entire spreadsheet covers over 400 rows and more than 52 columns resulting in 26 tabs of graphs from the Data sheet. Is my data in the wrong order i.e. should the rainfall value column be before the Date it occurred? I do not understand what ranges need to consist of when using Index/Match. Can someone point me to a decent tutorial explaining the different terms i.e. Reference, Row, Column, Range, Search Criterion, Lookup array. 20202021Highest Monthly Date2020 Rain Date2021 Rain DateRain 01/19 9,5 01/15 3 2020/01/19 9,5 02/16 1,5 02/14 3,5 2021/02/14 3,5 03/25 3,5 03/14 19 2021/03/14 19 04/11 20 04/26 7 2020/04/11 20 05/28 27,505/20 43 2021/05/20 43 06/11 26 #N/A0 0 07/09 85,5#N/A0 0 08/28 35 #N/A0 0 09/02 21 #N/A0 0 10/28 15 #N/A0 0 11/06 25 #N/A0 0 12/26 2 #N/A0 0 If you want the entire spreadsheet it is available on direct request, but ultimately I would like to understand how it works. This will at least enable to use the formula successfully on newer versions on LO. Regards Hylton I'm not sure what you are trying to do here, so I'll just guess. Just ignore me if I'm totally wrong. So first, I tried to create a spreadsheet following the text above, so this is what my spreadsheet looks like: Row 1 and two are just headers. Column A is dates for 2020 formatted as Month/Day. Column B is amount of rain in some unit, not sure which one, so I assume mm, since that's what we use where I live, and it doesn't matter for this question anyway. Column C is empty. Column D is dates for 2021 formatted as Month/Day. Column E is the amount of rain for 2021. Column F is empty. Column G is the column that contains the dates for each month for the respective year with the highest amount of rain, and it's also the column that you wish to automate, is that right? Column H is the highest value of rain in columns B and E for each row. Right so far? If so, you want column G to display the date for each maximum value in the B and E column per row, right? Column H is already figured out, so for instance, H3 contains the following: =MAX(B3;E3) Then you want to automatically display the datum of which this occured, or just the year? Well, date or year is only a question about formatting, so let's just leave it to be formatted later. I guess you know how to do cell formats and styles anyway. maybe I totally misunderstood the question, but if not, you don't need neither INDEX nor MATCH for this. Here's my cell formula in G3, for instance: =IF(B3>E3;A3;D3) So it there was more rain in 2020 than in 2021, display the date in column A, otherwise display the date in column D. If you want to fill further down to future dates and only show the values when the rest of the row is completely entered, you could add another test, like this: =IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3)) Then you could just fill down and only the relevant cells will display something and the rest will be blank. You can do the same thing t
Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match
Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) < hyl...@conacher.co.za>: > Hi, > > I have LO 7.0.6.2 and am battling with understanding which formula to > use as well as the syntax for that formula. > > I am aware of the availability of vlookup, hlookup, Index/Match formula > and have settled I think on the right one i.e. Index/Match > > Below is a portion of my spreadsheet that is divided as below with a > blank column between each year. What I want to calculate is the date the > Max rain occurred. I am OK with the formula to obtain the MAX but I need > help in constructing a formula to get the corresponding date. > > I had though the best would be Index and Match but no matter how I enter > it I cannot get the date listed under the Date column of 2020 or 2021, > never mind actually retrieving the year from the same column as the date > the originated. > > =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date > under Highest Monthly > Strange. I give a completely different result, so obviously my spreadsheet isn't identical to yours. In which cell did you put this formula and what result did you expect? > > I have looked aver tutorial and their mothers trying to find out what > ranges to insert into the Index(Match()) formula with ZERO success. > > I am manually entering the dates under each year when the max value is > revealed by my formula. Would love to have it automated but my entire > spreadsheet covers over 400 rows and more than 52 columns resulting in > 26 tabs of graphs from the Data sheet. > > Is my data in the wrong order i.e. should the rainfall value column be > before the Date it occurred? > > I do not understand what ranges need to consist of when using Index/Match. > > Can someone point me to a decent tutorial explaining the different terms > i.e. Reference, Row, Column, Range, Search Criterion, Lookup array. > > 20202021Highest Monthly > Date2020 Rain Date2021 Rain DateRain > 01/19 9,5 01/15 3 2020/01/19 9,5 > 02/16 1,5 02/14 3,5 2021/02/14 3,5 > 03/25 3,5 03/14 19 2021/03/14 19 > 04/11 20 04/26 7 2020/04/11 20 > 05/28 27,505/20 43 2021/05/20 43 > 06/11 26 #N/A0 0 > 07/09 85,5#N/A0 0 > 08/28 35 #N/A0 0 > 09/02 21 #N/A0 0 > 10/28 15 #N/A0 0 > 11/06 25 #N/A0 0 > 12/26 2 #N/A0 0 > > If you want the entire spreadsheet it is available on direct request, > but ultimately I would like to understand how it works. > > This will at least enable to use the formula successfully on newer > versions on LO. > > Regards > Hylton > > I'm not sure what you are trying to do here, so I'll just guess. Just ignore me if I'm totally wrong. So first, I tried to create a spreadsheet following the text above, so this is what my spreadsheet looks like: Row 1 and two are just headers. Column A is dates for 2020 formatted as Month/Day. Column B is amount of rain in some unit, not sure which one, so I assume mm, since that's what we use where I live, and it doesn't matter for this question anyway. Column C is empty. Column D is dates for 2021 formatted as Month/Day. Column E is the amount of rain for 2021. Column F is empty. Column G is the column that contains the dates for each month for the respective year with the highest amount of rain, and it's also the column that you wish to automate, is that right? Column H is the highest value of rain in columns B and E for each row. Right so far? If so, you want column G to display the date for each maximum value in the B and E column per row, right? Column H is already figured out, so for instance, H3 contains the following: =MAX(B3;E3) Then you want to automatically display the datum of which this occured, or just the year? Well, date or year is only a question about formatting, so let's just leave it to be formatted later. I guess you know how to do cell formats and styles anyway. maybe I totally misunderstood the question, but if not, you don't need neither INDEX nor MATCH for this. Here's my cell formula in G3, for instance: =IF(B3>E3;A3;D3) So it there was more rain in 2020 than in 2021, display the date in column A, otherwise display the date in column D. If you want to fill further down to future dates and only show the values when the rest of the row is completely entered, you could add another test, like this: =IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3)) Then you could just fill down and only the relevant cells will display something and the r
Re: [libreoffice-users] Calc formula tool tips - possible to turn off?
Gordon, perhaps you could add your comment to the bug report, the developers might appreciate the input / feedback. I also prefer the second choice and like your idea of a similar option for tool tips. On Mon, Aug 15, 2016 at 12:10 AM, gordon cooper wrote: > I like your second suggested option Bruce, i.e. None : Standard : Extended. > > It would be a good option for all tooltips. > > Gordon. > > > > On 15/08/16 13:04, Bruce Hohl wrote: > >> My search for a way to control the formula tips also was unsuccessful so I >> added the following enhancement requests: >> https://bugs.documentfoundation.org/show_bug.cgi?id=101512 >> >> On Sun, Aug 14, 2016 at 7:00 PM, Remy Gauthier >> wrote: >> >> >> > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns > ubscribe/ > 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 > -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula tool tips - possible to turn off?
I like your second suggested option Bruce, i.e. None : Standard : Extended. It would be a good option for all tooltips. Gordon. On 15/08/16 13:04, Bruce Hohl wrote: My search for a way to control the formula tips also was unsuccessful so I added the following enhancement requests: https://bugs.documentfoundation.org/show_bug.cgi?id=101512 On Sun, Aug 14, 2016 at 7:00 PM, Remy Gauthier wrote: -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula tool tips - possible to turn off?
My search for a way to control the formula tips also was unsuccessful so I added the following enhancement requests: https://bugs.documentfoundation.org/show_bug.cgi?id=101512 On Sun, Aug 14, 2016 at 7:00 PM, Remy Gauthier wrote: > Hello, > > No, I do not know of any way to turn the formula tool tips off. I even had > a look at the advanced settings (Tools... Options... LibreOffice... > Advanced... Advanced Settings) and I could not find anything. > > Rgds, > > Rémy Gauthier. > > Le samedi 13 août 2016 à 15:42 -0400, Bruce Hohl a écrit : > > Does anyone know if it is possible to turn off the formula tool tips in > Calc? > > > -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula tool tips - possible to turn off?
Hello, No, I do not know of any way to turn the formula tool tips off. I even had a look at the advanced settings (Tools... Options... LibreOffice... Advanced... Advanced Settings) and I could not find anything. Rgds, Rémy Gauthier. Le samedi 13 août 2016 à 15:42 -0400, Bruce Hohl a écrit : > > Does anyone know if it is possible to turn off the formula tool tips in > Calc? > -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula syntax assistance
At 19:42 19/12/2015 +0200, Hylton Conacher wrote: Running :O 5.0.3.2 on IMac and have seen there is a 5.0.4 update, however, perhaps my issue does not require an update. Anyway, the formula involves COUNTIF with the search range on another sheet and the search criteria in a cell adjacent to the countif formula. =COUNTIF(Data.$C$4:C$492,$B17) Each search criteria (B17), is text consisting of spaces, numbers and special characters [e.g.] BLUE GUM LANE (NO'S 1 - 17) On my version of LO this formula displays a zero as the count of the search criteria however on reviewing the Data sheet, I can identify at least one exact replica within range, yet it is not counted? Any ideas to get the correct answer of the number of times a field appears? There are simple ways to debug problems such as this - which you can do with your actual spreadsheet but you deprive anyone offering to help you from doing! o Set up a model spreadsheet with very simple entries so that something works - to establish that you understand exactly what COUNTIF() does. o Make a copy of your real spreadsheet and simplify it - removing material and simplifying the data - until it works. Whatever you changed last to make the function spring into life will be the culprit. As the help text explains about COUNTIF(), "[t]he search supports regular expressions." With regular expression enabled, the parentheses in your example data are interpreted as marking off the enclosed text as a reference. This can be referred to later in a search string (or in a replacement string, where relevant). This means that the parentheses are punctuation within the search string and not part of it: your example string would match "BLUE GUM LANE NO'S 1 - 17" but not "BLUE GUM LANE (NO'S 1 17)". If this is the problem, there are two alternative techniques to solve it. Either: o Disable regular expressions in formulae by removing the tick from Tools | Options... | LibreOffice Calc | Calculate | Enable regular expressions in formulae. (That will be Preferences... on the Mac, won't it?) Or: o Escape the parentheses by preceding them by backslashes in the search string: BLUE GUM LANE \(NO'S 1 - 17\) - so that they are treated as nothing more than the simple text characters you mean them to be. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula syntax assistance
-Original Message- From: "Hylton Conacher (ZR1HPC)" To: LibreOffice Users Sent: Sat, 19 Dec 2015 9:44 Subject: [libreoffice-users] Calc formula syntax assistance Hi, Running :O 5.0.3.2 on IMac and have seen there is a 5.0.4 update, however, perhaps my issue does not require an update. Anyway, the formula involves COUNTIF with the search range on another sheet and the search criteria in a cell adjacent to the countif formula. =COUNTIF(Data.$C$4:C$492,$B17) [Did you try this? =COUNTIF(Data.$C$4:$C$492,.$b17) Note, I made two adjustments to your formula • Absolute column reference for the end of the range (your probable intent) • Sheet reference in the search criteria The second change is likely to resolve your problem. If not, then you may have one or more cells with extraneous space(s). If the extraneous space(s) are in the search criteria you can add the TRIM function call there. -- jl] Each search criteria (B17), is text consisting of spaces, numbers and special characters i.e. BLUE GUM LANE (NO'S 1 – 17) On my version of LO this formula displays a zero as the count of the search criteria however on reviewing the Data sheet, I can identify at least one exact replica within range, yet it is not counted? Any ideas to get the correct answer of the number of times a field appears? Regards Hylton -- Jim -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
Brian Barker wrote: > At 10:28 11/03/2015 +1300, David Love wrote: > > What I want, in simple terms, is to determine the average number of > > steps I am taking every week. Hopefully, this explanation will answer > > the questions reaided. > > Not really! > > I think you've confirmed that the 1 figure is irrelevant in the > calculation. But your earlier statements that "if day 1 reaches a > production of 10,000 units I want this figure entered" and "If days > 1+2+3+4 total, say, 42,500 I want ... to show the average of 10,265" > both indicate that the required result does not depend on other daily > values. These contradict the idea that you simply want the true average > of all seven values. > > Unsurprisingly, you can calculate averages using the AVERAGE() function. > > Incidentally, doesn't all this cry out for a table instead of a list? > Put a week date (or date range) in column A, as you suggest. Enter your > data for the days of that week in columns B to H of the same row. It's > then particularly simple to calculate averages in column I. At the > bottom of the columns, you could calculate averages for each weekday - > and perhaps discover that you need to be more active on Thursdays! Ha! I constructed a table, as you suggested and, yes, it does meet my requirements. Thanks for the suggestion, Brian. David -- David Love Dogs wag their tail with their whole heart. -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
At 10:28 11/03/2015 +1300, David Love wrote: What I want, in simple terms, is to determine the average number of steps I am taking every week. Hopefully, this explanation will answer the questions reaided. Not really! I think you've confirmed that the 1 figure is irrelevant in the calculation. But your earlier statements that "if day 1 reaches a production of 10,000 units I want this figure entered" and "If days 1+2+3+4 total, say, 42,500 I want ... to show the average of 10,265" both indicate that the required result does not depend on other daily values. These contradict the idea that you simply want the true average of all seven values. Unsurprisingly, you can calculate averages using the AVERAGE() function. Incidentally, doesn't all this cry out for a table instead of a list? Put a week date (or date range) in column A, as you suggest. Enter your data for the days of that week in columns B to H of the same row. It's then particularly simple to calculate averages in column I. At the bottom of the columns, you could calculate averages for each weekday - and perhaps discover that you need to be more active on Thursdays! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
David Love wrote: > Using LinuxMint v17.1 Rebecca with MATE DE and LibreOffic v4.2.7.2 Build > ID 420M0(Build2) > > I have a three column spreadsheet. > > Column A. Date - The dates are consecutive > > Column B. Production - The Production has a daily target of 10,000 units > and each production period is of seven days. > > Column C. a formula every seventh cell. > > Is it possible to construct a formula which will show in the seventh > cell of column C the average for the number of days of production i.e. > if day 1 reaches a production of 10,000 units I want this figure entered > into the seventh cell in column C. If days 1+2+3+4 total, say, 42,500 I > want the seventh cell in column C to show the average of 10,265. In > other words I want the average shown for the actual days of production > each seven day period. Thank you Steve, Brian and Johnny for your prompt replies. I will work through the suggestions and report back. I should, however, make a confession :-) I used the word "Production" as I wanted members to see this as a "business" rather than a "personal" question. In reality, Column A is the numbers of steps I take each day. Ten thousand is considered to be the number necessary to keep healthy. What I want, in simple terms, is to determine the average number of steps I am taking every week. Hopefully, this explanation will answer the questions reaided. DL -- David Love Bachelor: A guy who has cheated a woman out of a divorce. -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
Wow, excellent suggestions Brian. Your ideas are always spot on. Thanks. Carl On 3/10/15 4:08 AM, Brian Barker wrote: At 19:20 10/03/2015 +1300, David Love wrote: I have a three column spreadsheet. Column A. Date - The dates are consecutive Column B. Production - The Production has a daily target of 10,000 units and each production period is of seven days. Column C. a formula every seventh cell. Is it possible to construct a formula which will show in the seventh cell of column C the average for the number of days of production i.e. if day 1 reaches a production of 10,000 units I want this figure entered into the seventh cell in column C. Hold on! How do you know this is going to be the average for the week? Do your workers celebrate reaching the daily target and take the rest of the week off? Surely they need to attempt the same daily target on each of the next six days? Or do you mean that 1 is the *weekly* target? If so, what happens when it is reached? Does production automatically stop to prevent its being exceeded? Or could some weeks exceed 1 - even by accident? If days 1+2+3+4 total, say, 42,500 I want the seventh cell in column C to show the average of 10,265. In other words I want the average shown for the actual days of production each seven day period. So 1 isn't a weekly limit. In that case, what is the significance of the 1? If four days exceed 1, as here, it's not a daily limit either: at least one of these days must have exceeded 1. I'm beginning to suspect that it has no significance for the calculation (so you didn't need to tell us): it may be of interest only to the workers' supervisor in interpreting the results. You can find the average of non-negative values (i.e. non-zero values in your case, assuming production cannot be negative) by putting in, say, C7: =AVERAGEIF(B1:B7;">0") If you copy this and paste it into every seventh row of column C, you will have what you need. But that leaves you with the rather messy requirement to paste separately into every seventh row - a process very prone to error. Instead, in C7 try: =IF(MOD(ROW();7)=0;AVERAGEIF(B1:B7;">0");"") ROW() returns the current row number. The MOD() function returns the remainder on dividing by 7. If this is zero - as it will be for row 7 and every seventh row thereafter - the required average is shown; otherwise the null string ensures that there is no display in the cell. You can copy or fill this down column C without the same risk of error as the previous suggestion. Note that comparing the result of the MOD() function with zero will show results in rows 7, 14, 21, and so on. You will have to change the "0" to "1" to show results instead in rows 8, 15, 22, and so on - and similarly for other possibilities. Once you have found the appropriate value, you can fill the formula containing it down the column. Is it possible for there to be no production at all in a particular week? The above formula, in evaluating the average of no values, attempts to divide by zero and displays #DIV/0! . You could test for this and avoid it in various ways. If days with no production have empty cells in column B, =IF(AND(COUNT(B1:B7)>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"") would suffice. If they have (or may have) explicit zero values, try: =IF(AND(COUNTIF(B1:B7;">0")>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"") I trust this helps. Brian Barker -- Carl Paulsen 8 Hamilton Street Dover, NH 03820 (603) 749-2310 -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
Ok. OP has stated "each production period is of seven days" so answers my first question. On 2015-03-10 19:20, David Love wrote: Using LinuxMint v17.1 Rebecca with MATE DE and LibreOffic v4.2.7.2 Build ID 420M0(Build2) I have a three column spreadsheet. Column A. Date - The dates are consecutive Column B. Production - The Production has a daily target of 10,000 units and each production period is of seven days. Column C. a formula every seventh cell. Is it possible to construct a formula which will show in the seventh cell of column C the average for the number of days of production i.e. if day 1 reaches a production of 10,000 units I want this figure entered into the seventh cell in column C. If days 1+2+3+4 total, say, 42,500 I want the seventh cell in column C to show the average of 10,265. In other words I want the average shown for the actual days of production each seven day period. DL -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
At 19:20 10/03/2015 +1300, David Love wrote: I have a three column spreadsheet. Column A. Date - The dates are consecutive Column B. Production - The Production has a daily target of 10,000 units and each production period is of seven days. Column C. a formula every seventh cell. Is it possible to construct a formula which will show in the seventh cell of column C the average for the number of days of production i.e. if day 1 reaches a production of 10,000 units I want this figure entered into the seventh cell in column C. Hold on! How do you know this is going to be the average for the week? Do your workers celebrate reaching the daily target and take the rest of the week off? Surely they need to attempt the same daily target on each of the next six days? Or do you mean that 1 is the *weekly* target? If so, what happens when it is reached? Does production automatically stop to prevent its being exceeded? Or could some weeks exceed 1 - even by accident? If days 1+2+3+4 total, say, 42,500 I want the seventh cell in column C to show the average of 10,265. In other words I want the average shown for the actual days of production each seven day period. So 1 isn't a weekly limit. In that case, what is the significance of the 1? If four days exceed 1, as here, it's not a daily limit either: at least one of these days must have exceeded 1. I'm beginning to suspect that it has no significance for the calculation (so you didn't need to tell us): it may be of interest only to the workers' supervisor in interpreting the results. You can find the average of non-negative values (i.e. non-zero values in your case, assuming production cannot be negative) by putting in, say, C7: =AVERAGEIF(B1:B7;">0") If you copy this and paste it into every seventh row of column C, you will have what you need. But that leaves you with the rather messy requirement to paste separately into every seventh row - a process very prone to error. Instead, in C7 try: =IF(MOD(ROW();7)=0;AVERAGEIF(B1:B7;">0");"") ROW() returns the current row number. The MOD() function returns the remainder on dividing by 7. If this is zero - as it will be for row 7 and every seventh row thereafter - the required average is shown; otherwise the null string ensures that there is no display in the cell. You can copy or fill this down column C without the same risk of error as the previous suggestion. Note that comparing the result of the MOD() function with zero will show results in rows 7, 14, 21, and so on. You will have to change the "0" to "1" to show results instead in rows 8, 15, 22, and so on - and similarly for other possibilities. Once you have found the appropriate value, you can fill the formula containing it down the column. Is it possible for there to be no production at all in a particular week? The above formula, in evaluating the average of no values, attempts to divide by zero and displays #DIV/0! . You could test for this and avoid it in various ways. If days with no production have empty cells in column B, =IF(AND(COUNT(B1:B7)>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"") would suffice. If they have (or may have) explicit zero values, try: =IF(AND(COUNTIF(B1:B7;">0")>0;MOD(ROW();7)=0);AVERAGEIF(B1:B7;">0");"") I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula ...
Hi. Yes it is. May some clarification though. Is it the prior 7 days like a rolling average or the days say Monday to Sunday, every Sunday. Are you only averaging days where production exceeds 1, so in your 1st example day 1 is the only day exceeding 1 so the average=1 and in your second example 4 days exceed 1 so those 4 are averaged. Steve On 2015-03-10 19:20, David Love wrote: Using LinuxMint v17.1 Rebecca with MATE DE and LibreOffic v4.2.7.2 Build ID 420M0(Build2) I have a three column spreadsheet. Column A. Date - The dates are consecutive Column B. Production - The Production has a daily target of 10,000 units and each production period is of seven days. Column C. a formula every seventh cell. Is it possible to construct a formula which will show in the seventh cell of column C the average for the number of days of production i.e. if day 1 reaches a production of 10,000 units I want this figure entered into the seventh cell in column C. If days 1+2+3+4 total, say, 42,500 I want the seventh cell in column C to show the average of 10,265. In other words I want the average shown for the actual days of production each seven day period. DL -- To unsubscribe e-mail to: users+unsubscr...@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] [Calc] Formula and Find & Replace menu bar function
W dniu 2013-11-16 20:35, Brian Barker pisze: o Type zero in an empty cell. o Copy the zero. o Select the (partially filled) cell range you wish to treat. o Go to Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V). o In the Paste Special dialogue, under Operations, select Add. o (Delete the zero.) Your approach Brian is very nice also. Thanks for you help. Regards, gordom -- To unsubscribe e-mail to: users+unsubscr...@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] [Calc] Formula and Find & Replace menu bar function
W dniu 2013-11-16 17:54, Paul pisze: Sure. Select the cell range you want to do the substitution on (otherwise it will do find and replace on the whole sheet!), and go to "Find & Replace". In the dialog, make sure the "Current selection only" option under "Other Options" is checked. Leave the "Search for" field empty, and put a zero in the "Replace with" field, then click "Replace All". Simple. I'm really impressed by the simplicity. It's brilliant :). Thanks for your help. Note that for some reason, this always seems to replace the blank cells with the text string "0", I can't seem to get it to replace with an actual zero. Maybe someone else knows: is there an opposite of the apostrophe "operator", one that marks input as a number? Or some way to tell "Find & Replace" to replace with numbers instead of text? You can try regex Find: ^.*$ Replace: & For now on the 0 should be treated as number and not a text. Regards, gordom -- To unsubscribe e-mail to: users+unsubscr...@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] [Calc] Formula and Find & Replace menu bar function
At 15:02 16/11/2013 +0100, Gordom Noname wrote: Some columns in my spreadsheet are filed with data only partly (some cells are blank). I need to put "0" value in this empty rows. [...] But I was wondering if this task can be accomplished without adding (even temporarily) additional column. o Type zero in an empty cell. o Copy the zero. o Select the (partially filled) cell range you wish to treat. o Go to Edit | Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V). o In the Paste Special dialogue, under Operations, select Add. o (Delete the zero.) Alternatively, you may want to consider the simpler solution of modifying any formulae that refer to these cells to treat empty cell data as if it were zero. It's generally possible to do this. But that may not be suitable if the columns you describe are part of a final printout, say, of your spreadsheet. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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] [Calc] Formula and Find & Replace menu bar function
Hi Gordom, On Sat, 16 Nov 2013 15:02:09 +0100 gordom wrote: > Hallo everyone. > Some columns in my spreadsheet are filed with data only partly (some > cells are blank). I need to put "0" value in this empty rows. Of > course I can add an extra column and use a formula like this one for > example =IF(ISBLANK(cell_address);"0";cell_address) Note that this puts a text string of "0" in the cell, not the number zero. For that you would want the double quotes around the zero removed, like so: =IF(ISBLANK(cell_address);0;cell_address) > But I was wondering if this task can be accomplished without adding > (even temporarily) additional column. Is there a way to use formula > (like the above one or any other) together with Find & Replace > function in the Edit menu? Sure. Select the cell range you want to do the substitution on (otherwise it will do find and replace on the whole sheet!), and go to "Find & Replace". In the dialog, make sure the "Current selection only" option under "Other Options" is checked. Leave the "Search for" field empty, and put a zero in the "Replace with" field, then click "Replace All". Simple. Note that for some reason, this always seems to replace the blank cells with the text string "0", I can't seem to get it to replace with an actual zero. Maybe someone else knows: is there an opposite of the apostrophe "operator", one that marks input as a number? Or some way to tell "Find & Replace" to replace with numbers instead of text? -- To unsubscribe e-mail to: users+unsubscr...@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] Calc formula for US Thanksgiving date
Figured it out by using an Excel formula;Thanksgiving Day == =DATE(E5,11,29)-WEEKDAY(DATE(E5,11,24)) In this case “E5” is the cell where the year number is located) Thanks, everyone, for your efforts. On 12/03/2012 10:47 AM, Dennis E. Hamilton wrote: You should get a lot of responses on this. US Thanksgiving is always the 4th Thursday in November. So, the first thing you want to do is find out what day of the week November 1, , in a particular year is. (There are functions for this). Then find out how many days later the first Thursday is. (In 2012, the answer is 0.) You'll have to work this out based on how day-of-week is counted. Add that number of days and 21 more to the November 1, date value. That will show you the date of Thanksgiving in year . (For 2012, the correct answer is November 22, 2012.) - Dennis -Original Message- From: . [mailto:l...@ebookring.net] Sent: Monday, December 03, 2012 02:59 To:users@global.libreoffice.orgSubject: [libreoffice-users] Calc formula for US Thanksgiving date I need a formula for Calc to determine the US Thanksgiving date for a given year. Thanks--www.eBookRing.netThe designer and maker of the original eBookRing The perfect stand for eReaders, iPads, iPhones, tablet computers and other electronic devices. Patent Pending -- 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] Calc formula for US Thanksgiving date
2012/12/3 Brian Barker : > At 05:58 03/12/2012 -0500, Dottie Noname wrote: >> >> I need a formula for Calc to determine the US Thanksgiving date for a >> given year. > > > I could have answered this sooner but resisted at first because of the > unfriendly - frankly selfish - habit of suppressing any name. How is anyone > to refer to you? How is anyone to search for your previous messages? It > doesn't matter if you use your real name or not: call yourself Albert > Einstein or Mickey Mouse or Algernon Cholmondeley-Featherstonehaugh if you > wish, but please remember that you are asking for help and grant everyone > the courtesy of giving yourself some sort of usable handle. Thank you. > > > It's not exactly clear what you want here: for this year, for example, would > you like the result to be the integer 22 or the date value 22 November 2012? > (I'm guessing probably the latter.) > > For the integer: > =29-WEEKDAY(DATE(Xn;11;3)) > or for the date value: > =DATE(Xn;11;29)-WEEKDAY(DATE(Xn;11;3)) He he he… that was a lot more elegant than my version! Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ > > In each case, Xn is the cell with the relevant year. In the second case, > the result cell will have to be appropriately formatted in order to display > as you will wish. > > I trust this helps. > > Brian Barker > > > > -- > 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 > -- 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] Calc formula for US Thanksgiving date
At 05:58 03/12/2012 -0500, Dottie Noname wrote: I need a formula for Calc to determine the US Thanksgiving date for a given year. I could have answered this sooner but resisted at first because of the unfriendly - frankly selfish - habit of suppressing any name. How is anyone to refer to you? How is anyone to search for your previous messages? It doesn't matter if you use your real name or not: call yourself Albert Einstein or Mickey Mouse or Algernon Cholmondeley-Featherstonehaugh if you wish, but please remember that you are asking for help and grant everyone the courtesy of giving yourself some sort of usable handle. Thank you. It's not exactly clear what you want here: for this year, for example, would you like the result to be the integer 22 or the date value 22 November 2012? (I'm guessing probably the latter.) For the integer: =29-WEEKDAY(DATE(Xn;11;3)) or for the date value: =DATE(Xn;11;29)-WEEKDAY(DATE(Xn;11;3)) In each case, Xn is the cell with the relevant year. In the second case, the result cell will have to be appropriately formatted in order to display as you will wish. I trust this helps. Brian Barker -- 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] Calc formula for US Thanksgiving date
2012/12/3 Dennis E. Hamilton : > You should get a lot of responses on this. Actually, I responded quite a few times, but unfortunately I forgot (as usual) about the ”new” nad odd behaviour of this list, so I only responded directly to the OP. That was not my intention, though, so here is a short summary, just in case someone happens to be interested: A1: Year B1: =IF(WEEKDAY(DATE(A1;10;31);2)<4;4;11)-WEEKDAY(DATE(A1;10;31);2)+21 The WEEKDAY thing is always calculated twice, which could feel a bit unnecessary. Could be avoided by using a cell for subtotal: A1: Year B1: WEEKDAY(DATE(A1;10;31);2) C1: =IF(B1<4;4;11)-B1+21 Here's a macro that does the whole thing: REM * BASIC * Function Thanksgiving(Year As Long) As Date Dim DayOfWeek As Long DayOfWeek=WeekDay(DateSerial(Year,10,31)) If DayOfWeek<5 Then Thanksgiving=DateSerial(Year,11,26-DayOfWeek) Else Thanksgiving=DateSerial(Year,11,33-DayOfWeek) EndIf End Function REM * END OF BASIC * A1: Year B1: =THANKSGIVING(A1) > > US Thanksgiving is always the 4th Thursday in November. > > So, the first thing you want to do is find out what day of the week November > 1, , in a particular year is. (There are functions for this). I found the whole thing a bit easier if I started by finding out the weekday of 31 October, but I guess it's just another way of doing it. > > Then find out how many days later the first Thursday is. (In 2012, the > answer is 0.) You'll have to work this out based on how day-of-week is > counted. > > Add that number of days and 21 more to the November 1, date value. That > will show you the date of Thanksgiving in year . (For 2012, the correct > answer is November 22, 2012.) > > - Dennis > > -Original Message- > From: . [mailto:l...@ebookring.net] > Sent: Monday, December 03, 2012 02:59 > To: users@global.libreoffice.org > Subject: [libreoffice-users] Calc formula for US Thanksgiving date > > I need a formula for Calc to determine the US Thanksgiving date for a > given year. > > Thanks > > -- > www.eBookRing.net > > The designer and maker of the original eBookRing > The perfect stand for eReaders, iPads, iPhones, tablet computers and other > electronic devices. > > Patent Pending > > > -- > 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 > > > -- > 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 -- 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] Calc formula for US Thanksgiving date
You should get a lot of responses on this. US Thanksgiving is always the 4th Thursday in November. So, the first thing you want to do is find out what day of the week November 1, , in a particular year is. (There are functions for this). Then find out how many days later the first Thursday is. (In 2012, the answer is 0.) You'll have to work this out based on how day-of-week is counted. Add that number of days and 21 more to the November 1, date value. That will show you the date of Thanksgiving in year . (For 2012, the correct answer is November 22, 2012.) - Dennis -Original Message- From: . [mailto:l...@ebookring.net] Sent: Monday, December 03, 2012 02:59 To: users@global.libreoffice.org Subject: [libreoffice-users] Calc formula for US Thanksgiving date I need a formula for Calc to determine the US Thanksgiving date for a given year. Thanks -- www.eBookRing.net The designer and maker of the original eBookRing The perfect stand for eReaders, iPads, iPhones, tablet computers and other electronic devices. Patent Pending -- 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 -- 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] Calc Formula Help
Hi,
Michael D. Setzer II schrieb:
If I understand what you are trying to do.
I put this data in Column A and Column B.
COLACOLB
A 7
G 87
T 1
A 1
G 6
T 7
A 43
G 3
T 2
I put the criteria in G1 and G2
COLA
A
Then used the formula to get the value.
=DMIN(A1:B10,"COLB",G1:G2)
That just does it for A, but you could easily setup other criteria to
get the values for the tother letters.
If there might be the need to change the criteria, the above is a
possible way. If you now, that you only need a fix criteria, then you
can include it in the formula using an inline matrix.
=DMIN(A1:B10,"COLB",{"COLA";"A"}). The delimiter between matrix columns
and matrix rows is set in Tools > Options > Calc > Formula. I have
assumed a semicolon as row delimiter here.
Kind regards
Regina
--
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] Calc Formula Help
If I understand what you are trying to do. I put this data in Column A and Column B. COLACOLB A 7 G 87 T 1 A 1 G 6 T 7 A 43 G 3 T 2 I put the criteria in G1 and G2 COLA A Then used the formula to get the value. =DMIN(A1:B10,"COLB",G1:G2) That just does it for A, but you could easily setup other criteria to get the values for the tother letters. On 2 Sep 2011 at 4:05, macroC wrote: Date sent: Fri, 2 Sep 2011 04:05:54 -0700 (PDT) From: macroC To: users@global.libreoffice.org Subject:[libreoffice-users] Calc Formula Help Send reply to: users@global.libreoffice.org > 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. > > -- > View this message in context: > http://nabble.documentfoundation.org/Calc-Formula-Help-tp3303917p3303917.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 > +--+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@kuentos.guam.net mailto:msetze...@gmail.com http://www.guam.net/home/mikes Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +--+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS SETI11189934.576302 | EINSTEIN 6468781.769851 ROSETTA 3563786.501816 | ABC 7836158.160609 -- 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] Calc Formula Help
Hi :) I don't quite understand what you are trying to do. Do columns A & B both already contain data? So are you trying to look-up all the values in B that have an A in column A and then add those numbers up? Regards from Tom :) From: macroC To: users@global.libreoffice.org Sent: Fri, 2 September, 2011 12:05:54 Subject: [libreoffice-users] Calc Formula Help 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. -- View this message in context: http://nabble.documentfoundation.org/Calc-Formula-Help-tp3303917p3303917.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 -- 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
