Another "between 2 numbers" stumper

[PBAnnie]

Good Afternoon! My brain is stumped on a “between the 2 numbers issue"….

Basics: Per Trip, no portion of LM leave can fall outside the trip window. If it does, 1 block under audit should flag “LM Error”. If the Leave Type is not LM or none is taken – the line doesn’t need to be evaluated (I put “no error” and a comment as to why on the example just for clarification of the post). Also, there can be more than one line of LM taken per trip.

Problem is I can’t get any array to correctly either jump past the blanks/non-LMs or correctly do the math to ID the errors.

These can work if I write one really long formula (6 times of the same thing)
=IF(E3<>"LM","", IF(OR(F3<B3,G3>C3),"LM Error",""))
=IF(E3<>"LM","", IF(F3<>MEDIAN(F3,B3,C3), "LM Error",""))

Doesn’t work (as an array):
=IF(E3:E5<>"LM","", IF(F3:F5<>MEDIAN(F3:F5,B3,C3), "LM Error",""))

I've tried a bunch of arrays adding, multiplying, using min/max etc. 20 something hours later, nothing….help please!

Note – first post, so if I goobered it, please take pity on me!

 

[John Jairo V]

Hi!

Try this formula in I3, and drag it down:

=IF(E3<>"LM","",IF((MIN(LOOKUP(2400,B$3:B3),LOOKUP(2400,C$3:C3),F3)>=LOOKUP(2400,B$3:B3))*(LOOKUP(2400,C$3:C3)>=MAX(LOOKUP(2400,B$3:B3),LOOKUP(2400,C$3:C3),G3)),"No","LM")&" Error")

Blessings!

 

[PBAnnie]

Hi!

Try this formula in I3, and drag it down:

=IF(E3<>"LM","",IF((MIN(LOOKUP(2400,B$3:B3),LOOKUP(2400,C$3:C3),F3)>=LOOKUP(2400,B$3:B3))*(LOOKUP(2400,C$3:C3)>=MAX(LOOKUP(2400,B$3:B3),LOOKUP(2400,C$3:C3),G3)),"No","LM")&" Error")

Blessings!

Thank you! Tried and it works....Is it possible to modify a bit so I can put the formula in only 1 cell per trip (Like I4, I7, and I10)?

 

[John Jairo V]

Although what you say seems nonfunctional (since the formula I gave you need not look out where the data with LM is) I will help you with the first formula. Likewise, you can put the other.

=IF(E3<>"LM","",IF((MIN(B3,C3,F3)>=B3)*(C3>=MAX(B3,C3,G3)),"No","LM")&" Error")

God Bless You!

 

[PBAnnie]

Actually your formula is absolutely amazing - I have no clue as to how you're not referencing the LM times...

Problem comes in that the other "I" cells have other formulas in them for other errors - If I can put the formula outside the visible portion of the spreadsheet and refer the "LM Error" to just one cell that would work also.

 

[PBAnnie]

Actually your formula is absolutely amazing - I have no clue as to how you're not referencing the LM times...

Problem comes in that the other "I" cells have other formulas in them for other errors - If I can put the formula outside the visible portion of the spreadsheet and refer the "LM Error" to just one cell that would work also.

I've been pondering how the formula works - I'm baffled by the vlookup 2400 since there isn't a 2400 anywhere I can't decide what it's finding. Help please

 

[NARAYANK991]

Hi ,

If you want to know how the LOOKUP function works , the best way is to experiment. In the attached file , I have put in 3 columns of data ; in the first , the data is sorted in ascending order , in the second it is in descending order , and in the third , it is in random order.

Put in different values in cells B1 , C1 and D1 , and see what the LOOKUP function returns. If you experiment enough , you will get an idea of how the function works.

Narayan

 

[PBAnnie]

Hi ,

If you want to know how the LOOKUP function works , the best way is to experiment. In the attached file , I have put in 3 columns of data ; in the first , the data is sorted in ascending order , in the second it is in descending order , and in the third , it is in random order.

Put in different values in cells B1 , C1 and D1 , and see what the LOOKUP function returns. If you experiment enough , you will get an idea of how the function works.

Narayan

Thank you! I've researched, pondered, and puddled with it - I get everything except why when you're asking it to find 11 it returns an 1 or an 8...from everything I've read it should return the closest number that is not higher than it - which would be a 10 in every case. Interpreted in my brain it means that if the choices were 1-9, 10, 12, it should return the 10 no matter what order they're in?

 

[NARAYANK991]

Hi Annie ,

When the value being looked up is greater than the maximum value in the data range , the LOOKUP function will return the value which is found in the last place in the range ; it does this because it expects the range to be sorted in ascending order.

Thus , when the last value in the sorted range is 10 , it returns 10 , but if the range is sorted in descending order , the last place in the range is occupied by 1 , while if the range is in random order , the last place in the range may be occupied by any random number.

Narayan

 

[PBAnnie]

Thank you! Finally my brain can rest easy.....actually that's a Very useful thing to know if I just need to find the end of list! And, now I get why John's post worked! Off to finish my project now.....