Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nth value in an array formula
I currently have an array formula that reads
{=IF(MilestoneDates=$D$31;Tasks;"")} What this does is that it checks through my milestone dates and sees if they are equal to the next Group Meeting date ($D$31) - if they are, the formula returns the associated Task. The problem is I only know how to use this to show the first such result. What I want to be able to do is return, for example, all five tasks with this milestone date, on separate rows. The tasks are all text values unfortunately, which means I can't use BIG or SMALL unfortunately. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
nth value in an array formula
Perhaps this can help:
http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200839 "Babymech" wrote: I currently have an array formula that reads {=IF(MilestoneDates=$D$31;Tasks;"")} What this does is that it checks through my milestone dates and sees if they are equal to the next Group Meeting date ($D$31) - if they are, the formula returns the associated Task. The problem is I only know how to use this to show the first such result. What I want to be able to do is return, for example, all five tasks with this milestone date, on separate rows. The tasks are all text values unfortunately, which means I can't use BIG or SMALL unfortunately. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
nth value in an array formula
Thanks - that did help indeed, but as soon as I got it to work another question popped up: now I want to do the same with multiple criteria. I assumed that it wouldn't be harder than using: {=IFERROR(INDEX(Tasks;SMALL(IF(AND(MilestoneDates D31-6;MilestoneDates<=D31);ROW(MilestoneDates));ROW(1: 1))-3);"")} But that returns nothing. How would I go about setting it up so that the date it checks is between two values, or bring in another criteria to bear? "Gary''s Student" wrote: Perhaps this can help: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200839 "Babymech" wrote: I currently have an array formula that reads {=IF(MilestoneDates=$D$31;Tasks;"")} What this does is that it checks through my milestone dates and sees if they are equal to the next Group Meeting date ($D$31) - if they are, the formula returns the associated Task. The problem is I only know how to use this to show the first such result. What I want to be able to do is return, for example, all five tasks with this milestone date, on separate rows. The tasks are all text values unfortunately, which means I can't use BIG or SMALL unfortunately. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
nth value in an array formula
Never mind - I think I got it, by using multiplication instead of an AND
formula. Thanks! "Babymech" wrote: Thanks - that did help indeed, but as soon as I got it to work another question popped up: now I want to do the same with multiple criteria. I assumed that it wouldn't be harder than using: {=IFERROR(INDEX(Tasks;SMALL(IF(AND(MilestoneDates D31-6;MilestoneDates<=D31);ROW(MilestoneDates));ROW(1: 1))-3);"")} But that returns nothing. How would I go about setting it up so that the date it checks is between two values, or bring in another criteria to bear? "Gary''s Student" wrote: Perhaps this can help: http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200839 "Babymech" wrote: I currently have an array formula that reads {=IF(MilestoneDates=$D$31;Tasks;"")} What this does is that it checks through my milestone dates and sees if they are equal to the next Group Meeting date ($D$31) - if they are, the formula returns the associated Task. The problem is I only know how to use this to show the first such result. What I want to be able to do is return, for example, all five tasks with this milestone date, on separate rows. The tasks are all text values unfortunately, which means I can't use BIG or SMALL unfortunately. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
nth value in an array formula
Excel 2007 Pivot Table
Works on text. One easy non-array formula: =([Deadline]-[Milestone]<6)*([MyCriteria]=1) No hidden rows. Dynamic resize. http://www.mediafire.com/file/lgajntlwldm/03_16_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Help | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
An array formula to sum row max | Excel Discussion (Misc queries) |