[SOLVED ATLAST] very complicated excel formula

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Hi to all
my sheet which was originally created in Excel with some formula is not working in Openoffice. have some complicated formulas need some solution for this
formula is asbelow
=IF(ISERROR(INDEX($Targets.$B$5:$F$1000;MATCH($PayalA.H135&$PayalA.I135&$PayalA.J135;$Targets.$B$5:$B$1000&$Targets.$C$5:$C$1000&$Targets.$D$5:$D$1000;0)));INDEX($Targets.$E$5:$E$1000;MATCH($PayalA.H135&$PayalA.I135&$PayalA.J135;$Targets.$B$5:$B$1000&$Targets.$C$5:$C$1000&$Targets.$D$5:$D$1000;0)))

the error i get in cell is ERR:504
pls help me to resolve this problem
Thanks a lot

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Hi,
i will try to explain you the scenario of the above formula
i have two sheets Targets and PayalA
in Targets four columns as seen below

Audit Audit Type User Type WORK-CODE

LXDK Sales Auditor LXDK
LXDK Sales QC LXDKQC
LXDK Sales ADM LXDKADM
LXDK Refunds Auditor LXDKRfnd
LXDK Refunds QC LXDKRfndQC
LXDK Refunds ADM LXDKRfndADM
LXFI Sales Auditor LXFI

now in PayalA i have four column same as above the only diffrence is the first three solumns have drop down menus in in first columns drop down menu you will find LXDK, LXFI in second you will find Sales, Refunds. in third you will find Auditor, QC etc. now when you select LXFI in first columns and Sales IN second Column and Auditor in third column then the fourth coulm automatically displays LXFI as shown in WORK-CODE..

i want to know that can this be done with other simple formula pls help me

Thanks in advance

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Hi,
Also in Excel i am seeing { } in start and end of the formula for eg.

{=IF.............}

Pls help me really in need of help
Thanks

huwg · Super User Joined: 14 Feb 2007 Posts: 890

The curly brackets mean it is an array formula. Calc uses them for the same purpose.

jwr · Posted: Wed Jul 18, 2007 4:35 am Post subject:

If you search for Err 504 (remember to search for both terms)
you will find approximately 27 threads to this topic. Error 504 is documented as:

Your formula is probably messing up text and numbers.

Try to isolate selected parts of the formula and see if the error disappears.

Added: Excel is smarter than Calc when it comes to array-formulas.
See: http://www.oooforum.org/forum/viewtopic.phtml?t=34340&highlight=err+504

Regards -Hans-
_________________
Windows 2K, OOo-2.3.0, MS-IE-6.0, Firefox-1.0.4, MS Office 97,
Say NO to MS OpenXML: http://www.noooxml.org/petition

huwg · Super User Joined: 14 Feb 2007 Posts: 890

An easy way to check part of a formula is to highlight just that bit in the input line and then press F9 to see its result.

acknak · Posted: Wed Jul 18, 2007 7:00 am Post subject:

There are several things that look strange to me. I'm not that good with array formulas, so maybe I just don't know, but here's what I see.

First, the formula isn't that complicated if you lay it out clearly:

Villeroy · Posted: Wed Jul 18, 2007 7:54 am Post subject:

The simplified version =MATCH($A$1&$B$1&$C$1;$E$1:$E$7&$F$1:$F$7&$G$1:$G$7;0)
returns err:504 (array or not)
but I can concatenate E1:G7 using {=E1:E7&F1:F7&G1:G7}
and use
=MATCH($A$1&$B$1&$C$1;$H$1:$H$7;0)

I hope that you spreadsheet is just a play ground for unimportant data, since this is another example for misuse of spreadsheet as database.
In my example "ABC" matches "abc" as well as "Abcd", "ABCDE", ... depending on settings in the options panel.
What tells you that there is only one match?

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Guru,
i am more confused and beleive me the spreadsheet is not a playground of unimportant data neither it is used as database it is very much used for Calculations i am uploading the file please have a look and hope then i get some help.
if this get resolves it will be a great learning for me so really looking fwd for solution thanks to all of you in advance

http://upload2.net/page/download/VHzBc8INtvv2jXm/sheet+with+formula+Formula.xls.html

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Can this be done with vlookup?
like from sheet 1 i need a match of three columns and in sheet two if a match of three colums are found it should show me fourth column in sheet 2 which is also in sheet 1

thanks

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Can this be done with vlookup?
like from sheet 1 i need a match of three columns and in sheet two if a match of three colums are found it should show me fourth column in sheet 2 which is also in sheet 1

thanks

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Assuming that Openoffice cannot handle such situation

Villeroy · Posted: Thu Jul 19, 2007 7:37 am Post subject:

Excel can not handle this situation neither!
Whatever you try to lookup, be it one, two or many items at a time. There is always a chance that your data are not consistant.
MATCH( 3.14 ; Column ; 0)
May return #NA if number 3.14 is not in Column.
There could be a typo or text-formatting in Column, so that string-values "3.14" or "3,14" will never be matched. A database would simply not accept text input in a numeric column.
Same issue with numbers in text columns:
When you lookup some text in a column of remarks, then someone may have typed a phone number as text-value another one may have typed a plain numeric value. In your case the concatenated lookup may stumble upon leading zeros, missing in the numeric value.
Then you may have more than one value 3.14 in Column. Only the first value will be matched. A database can be indexed so that Column has to be unique or a combination of values in more than one column has to be unique. All input, which would violate indexing will never be written to disk.

Spreadsheets are not designed to replace databases. Unfortunately nothing but desaster stops people from continuing with "spreadbases", so I provided a "solution" to your lookup problem. The difference between Excel anc Calc is, that the latter can not handle the concatenations in the second argument of MATCH.
MATCH(A1&B1&C1 ; $F$1:$F$1000$G$1:$G$1000;$H$1:$H$1000 ; 0) does not work in Calc.
MATCH(A1&B1&C1 ; $X$1:$X$1000 ; 0) works just fine if X has the concatenated values from F, G and H

Writing this, there arises another question:
A1: "abc"
B1: "def"
C1: "ghi"
Now you try to match the concatenation of A1, B1 and C1 in columns F, G, and H (one way or the other in Excel).
You may get a matching row where
F: "ab"
G: "cde"
H: "fghi"
or any other combination of "abcdefghi" in the three columns.
Is this intended to be so? Did anyone ever think of this possibility?
A helper column with this array formula can handle this in both applications.
X1:X1000:

jwr · Posted: Thu Jul 19, 2007 10:55 pm Post subject:

Villeroy · Posted: Fri Jul 20, 2007 2:33 am Post subject:

Hi, Hans
Implement database functionality by macros? Been there, done that, got the t-shirt. The shirt's unforseen costs amounted to some thousands Deutsche Mark at that time (1 EUR ~= 2 DM).
Try to hack a Writer document, so a text-table in that document gets some
spreadsheet functionality, e.g. update references on insertion of rows/columns.
Would be a nice playground to explore text tables in depth, but nothing more.

Most recently in the other forum:
http://www.oooforum.org/forum/viewtopic.phtml?t=59904
It should be possible to prevent duplicate input by macro for a *few* thousands of records.
Next questions would be: "How do I handle/prevent cut/copy and paste, deletion/insertion of rows/columns, SaveAs..., ?
Without any office package, using some powerful scripting language (like Perl) a skilled programmer could implement a tiny text-based database within some hours (disregard existing libraries). But why should one do that?

There is another guy in this forum, who cluttered his data across several spreadsheets
and then needed a macro to transfer sub-sets of data from one to another. "No database required. My data are OK, just a simple routine to copy values with formatting from a distinct sheet to ...".
I hacked at least 3 versions of a quick and dirty Basic macro for him. No big deal. But that was some year ago. The guy is still struggling with transfer of data between spreadsheets.

This seems to be a psychological issue. Spreadsheets look so tidy and clean, they are "easy" and anything seems to be possible.
For many users (including me) spreadsheet-formulae opened a back door to the secret world of programming in a friendly, document orientated environment.
Computers are told to compute numbers. This seems to be *the one and only* tool which enables me to do so when my word processor reached his limits.
It's the Microsoft effect. Big gain with small efford. Human brains (and not only humans) respond immediatly and unconsciously to incentives like this.
If your only tool is a hammer, everything looks like a nail.