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

Hi,
I believe you had a similar (if not identical question) which you
posted back on September 27th (topic: IF doesn't respect AND - http://d
ocument-foundation-mail-archive.969070.n3.nabble.com/IF-doesn-t-
respect-AND-td4249584.html). At the time you had uploaded a file to
Google and I had offered a possible solution to your problem which had
worked in my environment. I am curious to know if you have tried it and
if it worked.
Rgds,
Rémy.
Le samedi 10 novembre 2018 à 08:16 -0600, Michael H a écrit :
> I'm not sure I follow your logic, but if your source range contains a
> list
> of values that includes nulls, and non-numbers, you need to logically
> exclude those from a math statement, or you get the 'VALUE' error
> (tried to
> do math on a non-numeric cell.) ISBLANK, and ISNUMBER are functions
> that
> provide the way around the VALUE.
>
> If you're trying to make a list of values out of a column of values,
> investigate CONCATENATE.  The problem with concatenate is that it
> doesn't
> auto-insert separators and whitespace.  You'll either need to include
> a new
> column with ", " in each row and concatenate 2 columns, or have the
> appropriate whitespace in each cell.
>
> If you only have a couple cells to concatenate, you can be precise
> with the
> & operator.  that is [="Hello "&"there!"] allows multiple strings in
> a
> single cell, and each string can be cell references or functions with
> string or numeric output.  [="I've seen "&A5&", "&G3&" cells today."]
> is a
> nice way to summarize your data in human readable form.
>
> If you're trying to only pick a few values, investigate LOOKUP.  I'm
> an
> excel refugee, (and used Lotus, and 8bit (GEOS) spreadsheets before
> that.)
> LibreOffice version of LOOKUP is.... not very well done, but if
> you're
> patient with it, it gets the job done.  I frequently have to insert
> dummy
> rows containing null cells when I want 'exact match only', and
> libreoffice
> can't deal with 'non-sorted' indexes, and they aren't clearly or
> obvious
> what the sorting pattern should be, so it's best to sort your data
> within
> LibreOffice before you use lookup.  (for lookups, a ==A or not,
> sometimes
> NBSP == space, but sometimes not.  This affects matches, but also
> sorting..
> and sort failures breaks many responses, not just one.  These sorting
> issues are constant for a single edition of libreoffice, but seem to
> change
> without any notice on updates.)
>
> On Sat, Nov 10, 2018 at 7:44 AM Hylton Conacher (ZR1HPC) <
> hylton@conacher.co.za> wrote:
>
> > Hi,
> >
> > What I need help with is the formula to find any row from Data.$A$3
> > to
> > Data$A$1000 and after shortening the 8 digit date value to 6 digit
> > value, and having the same 6 digit value as Summary.$A4, forward
> > ONLY
> > those rows onto the next part of the formula i.e. SUMIF.
> >
> > I have a formula shown below but currently is showing me #VALUE! as
> > my
> > result.
> >
> >
> > "=IF(INT(Data.$A$31:Data.$A$50/100)=Summary.$A4,SUMIF(Data.$F$31:Da
> > ta.$F$50,"N",Data.$C$31:Data.$C$50))"
> >
> > I have identified the problem where the INT value = Summary.$A4
> >
> > What would the correct syntax be to have a criteria equal a
> > specified
> > value as a criteria in a formula? I tried encasing
> > "INT...=Summary.A4",
> > but no luck.
> >
> > SUMIFS does not assist as the criteria to find, after INT
> > shortening
> > must equal another value before the next step can be carried out.
> >
> > Appreciate any help. Where should I upload a file Nabble, GDrive,
> > other?
> >
> > Regards
> > Hylton
> >
> > --
> > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > Problems?
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscri
> > be/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Neti
> > quette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
-- 
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