[Solved] Largest If?

[giuvi]

Split from Largest If? (Hagar, Moderator)

Hello everyone!

I have a similar issue as Robindude and I tried the suggested MAX(IF()) Formula, but with two conditions and using cell area names:

Code: Select all

{=MAX(IF(DateColumn=A5;OrderColumn=B5;EndTime))}

where
OrderColumn -> contains the order name (many orders have the same name, but not on the same day) [Text format]
DateColumn -> contains the days, when the order is executed [Date format: DD.MM. NN]
EndTime -> this column contains the time, when the orders has been completed [Time format: hh:mm]

But I get wrong results
Does the formula contain more the one condition? Or is my syntax wrong?

Thanks in advance for replay
Pinotto

[Villeroy]

The times are text values.
menu:View>Highlight Values [Ctrl+F8] displays all numbers in blue and text in black.

[giuvi]

The times are text values.
menu:View>Highlight Values [Ctrl+F8] displays all numbers in blue and text in black.

I see ... Now I have changed the previous text.

You wrote: "The times are text values"
Do you mind, times should be text values?

[Villeroy]

No. Dates, times, currencies etc. in spreadsheets should be numbers.
How do the times get into your sheet?
Text import?
Copy&Paste from internet?
What is your locale setting in Tools>Options>LanguageSettings>Languages? Italian?

[giuvi]

How do the times get into your sheet?
What is your locale setting in Tools>Options>LanguageSettings>Languages? Italian?

Hello Villeroy,

thanks for your replay.

All the source data (dates, times, order names) are manual put in, but some time values are determined by formulas.
he AOO language is German and the MAX(IF()) Formula too.
Here copied from the sheet:

Code: Select all

=MAX(WENN(DatumArbLog=A49;AuftragArbLog=B49;bisArbLog))

Where:
DatumArbLog is the cells area containing day values [DD.MM. NN]
AuftragArbLog is the cells area containing the order names [text format]
bisArbLog is the cells area containing the time, the order is completed [hh:mm]. The results cells have the same format.


Additional information - until now omitted, because I think, it does not influence the result.
Source and results data are in different spreadsheets on the same calc file.

[Villeroy]

The formatting is completely irrelevant. The only thing that matters is cell type text or number and the actual value of the text or number.
For this kind of database stuff, I would suggest function DMAX or a simple pivot table showing all MAX for all categories (Y and N in this case).
https://wiki.openoffice.org/wiki/Docume ... /DataPilot (aka pivot table)

Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.

https://wiki.openoffice.org/wiki/Docume ... _functions

An array function like MAX( (range="Y") * times) may also work.

In most cases, the IF function is not the solution to a spreadsheet problem.

[giuvi]

Villeroy, thanks for support!

I know pivot table and sometimes I use it, but I find in my case it is not the ideal solution, because the source data get about 20 rows added every day.
DMAX. I saw the syntax of DMAX in the page, you have suggested and I see, it does not support conditions, where I need to find the max value of time for a certain day (of many) and a certain order (of many in same day).

But back to the my original question:
Do you know, whether the MAX(IF()) formula work with two conditions?

[Villeroy]

Of course you can add new records to your pseudo-database.
1. Tools>Options>Calc>General>"Expand refs when new rows and columns..." = ON
2. Insert new rows within the list range or directly below.
All formulas, all names, pivot tables, charts, conditional formats, validations etc. referring to that list range will adjust. Without the above option the insertion below would not expand the reference.
3. Refresh the data pilot (right-click>refresh or menu:Data>Pivot>refresh)
or
3. Put the pilot(s) on separate sheets and use a little macro to update all pivots when the sheet becomes active.

Code: Select all

Sub activatePivotSheet()
sh = ThisComponent.CurrentController.getActiveSheet()
for each dp in sh.DataPilotTables
	dp.refresh()
next
End Sub

or refresh all pivots on all sheets by pushing a document specific toolbar button:

Code: Select all

Sub refreshAllPivots()
shx = ThisComponent.getSheets()
for each sh in shx
for each dp in sh.DataPilotTables
	dp.refresh()
next
next
End Sub

Tools>Customize>Toolbars, select your document at "Save in" and add a new toolbar with a macro button.

This would also work if you would store your list data in a real database (which is the one and only way to go with all kinds of growing row sets).
And of course you can do similar aggregations with simple database queries.

[MrProgrammer]

{=MAX(IF(DateColumn=A5;OrderColumn=B5;EndTime))}
But I get wrong results. … Or is my syntax wrong?

Syntax: IF(Test; ThenValue; OtherwiseValue)

Of course your syntax is wrong. Compare the description of IF() syntax with what you wrote. Don't try to guess at syntax.
Try =MAX(IF((DateColumn=A5)*(OrderColumn=B5);EndTime;"")) as an array formula (⇑⌘Enter on a Mac, Ctrl+Shift+Enter on other platforms).

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

[Tutorial] Ten concepts that every Calc user should know

[giuvi]

Many thanks MrProgrammer !!!

That's it!