[Solved] Formula not working #VALUE!

[Hardrockbaer]

Hello,
I have a open office cal file,
where a formula does not work well (value ends up with #Wert!
I tried to upload my file but it was too big -> 750 kiB.

Therefore I attach my formula herer:

This is working (function is: pick up from another folder , where I copy my CSV data in, the first day (here MONDAY, and date)

Code: Select all

=WENN(KÜRZEN(MAX($'Kopie aus CSV-Datei'.$A$3:$A$2454))=MAX($A$2:A2);"";KÜRZEN(KKLEINSTE($'Kopie aus CSV-Datei'.$A$3:$A$2454;WENN(ZEILE($'Kopie aus CSV-Datei'.A2)=1;0;SUMMENPRODUKT(N(KÜRZEN($'Kopie aus CSV-Datei'.$A$3:$A$2454)<=A2)*($'Kopie aus CSV-Datei'.$A$3:$A$2454<>"")))+1)))

And here comes the problem: With that calculation the next day should be shown (here Tuesday and date)

Code: Select all

=WENN(KÜRZEN(MAX($'Kopie aus CSV-Datei'.$A$3:$A$2454))=MAX($A$2:A9);"";KÜRZEN(KKLEINSTE($'Kopie aus CSV-Datei'.$A$3:$A$2454;WENN(ZEILE($'Kopie aus CSV-Datei'.A3)=1;0;SUMMENPRODUKT(N(KÜRZEN($'Kopie aus CSV-Datei'.$A$3:$A$2454)<=A9)*($'Kopie aus CSV-Datei'.$A$3:$A$2454<>"")))+1)))

The calc file was written in Microsoft Excel, where it also works perfectly.
However in Open Office I have troubles now (Error #Wert!)

Any ideas ?
Would be happy about help

[robleyd]

The error - in English - is #VALUE! which is Error 519. The error means:
The formula yields a value that does not correspond to the definition; or a cell that is referenced in the formula contains text instead of a number.

[Hardrockbaer]

Hi,
thank you for your quick answer.
Yes, you are right. Sorry my file is in German ...
But I can´t find the problem.
The imported CSV file is just a list with date/time (in one cell) ,, followed by numbers.

Uhrzeit Netzb [kW] Netz[kW] Stromv [kW] Akku-[kW] Akku-E kW] Strom[kW]
28.09.2020 00:09 0,000224 0,010585 0,254127 0 0,264575 0 55,024002 -5,09
28.09.2020 00:14 0,000283 0,010345 0,255295 0 0,265434 0 54,992004 -4,87
28.09.2020 00:19 0,000529 0,010412 0,258547 0 0,268487 0 54,957001 -5,08

The formula refers to the date/time column.
The first calculation is working (monday), but the following day does not work ,,,,

Dont know where to search ....
Is there any function in open office to find that "wrong cell" ?

Any help would be really great.
Again ... both formula (from post #1) are working at Microsoft Excel) ....

[Villeroy]

The most common mistake when importing csv is to not check the "special numbers" option ("Erweiterte Zahlenerkennung"). Always check that option. Otherwise dates, times, currencies, percent, booleans etc. import as text and calculation with text raises #VALUE! errors.

[Hardrockbaer]

thanks everybody for best ideas.
But still struggling....
Any chance to send the file to anybody to check the calc file on that point ?

[Villeroy]

Click [Post Reply] instead of [Quick Reply]. Scroll down a little bit. Upload a small document with the formula and its referenced values.

[robleyd]

What is the source file that you refer to as Kopie aus CSV-Datei? Is this a raw CSV file, or have you imported into a Calc sheet? If so, did you check - meaning select - the option "Erweiterte Zahlenerkennung" (Detect special numbers) when you imported the CSV file?

[Hardrockbaer]

i tried to reduce the size, however, the smallest size is 450 kiB .... and its too big to upload ....

[Hardrockbaer]

What is the source file that you refer to as Kopie aus CSV-Datei? Is this a raw CSV file, or have you imported into a Calc sheet? If so, did you check - meaning select - the option "Erweiterte Zahlenerkennung" (Detect special numbers) when you imported the CSV file?

I just copy from the other file the data with ctrl+c
and paste it into the calc file ....

doing the same with Microsoft Excel .... all is fine ....

[Hardrockbaer]

What is the source file that you refer to as Kopie aus CSV-Datei? Is this a raw CSV file, or have you imported into a Calc sheet? If so, did you check - meaning select - the option "Erweiterte Zahlenerkennung" (Detect special numbers) when you imported the CSV file?

I found out with the "detectiv" function ... that the calculation for the second day will fail somewhere in that term :

=WENN(KÜRZEN(MAX($'Kopie aus CSV-Datei'.$A$3:$A$2454))=MAX($A$2:A9);"";KÜRZEN(KKLEINSTE($'Kopie aus CSV-Datei'.$A$3:$A$2454;WENN(ZEILE($'Kopie aus CSV-Datei'.A3)=1;0;SUMMENPRODUKT(N(KÜRZEN($'Kopie aus CSV-Datei'.$A$3:$A$2454)<=A9)*($'Kopie aus CSV-Datei'.$A$3:$A$2454<>"")))+1)))

getting crazy ....

[Zizi64]

i tried to reduce the size, however, the smallest size is 450 kiB .... and its too big to upload ....

Then you can share it on a free file sharing service, like the Google drive. Put the link into your post. Then we will able download it.

[Hardrockbaer]

thanks. good idea

please check there :

https://drive.google.com/drive/folders/ ... sp=sharing

you can see at one page ... 28.09.20 mo
the next line is the problem - should be tuesday ....

[Villeroy]

What a spreadsheet madness.
The results look right when loaded with LibreOffice (although I do not understand anything).

[MrProgrammer]

Hi, and welcome to the forum.

… where a formula …

To improve your chances of getting the problem solved from your initial post, always attach a document demonstating the difficulty. If the original document is large, make a subset of it which is smaller than 128K but still demonstates the difficulty.

please check there: https://drive·google·com/drive/folders/1ERqQjWegTH-v0ZFr_zpHPtY104ND8vNB?usp=sharing

Thank you. Always tell us which sheet and which cell in your attachment contains the formula with the difficulty.

Is there any function in open office to find that "wrong cell" ?

Insert → Function → Structure, Tools → Detective → Trace Error, and F9 (partial formula evaluation) helped me to isolate your difficulty. Read about those suggestions in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

… ends up with #Wert!

In 'Tagesauswertung in kWh'.A10 you have formula:

=IF(
       TRUNC(
          MAX($'Kopie aus CSV-Datei'.$A$3:$A$2454)
       )
    =
       MAX($A$2:A9);
    "";
    TRUNC(
       SMALL(
          $'Kopie aus CSV-Datei'.$A$3:$A$2454;
             IF(
                   ROW($'Kopie aus CSV-Datei'.A3)
                =
                   1;
                0;
                SUMPRODUCT(
                      N(
                            TRUNC($'Kopie aus CSV-Datei'.$A$3:$A$2454)
                         <=
                            A9
                      )
                   *
                      ($'Kopie aus CSV-Datei'.$A$3:$A$2454<>"")
                 )
             )
          +
             1
       )
    )
 )

However COUNT($'Kopie aus CSV-Datei'.$A$3:$A$2454) is 2002 and IF(ROW($'Kopie aus CSV-Datei'.A3)=1;0;SUMPRODUCT(N(TRUNC($'Kopie aus CSV-Datei'.$A$3:$A$2454)<=A9)*($'Kopie aus CSV-Datei'.$A$3:$A$2454<>"")))+1 is 2003. SMALL cannot select a 2003rd number from $'Kopie aus CSV-Datei'.$A$3:$A$2454 and returns #VALUE!. Then errors cascade after that.

The calc file was written in Microsoft Excel, where it also works perfectly.

That the formula works in Excel is not relevant. Calc is not a clone of Excel. There are many differences. Use the software which works best for you.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know