Combining two formulas

[johnmitch38]

Hi all,

I have the two below formulas that work fine on their own. I would like to combine them:-

{=IFERROR(INDEX(data!$L$2:$L$999,MATCH($A4&"|"&E$2,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0)),0)} - This returns a time that I want to round up to the nearest 15 minutes.

=TIME(HOUR(REF), ROUNDUP((MINUTE(REF)/60)*4, 0) * 15, 0) - Replace REF with the cell that contains the above and I get the answer I'm looking for.

If I just copy the first formula over the top of "REF", The formula works but doesn't return the correct value.

I'm not sure exactly what's going on but if it anyone can help it will be greatly appreciated.

[soberguy]

=IFERROR(TIME(HOUR(INDEX(data!$L$2:$L$999,MATCH($A4&"|"&E$2,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0)), ROUNDUP((MINUTE(INDEX(data!$L$2:$L$999,MATCH($A4&"|"&E$2,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0))/60)*4, 0) * 15, 0),0)

[johnmitch38]

Thanks but that returns the "the formula you have entered contains an error" message

[soberguy]

Hmm. OK, when I get back in the office I'll get it sorted.

[johnmitch38]

Thank you!

much appreciated.

[soberguy]

Do you have some sample data I can work with? Particularly for the index/match you're running. Do you have a sample spreadsheet with sample data? Would be a huge help.

[johnmitch38]

I've attached my spreadsheet.The formula in question I've highlighted yellow.

Basically I'd just like it to round up to the nearest 15 minutes.

Thank you.

[soberguy]

Remember to enter as an array formula with Ctrl Shift Enter:
=TIME(HOUR(INDEX(data!$L$2:$L$999,MATCH($A13&"|"&F$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0))),ROUNDUP((MINUTE(INDEX(data!$L$2:$L$999,MATCH($A13&"|"&F$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0)))/60)*4,0)*15,0)

Edit:
=IFERROR(TIME(HOUR(INDEX(data!$L$2:$L$999,MATCH($A11&"|"&F$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0))),ROUNDUP((MINUTE(INDEX(data!$L$2:$L$999,MATCH($A11&"|"&F$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0)))/60)*4,0)*15,0),0)

Forgot you had the IFERROR on there. Apologies.

[johnmitch38]

Thanks, I just get 00:00 as a result though?

[soberguy]

It's a cell reference issue. Put this in E8:

=IFERROR(TIME(HOUR(INDEX(data!$L$2:$L$999,MATCH($A8&"|"&E$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0))),ROUNDUP((MINUTE(INDEX(data!$L$2:$L$999,MATCH($A8&"|"&E$7,data!$B$2:$B$999&"|"&data!$C$2:$C$999,0)))/60)*4,0)*15,0),0)

[johnmitch38]

Awesome you're a star.

It's is greatly appreciated!

[soberguy]

Happy to help!

[FDibbins]

try this...
=IFERROR(TIME(HOUR(INDEX(data!$L$2:$L$999,MATCH($A8&"|"&E$7,INDEX(data!$B$2:$B$999&"|"&data!$C$2:$C$999,0),0))), ROUNDUP((MINUTE(INDEX(data!$L$2:$L$999,MATCH($A8&"|"&E$7,INDEX(data!$B$2:$B$999&"|"&data!$C$2:$C$999,0),0)))/60)*4, 0) * 15, 0),0)

edit: oops need to refresh more often