How do i make this array formula dynamic?

[wesimmo]

I inherited a spread sheet that uses a technique I've never seen before to decide whether a time in a cell falls between two others.

=IF(PRODUCT({"08:00:00","20:00:00"}-D6)<0,"NO CHARGE",72.31)

I kind of like this one over the alternative of IFs, ANDs etc.

However part of what I am doing is making the models more flexible so that all the parameters and charges are kept in a series of tables and updated there so the model can be used across different contracts just by changing those tables, when I try and change the two times hard coded into the formula above to links to cells containing those values it won't let me enter it, giving me an error and highlighting the references to the cells containing the times as the issue.

While I could just change the formula for each contract and make sure it's dragged down, I'd rather retain the use of the table based approach.

Is there anyway to do that?

 

[RonB1111]

Assume the 1st time is in A1 and the 2nd time is in A2, try:
=IF(A1*A2-D6)<0,"NO CHARGE",72.31)

 

[wesimmo]

Thanks, but that formula has incorrect parentheses?

To be honest in this case with only 2 time limits involved, it's as easy to just use

=IF(PRODUCT($A$1-D6,$A$2-D6)<0,"NO CHARGE",72.31)

It's when there are multiple limits that the array formula would be useful, i.e. if you are checking adherence to a schedule for someone who has a start and end time and various breaks throughout the day. That, and I don't like not understanding why something won't work!

 

[RonB1111]

Sorry, try:
=IF(A1*A2-D6<0,"NO CHARGE",72.31)

 

[wesimmo]

Sorry, it's still not doing what I expected even if I enter it as an array formula.

Its really strange that it only works if you enter the times in the speech marks.

As I thought it works fine if you're testing against more time periods, so for example if I wanted to test if someone made a call while they were scheduled to be at their desk then I could use...

=IF(PRODUCT({"09:00:00","12:00:00","13:00:00","17:00:00"}-B9)<0,"Yes","No") where B9 is the time a call was made.

Which is fine, but if you're working with loads of different people with different working schedules you'd need to make those times dynamic but Excel won't allow me to.

 

[Marcelo Branco]

Maybe something like this...

=IF(SUMPRODUCT(PRODUCT(CHOOSE({1,2,3,4},A1,A2,A3,A4)-B9))<0,"Yes","No")

where
A1 = 09:00:00
A2 = 12:00:00
A3 = 13:00:00
A4 = 17:00:00

Hope this helps

M.

 

[wesimmo]

Thanks, that works as intended, but not sure it's as dynamic as I'd need to use.

I'll play around myself, but assuming that I could have a list of employee IDs and their shifts for a particular day extracted from a scheduling system and then somewhere else an employee ID and a time and date that some action was taken, I'd want to look up the shifts for that employee for that day and then check if that action was done on shift or not.

I realise that I'm probably trying to do something that could be achieved differently, it's more out of interest and not letting it beat me that I want to do it this way!

 

[RoryA]

If your cells are contiguous, it's easy; if not, you probably need something like Marcelo's suggestion.