help please

[David]

I hope I explain what I want to do and it doesn't sound confusing.


In cells C9 C10 C11 I will enter in three different start times in E9
E10 E11 I will enter in three different finish times in M9 M10 M11 I
have three different numbers. In column A starting with A16 down to A27
I have fixed times incrementing by one hour. What I want to do is find
out what function to use in C16 down to C27 that determines if the times
in column A are within the time range of C9 and E9 then divide the
number in column B by M9 or if they are within the range of C10 and E10
then divide it by M10 and the same for M11.

Thank you so much in advance if I have made myself clear.
David

 

[Jason Morin]

One way...place this in C16, press ctrl/shift/enter, and
fill down:

=INDIRECT("M"&MAX((A16>=$C$9:$C$11)*(A16<=$E$9:$E$11)*
{9;10;11}))

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Sorry...forgot about column B. B16 should be divided by
the formula I gave you, so use:

=B16/INDIRECT("M"&MAX((A16>=$C$9:$C$11)*(A16<=$E$9:$E$11)*
{9;10;11}))

Jason

 

[David]

One way...place this in C16, press ctrl/shift/enter, and
fill down:

=INDIRECT("M"&MAX((A16>=$C$9:$C$11)*(A16<=$E$9:$E$11)*
{9;10;11}))

HTH
Jason
Atlanta, GA



times in E9


M9 M10 M11 I


A16 down to A27


to do is find


determines if the times


divide the


of C10 and E10

Jason,

I want to thank you so much for your reply. I might have not explained
it clear enough but it looks like you know what I am talking about. Let
me see if I can better explain it.

For cells C9 and E9 I will be entering a start time and a finish time
lets say 4:30 and 5:15 for cell A16 there will be a fixed time of 5:30
in cell C16 I want to enter a formula looks to see if the time in A16 is
within the time range that I entered in C9 and E9 if so then divide B16
by M9 if not see if A16 is within the time range of C10 and E10 if so
then divide B16 by M10 and if not check to see if A16 is within the
range of C11 and E11 if so divide B16 by M11.

Thanks again Jason for your help.

David

 

[David]

Sorry...forgot about column B. B16 should be divided by
the formula I gave you, so use:

=B16/INDIRECT("M"&MAX((A16>=$C$9:$C$11)*(A16<=$E$9:$E$11)*
{9;10;11}))

Jason

Jason,

I think I have it thanks to you but, could you explain that formula. I
understand this part of it A16>=$C$9:$C$11)*(A16<=$E$9:$E$11 but I don't
see where the number in column B gets divided by M9,M10, or M11.
Everything works I just want to put the numbers in the M column on a
different hidden sheet. Also the B column has a formula in it that
refers to column D. Using the formula you gave me results in a #Value!
unless I type a number in B16

Thanks again,
David

 

[David]

Jason,

I think I have it thanks to you but, could you explain that formula. I
understand this part of it A16>=$C$9:$C$11)*(A16<=$E$9:$E$11 but I don't
see where the number in column B gets divided by M9,M10, or M11.
Everything works I just want to put the numbers in the M column on a
different hidden sheet. Also the B column has a formula in it that
refers to column D. Using the formula you gave me results in a #Value!
unless I type a number in B16

Thanks again,
David

Jason,

I figured it out Thanks a Million for your help

David