Re: [libreoffice-users] Help with LOCalc formula (SOLVED)
Hi, Bruce, Remy, Brian and Jim Thank you for your kindness and answers. Tonight while following the election on TV I'll have a lot to do when giving the VLOOKUP() a new try according to your advice. I must have done bad thinking -- perhaps getting old. Anyway I again learn a lot. Best regards Pertti On 8.11.2016 11.58, James E Lang wrote: Correction: replace 25 with 0. -- Jim -Original Message- From: "James E. Lang" <jim+...@lang.hm> To: "users@global.libreoffice.org" <users@global.libreoffice.org>, "Pertti Rönnberg" <p...@elisanet.fi> Cc: "Pertti Rönnberg" <p...@elisanet.fi> Sent: Tue, 08 Nov 2016 1:52 Subject: Re: [libreoffice-users] Help with LOCalc formula Try this. =IF(B1="","",OFFSET($Z$20,MATCH(B1,$B$20:$B$35,0)-1,25)) -- Jim -Original Message- From: "Pertti Rönnberg" <p...@elisanet.fi <mailto:p...@elisanet.fi>> To: "users@global.libreoffice.org <mailto:users@global.libreoffice.org>" <users@global.libreoffice.org <mailto:users@global.libreoffice.org>> Cc: "Pertti Rönnberg" <p...@elisanet.fi <mailto:p...@elisanet.fi>> Sent: Mon, 07 Nov 2016 8:00 Subject: [libreoffice-users] Help with LOCalc formula Best Spreadsheet Experts, May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10) Description I try to get a little extra to my pension savings by experimenting on the share market and follow up the results using Calc (not a database). I have a spreadsheet with two ranges – range(1) directly above range(2). r(1) specifies per row every specific purchase/sell event (date, company name, amount, etc). To calculate the actual "to-day"-result for this specific event (row) we need the "to-day"-value for this company's share (in e.g. column X), which value is taken from respective company's row in col.Z in r(2). r(1) has yearly 100-130 rows (events) Range(2) lists each company named in the above Range(1) and has in col.Z the corresponding share's "to-day"-value. These inserted share values varies from one day to the next. There are now 10-15 companies (rows) listed in r(2) – not in alphabetic order. r(2) sums the up-to-date result per company and the total result. Problem The belowe example simplified to 5 events concerning 3 companies may clearify the problem with the formula in X1 copied down col.X: =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) As you can see: with 15 (or more) companies (and >100 events) the formula will grow and become very long and sensitive for mistakes. VLOOKUP does not seem to be an option, at least it requires a completely new setup of the page. Range(1) - events A B C…. X 1. nokia 5,o 2. kone 41,o 3. nokia 5,o 4. fiskars 12,o 5. kone41,o Range(2) - companies A B C... Z 20 nokia 5,o 21 fiskars 12,o 22 kone 41,o Any suggestion will be very interesting. Thank you in advance Pertti Rönnberg/Finland -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org <mailto: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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org <mailto: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 -- 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] Help with LOCalc formula
Try this. =IF(B1="","",OFFSET($Z$20,MATCH(B1,$B$20:$B$35,0)-1,25)) -- Jim -Original Message- From: "Pertti Rönnberg"To: "users@global.libreoffice.org" Cc: "Pertti Rönnberg" Sent: Mon, 07 Nov 2016 8:00 Subject: [libreoffice-users] Help with LOCalc formula Best Spreadsheet Experts, May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10) Description I try to get a little extra to my pension savings by experimenting on the share market and follow up the results using Calc (not a database). I have a spreadsheet with two ranges – range(1) directly above range(2). r(1) specifies per row every specific purchase/sell event (date, company name, amount, etc). To calculate the actual "to-day"-result for this specific event (row) we need the "to-day"-value for this company's share (in e.g. column X), which value is taken from respective company's row in col.Z in r(2). r(1) has yearly 100-130 rows (events) Range(2) lists each company named in the above Range(1) and has in col.Z the corresponding share's "to-day"-value. These inserted share values varies from one day to the next. There are now 10-15 companies (rows) listed in r(2) – not in alphabetic order. r(2) sums the up-to-date result per company and the total result. Problem The belowe example simplified to 5 events concerning 3 companies may clearify the problem with the formula in X1 copied down col.X: =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) As you can see: with 15 (or more) companies (and >100 events) the formula will grow and become very long and sensitive for mistakes. VLOOKUP does not seem to be an option, at least it requires a completely new setup of the page. Range(1) - events A B C…. X 1. nokia 5,o 2. kone 41,o 3. nokia 5,o 4. fiskars 12,o 5. kone41,o Range(2) - companies A B C... Z 20 nokia 5,o 21 fiskars 12,o 22 kone 41,o Any suggestion will be very interesting. Thank you in advance Pertti Rönnberg/Finland -- 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 -- 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] Help with LOCalc formula
At 17:58 07/11/2016 +0200, Pertti Rönnberg wrote: I have a spreadsheet with two ranges range(1) directly above range(2). r(1) specifies per row every specific purchase/sell event (date, company name, amount, etc). To calculate the actual "to-day"-result for this specific event (row) we need the "to-day"-value for this company's share (in e.g. column X), which value is taken from respective company's row in col.Z in r(2). r(1) has yearly 100-130 rows (events) Range(2) lists each company named in the above Range(1) and has in col.Z the corresponding share's "to-day"-value. These inserted share values varies from one day to the next. There are now 10-15 companies (rows) listed in r(2) -- not in alphabetic order. r(2) sums the up-to-date result per company and the total result. Problem The below example simplified to 5 events concerning 3 companies may clarify the problem with the formula in X1 copied down col.X: =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) As you can see: with 15 (or more) companies (and >100 events) the formula will grow and become very long and sensitive for mistakes. VLOOKUP does not seem to be an option, at least it requires a completely new setup of the page. Range(1) - events A B C . X 1. nokia 5,o 2. kone 41,o 3. nokia 5,o 4. fiskars 12,o 5. kone41,o Range(2) - companies A B C... Z 20 nokia 5,o 21 fiskars 12,o 22 kone 41,o Sorry, but why do you think that VLOOKUP() is not an option? As has already been suggested, it is exactly what you need. Are you perhaps concerned that column Z in your company table is so far away from column B? That does not matter. For the formulae in column X of your events table, you retrieve the required value from the company table using VLOOKUP(). Its first parameter is the company name in the events table, Bn or whatever. The second parameter is the range that contains the company names and values, so B$20:Z$22 (if you see what I mean). The third parameter is the column in that range containing the required value; if the range really does span columns B to Z, that will be 25. The fourth parameter should be FALSE (or zero), to indicate that the company names in column B of the company table are not sorted. 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] Help with LOCalc formula
Le lundi 07 novembre 2016 à 17:58 +0200, Pertti Rönnberg a écrit : > > r(1) specifies per row every specific purchase/sell event (date, company > name, amount, etc). > > To calculate the actual "to-day"-result for this specific event (row) we > need the "to-day"-value for this company's share (in e.g. column X), > which value is taken from respective company's row in col.Z in r(2). > r(1) has yearly 100-130 rows (events) > > > Range(2) lists each company named in the above Range(1) and has in col.Z > > the corresponding share's "to-day"-value. These inserted share values > varies from one day to the next. > > There are now 10-15 companies (rows) listed in r(2) – not in alphabetic > order. > r(2) sums the up-to-date result per company and the total result. > > Problem > The belowe example simplified to 5 events concerning 3 companies may > clearify the problem with the formula in X1 copied down col.X: > =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) > Unless there is something I do not understand, VLOOKUP() would probably be a good option for you. I would enter the formula as follows (based on your example) in X1: =IFERROR(VLOOKUP(B1;B$20:Z$22;25;FALSE());0) The "IFERROR()" is used to return a value of zero in case the lookup fails, which corresponds to the last selection when false in the string of nested IF() functions you listed. You can also re-write the formula like this: =IFERROR(VLOOKUP(B1;OFFSET(B$20;0;0;;25);25;FALSE()) ;0) where is replaced by a formula that counts the number of entries you have in r(2). This way, as you add entries, the formula dynamically adjusts itself to the correct count of values and you can drag this formula with little risk of error; depending on what you are counting COUNT() or COUNTA() would work for you. Additionally, you could assign a name to cell $B$20 and use that name in the formula instead of an explicit reference to the cell. Alternatively, you could use the MATCH() function to locate on which row the company name is and then use INDIRECT(ADDRESS()) to retrieve the value you are looking for. This is slightly more complex and in my mind does not really perform better than VLOOKUP() in this case, but it remains an alternative. I hope this helps. Rgds, Rémy Gauthier. > Any suggestion will be very interesting. Thank you in advance Pertti Rönnberg/Finland -- 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] Help with LOCalc formula
I think VLOOKUP might work. In cell X1 try this: =VLOOKUP(B1,B$20:C$22,2,0) Copy down as needed; Adjust 2nd parameter (B$20:C$22) as needed. On Mon, Nov 7, 2016 at 10:58 AM, Pertti Rönnbergwrote: > Best Spreadsheet Experts, > May I ask for help having a more elegant formula in LOCalc (v.5.0.3; win10) > > Description > I try to get a little extra to my pension savings by experimenting on the > share market and follow up the results using Calc (not a database). > I have a spreadsheet with two ranges – range(1) directly above range(2). > > r(1) specifies per row every specific purchase/sell event (date, company > name, amount, etc). > To calculate the actual "to-day"-result for this specific event (row) we > need the "to-day"-value for this company's share (in e.g. column X), which > value is taken from respective company's row in col.Z in r(2). > r(1) has yearly 100-130 rows (events) > > Range(2) lists each company named in the above Range(1) and has in col.Z > the corresponding share's "to-day"-value. These inserted share values > varies from one day to the next. > There are now 10-15 companies (rows) listed in r(2) – not in alphabetic > order. > r(2) sums the up-to-date result per company and the total result. > > Problem > The belowe example simplified to 5 events concerning 3 companies may > clearify the problem with the formula in X1 copied down col.X: > =if(B1=$B$20;$Z$20;if(B1=$B$21;$Z$21;(ifB1=$B$22;$Z$22;0))) > > As you can see: with 15 (or more) companies (and >100 events) the formula > will grow and become very long and sensitive for mistakes. > VLOOKUP does not seem to be an option, at least it requires a completely > new setup of the page. > > Range(1) - events > A B C…. X > 1. nokia 5,o > 2. kone 41,o > 3. nokia 5,o > 4. fiskars 12,o > 5. kone41,o > > Range(2) - companies > A B C... Z > 20 nokia 5,o > 21 fiskars 12,o > 22 kone 41,o > > Any suggestion will be very interesting. > Thank you in advance > Pertti Rönnberg/Finland > > > -- > 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