Back again with the ultimate test.
On a worksheet (A) I have an input legend that contains the following information in 4 columns.
__Avg Size________Avg Time
From__To_______Over__Under
1_____250________1 hr___10 mins
251___500________2 hr___1 hr
etc
In a different worksheet (B) I have a list of data that has the customer, the average size, and the avg time. Example:
__Name________Size______Time__
Customer A______190______1.3 hrs
Customer B______360______1.5 hrs
Customer C______55_______33 mins
etc
On a third worksheet (C) I need a formula that will only carry over the data from (B) exactly as it is when it meets the parameters set out in the legend in (A). For example:
Customer A has an average size of 190 so since the size falls within the 1-250 range it would look to the first row for the time constraints of 1hr and 10mins. Since Customer A's time of 1.3hrs is greater than the 1hr limit a "0" would be placed in that cell.
The end result for worksheet (C) in this example would look like this:
__Name________Size______Time__
___0_____________0__________0___
Customer B______360______1.5 hrs
Customer C______55_______33 mins
I hope this is not too confusing. If you have additional questions please let me know. Thanks for the help.
On a worksheet (A) I have an input legend that contains the following information in 4 columns.
__Avg Size________Avg Time
From__To_______Over__Under
1_____250________1 hr___10 mins
251___500________2 hr___1 hr
etc
In a different worksheet (B) I have a list of data that has the customer, the average size, and the avg time. Example:
__Name________Size______Time__
Customer A______190______1.3 hrs
Customer B______360______1.5 hrs
Customer C______55_______33 mins
etc
On a third worksheet (C) I need a formula that will only carry over the data from (B) exactly as it is when it meets the parameters set out in the legend in (A). For example:
Customer A has an average size of 190 so since the size falls within the 1-250 range it would look to the first row for the time constraints of 1hr and 10mins. Since Customer A's time of 1.3hrs is greater than the 1hr limit a "0" would be placed in that cell.
The end result for worksheet (C) in this example would look like this:
__Name________Size______Time__
___0_____________0__________0___
Customer B______360______1.5 hrs
Customer C______55_______33 mins
I hope this is not too confusing. If you have additional questions please let me know. Thanks for the help.
