Re: [libreoffice-users] [SOLVED] Re: Calc 6.0.6.2 IF criteria statement help

Hi,
To answer your question about the CONCATENATE("<>","Y"), I am looking
for something that is not explicitly marked as reconciled (not "Y").
This test will mark as not reconciled all entries having an "N" (or
anything else for that matter) and all blank entries (a blank cell
obviously not being equal to "Y"), which is a more robust test than
looking only for cells that are qual to "N".
For the rest, I think you have a good understanding of what the formula
is doing.
I hope this helps.
Rémy.
.Le jeudi 15 novembre 2018 à 21:35 +0200, Hylton Conacher (ZR1HPC) a
écrit :
> Hi Remy,
>
> On 2018/11/13 01:22, remygauthier wrote:
> > Hi,
> > Unclaimed_slips.ods
> > <http://document-foundation-mail-archive.969070.n3.nabble.com/file/
> > t489793/Unclaimed_slips.ods>
> > I went back to the original post and downloaded the file you had
> > uploaded to
> > Google Drive. I made what I think were the correct mods and the
> > results on
> > the Summary sheet are consistent with what has been entered. I have
> > attached
> > the modified file, so you should be able to download it and have a
> > look (you
> > will probably need to access the post via the nabble site -
> > nabble.documentfoundation.org/). Once we know what is wrong with
> > the
> > changes, it should be easier to provide information.
> >
> > I hope this helps.
>
> I have reviewed your file on Nabble and after reviewing and
> inserting 
> the formula into the current file, am pleased to announce that the
> issue 
> is SOLVED! :)
>
> THANKYOU
>
> Looking at the formula 
> <=SUMIFS(Data.C$3:Data.C$1000,Data.$A$3:Data.$A$1000,CONCATENATE("=",
> $A3 
> ,"*"),Data.$F$3:Data.$F$1000,CONCATENATE("<>","Y"))>, I see how the 
> Concatenate was used almost like an IF statement criteria, and could
> be 
> stated as 'CONCATENATE("=",Summary.$A3 ,"*")'. However I do not 
> understand the second CONCATENATE as I am looking for those with an
> "N" 
> i.e. ones that have not been reconciled yet the SUMIFS yields the 
> correct value?
>
> Concatenate joins/amalgamates values from different cells or text
> i.e. 
> as a Linux command $>cat filename would display the contents of the 
> filename. My understanding is that a similar command of $>cat bee
> hive 
> would result in the word beehive being displayed.
>
> In the formula you state to SUM values in Data.c3:Data.c1000 if date 
> value of column A equals a partial match of the actual Data column 
> holding the date AND where the value is blank or Y, despite you only 
> wanting to SUM the N values?
>
> Basically the formula works and I am not complaining and very
> grateful 
> but I would like to better understand the formula, so that I can use
> it 
> in future.
>
> I hope my explanation is sufficient.
>
> Regards
> Hylton
>
> P.S.: To ALL owners of a Debit or Credit Card, establish a
> spreadsheet 
> using the above formula, enter your transactions on the Data tab, 
> reconcile them according to your bank statement and see what value
> of 
> goods you are getting for FREE as sometimes it pays to shop at
> certain 
> retailers, even if it is only occasional.
-- 
To unsubscribe e-mail to: users+unsubscribe@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