Multiple condition

[excelclassic]

Hi There,
Iam looking for a formula to find the rates corresponding to the chcode and the timeband range in another file.

RateMaster.xlsx:
ChCode Start_time End_time FixedRate
1 6:00 8:59 5000
1 9:00 12:59 25000
1 13:00 18:59 36000
2 7:00 17:59 15000
2 18:00 18:29 18000
and so on

Trans.xlsx:
ChCode Time Rate
1 13:03 36000
1 11:41 25000
2 18:29 18000
and so on
I need the Rate column in Trans.xlsx to pick the FixedRate from RateMaster.xlsx for the corresponding ChCode and the time in the start_time and end_time range (for example 13:03 comes in range 13:00 - 18:59) . I have 350 Chcodes with each code having various timebands and trans file has 60000+ records. I tried using INDEX & MATCH but could not get through. Is there any formula to get this?

[TMS]

Please post a sample workbook.

Regards

[excelclassic]

Thanks for the quick response. Here are the sample workbooks.

[TMS]

You can use SUMIFS to do this in Excel 2007 and above.

Cell C2: =SUMIFS([RateMaster.xlsx]Sheet1!$D$2:$D$1550,[RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2)

and copy down.

However, you have a problem. The "times" in the RateMaster workbook are not (all) actually times; they're text values that look like times.

You can convert the text to a time value with this formula:

Cell E2: =IF(TYPE(B2)=2,TIME(LEFT(B2,FIND(":",B2)-1),RIGHT(B2,2),0),B2)

copy across to F2 and then down to row 1550.

Once you have the times, copy and paste special into columns B and C.

You'll probably need to format the times as hh:mm.

With the times converted, your INDEX/MATCH might also work.

Not as straightforward as it looks.

[excelclassic]

thanks TMShucks, I tried as you said. It works - however there are some duplicate rows for each Chcode and timeslots in RateMaster, and hence sumifs function sums the multiple rates that matches the specified conditions. I donot want the rates to be added up. For redundant records, let the rates on the first or last row that matches the conditions be considered. Also I need #N/A for no match found. Please help.

[TMS]

If the values are duplicate, how about:

=SUMIFS([RateMaster.xlsx]Sheet1!$D$2:$D$1550,[RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2)/COUNTIFS([RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2)

Regards

[TMS]

I've had another look at your data and the formula. All the entries, where they exist, appear to be paired so the COUNTIFS will return 2 and halve the SUMIFS value. Seems like a result ;-)

Where the value does not exist, the COUNTIFS will return 0 and the formula will give #DIV/0! This can be converted to #N/A using the IFERROR and NA() functions as below.

=IFERROR(SUMIFS([RateMaster.xlsx]Sheet1!$D$2:$D$1550,[RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2)/COUNTIFS([RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2),NA())


Given that this uses SUMIFS and COUNTIFS with the same conditions, it seems logical to conclude that it could be shortened using AVERAGEIFS ... but I have yet to test that.

Regards

[excelclassic]

Hi TMShucks, The formula is calculating the average of rates in case of nested timeslots. For example, Code 6 has timeslots 12:00 - 17:59 (rate: 5500) and 12:30 - 13:59 (rate: 4000). for the overlapping timeslot 12:30-13:59, the formula calculates the average rate and gives 4750 as the rate. For times 12:30-13:59, it should take 4000 as the rate. Is the formula be possible with INDEX? Please suggest.

[excelclassic]

The formula is calculating the average of rates in case of nested timeslots. For example, Code 6 has timeslots 12:00 - 17:59 (rate: 5500) and 12:30 - 13:59 (rate: 4000). for the overlapping timeslot 12:30-13:59, the formula calculates the average rate and gives 4750 as the rate. For times 12:30-13:59, it should take 4000 as the rate. Any formula possible with INDEX/Match? Please suggest.

[TMS]

If you add a Helper column to the RateMaster sheet, say, column H with the formula: =--(A2&B2), you can then use INDEX/MATCH:

=INDEX([RateMaster.xlsx]Sheet1!$D:$D,MATCH(--(A2&B2),[RateMaster.xlsx]Sheet1!$H:$H,-1))

I hope this is what you want as I'm all out of ideas

Regards

[excelclassic]

Hi TMShucks, INDEX/MATCH formula gives me different rates, your following formula (that you gave earlier) is fine:
=IFERROR(SUMIFS([RateMaster.xlsx]Sheet1!$D$2:$D$1550,[RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2)/COUNTIFS([RateMaster.xlsx]Sheet1!$A$2:$A$1550,A2,[RateMaster.xlsx]Sheet1!$B$2:$B$1550,"<="&B2,[RateMaster.xlsx]Sheet1!$C$2:$C$1550,">="&B2),NA())

only that it does summation of all applicable timebands. All I need is the corresponding rate for the relevant row of ratemaster. Instead of sumifs which adds the rates it would be great if there is a function in excel which picks the corresponding row's rate only. Is this possible?