Adding some tricky pieces to an INDEX COLLECT formula

JLC

Anybody want to take a stab at this?



I'm helping a design team estimate their effort based on the details of the work coming in. The formula will be an INDEX COLLECT with some nested IF statements. Note that this is currently on a test sheet and in the end the formula will live on an overall tactic sheet with cross-sheet references to columns within a helper chart on a separate sheet. Here's what I have so far:

=IF(OR([Tactic status]@row = "Cancelled", [Tactic status]@row = "On hold"), 0, (IF([Project number]@row = "19-035", 0.5, (IF(OR([Design resource]@row = "Digital Services", [Design resource]@row = "N/A", [Media type]@row = "Non-production"), 0, (INDEX(COLLECT([Total hours]$1:[Total hours]$236, [Media type]$1:[Media type]$236, [Media type]@row, [Level of design/production]$1:[Level of design/production]$236, [Level of design/production]@row, [New or update]$1:[New or update]$236, [New or update]@row), 1)))))))

To summarize:

- if "Tactic status" = "Cancelled" or "On hold", show 0

- if "Project number" = "19-035", show 0.5

- if "Design resource" = "Digital Services" or "N/A", show 0

- if none of the above, pull the "Total hours" from the helper chart based on matching the following: "Media type", "Level of design/production", and "New or update"

This seems to be working so far but I need to add in an important piece that has me stumped. This is the "Number of pages/slides". This piece is ONLY needed when "Media type" is equal to "Print" or "PDF".

The issue here is that "Number of pages/slides" can be any number (maybe for this let's say anything from 0 to 100). So far with the helper chart I have expanded on all combinations (890 in total) up to the value of "24" as that's supposed to be the maximum  However we do have some rare occasions where that value could be anything, for example up to 200 if it's a massive piece of work. I'd like to avoid mapping out all combinations up to 200 pages/slides especially when it's so rarely needed. Is there a way I can say:

- if "Media type" <> "Print" or "PDF", do exactly as already stated in the formula above

- if "Media type" = "Print" or "PDF" and "Number of pages/slides" is <=24, give me the total hours according to all other matches as listed above

- if "Media type" = "Print" or "PDF" and "Number of pages/slides" is >24, consider it to equal 24 and match according to all other matches as listed above

 

I am aware that I could use a helper column in the data sheet to return a value of 24 for anything over 24 in the "Number of pages/slides" column. That would make life much easier! However, our team does not own this sheet and are trying to avoid asking to do so. We'll use it as a last resort but if anybody can help do this in a formula I'd be very grateful. 

Paul Newcome

You would start by using an OR statement for the media type. You can the wrap that along with the additional criteria within an AND statement. It would follow this logic.

 

AND(and logic 1, and logic 2)

 

We would use the OR as a logical statement.

 

AND(OR(or logic 1, or logic 2), and logic 2)

.

We would then just use the normal syntax of nested IF statements.

.

=IF(AND(OR([Media Type]@row = "Print", [Media Type]@row = "PDF"), [Number of pages/slides]@row <= 24), do this, =IF(AND(OR([Media Type]@row = "Print", [Media Type]@row = "PDF"), [Number of pages/slides]@row > 24), do this, otherwise do exactly as already stated in the original formula))

.

Does that help you get started, or did you also need help with the "do this" part of my example?

JLC

Paul, can't thank you enough for this. Here's what I came up with using your framework. I had some updates to the "most important IFs" for lack of a better way of saying it - the first section before we get into the Print and PDF media types and number of pages/slides. Breaking out in chunks underneath for ease of viewing! Thanks again and much appreciated.

=IF(OR([Tactic status]@row = "Cancelled", [Tactic status]@row = "On hold", [Design resource]@row = "Digital Services", [Design resource]@row = "N/A", AND([Media type]@row = "Non-production", [Project number]@row <> "19-035"), OR([Media type]@row = "Video", [Media type]@row = "Podcast", [Media type]@row = "Other")), 0, (IF(AND([Project number]@row = "19-035", AND([Design resource]@row <> "N/A", [Design resource]@row <> "")), 0.5, (IF(AND([Project number]@row = "19-035", [Design resource]@row = ""), 0,

IF(AND(OR([Media type]@row = "Print", [Media type]@row = "PDF"), [Number of pages/slides]@row <= 24), (INDEX(COLLECT([Total hours]$1:[Total hours]$890, [Media type]$1:[Media type]$890, [Media type]@row, [Level of design/production]$1:[Level of design/production]$890, [Level of design/production]@row, [New or update]$1:[New or update]$890, [New or update]@row, [Number of pages/slides]$1:[Number of pages/slides]$890, [Number of pages/slides]@row), 1)),

IF(AND(OR([Media type]@row = "Print", [Media type]@row = "PDF"), [Number of pages/slides]@row > 24), (INDEX(COLLECT([Total hours]$1:[Total hours]$890, [Media type]$1:[Media type]$890, [Media type]@row, [Level of design/production]$1:[Level of design/production]$890, [Level of design/production]@row, [New or update]$1:[New or update]$890, [New or update]@row, [Number of pages/slides]$1:[Number of pages/slides]$890, 24), 1)),

(INDEX(COLLECT([Total hours]$1:[Total hours]$890, [Media type]$1:[Media type]$890, [Media type]@row, [Level of design/production]$1:[Level of design/production]$890, [Level of design/production]@row, [New or update]$1:[New or update]$890, [New or update]@row), 1)))))))))

Paul Newcome

That's one crazy mess right there!!! I'm glad I was able to help! 

JLC

Thanks Paul, it sure is - but it does exactly what we needed it to do and helps our designers estimate their hours on both simple and complex deliverables thanks again!

Paul Newcome

Haha. What matters is that it works.  Happy to help.