Ask Your Question
0

max with condition

asked 2016-06-05 22:33:29 +0100

-rob- gravatar image

I have a spreadsheet which has an ID (in this case of a musical piece) in column C of tab "Lijst".

Now I want to know when this is last performed by our choir and put that in a separate column. The list of music performed is in tabs 2016, 2015, etc. where the ID is in column E en the performance date in column B.

I tried to put in: {=MAX(IF(C2='2016'.$E$2:$E$150,'2016'.$B$2:$B$150))}

That seems to work, but when I pull down this formula all cells report Err:508

How can I make this work? Or is there an easier option to accomplish this?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-06-06 15:02:09 +0100

-rob- gravatar image

Lupp, thanks for your answer. Since I am on a Mac Ctrl doesn't work to fill the arry formula down. Also the function key and the Applekey don't do the trick. I also tried cut/paste, but that works only if pasted to a single cell. Any idea's?

edit flag offensive delete link more

Comments

First select the source cell and all the target cells, then, depending on the version (I' not near to my computer; haven't a LibO at hand at the moment):

Edit > .. or Sheet > ...
Find a > 'Fill' > 'Down' menu item.

Lupp gravatar imageLupp ( 2016-06-06 21:32:13 +0100 )edit
0

answered 2016-06-06 00:39:16 +0100

Lupp gravatar image

updated 2016-06-06 00:40:28 +0100

Keeping data of same structure in more than one sheets is a bad idea.
All the performce data in one sheet would allow to use the formula

{=MAX(($Performnces.$E$2:$E$5000=C2)*($Performances.$B$2:$B$5000))}

entered for arry evaluation wit Ctrl+Shift+Enter.

I cannot see how your formula should return a "ltest" date" if vnot in 206.

Filling an array formula down with the mouse reuires modifiuction with the Ctrl key.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-06-05 22:33:29 +0100

Seen: 1,008 times

Last updated: Jun 06 '16