77highland
New Member
- Joined
- Nov 11, 2013
- Messages
- 7
Hi All,
Need help with 2 formulas, kind of related as both need a HLOOKUP start range and 'last blank cell in row' as end range (i believe), any help will be greatly appreciated.......
In column M (M2:M5) i need a formula along the lines of......
=IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',"<600"),IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',">600"),IF(L2="","No")))
In column O (O2:O5) i need a formula along the lines of......
=AVERAGE(HLOOKUP(N2,A1:J5,2):'last blank cell in row')
But please note that the date range continuously expands via inserting columns (between columns J and K in the example below).
<tbody>
</tbody>
Need help with 2 formulas, kind of related as both need a HLOOKUP start range and 'last blank cell in row' as end range (i believe), any help will be greatly appreciated.......
In column M (M2:M5) i need a formula along the lines of......
=IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',"<600"),IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',">600"),IF(L2="","No")))
In column O (O2:O5) i need a formula along the lines of......
=AVERAGE(HLOOKUP(N2,A1:J5,2):'last blank cell in row')
But please note that the date range continuously expands via inserting columns (between columns J and K in the example below).
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
1 | 01/05/18 | 02/05/18 | 03/05/18 | 04/05/18 | 05/05/18 | 06/05/18 | 07/05/18 | 08/05/18 | 09/05/18 | 10/05/18 | Priority Tracking Start Date | Priority | Priority % Hit Rate | OH Completion Date | Average Mileage since OH |
2 | 300 | 400 | 500 | 500 | 700 | 200 | 400 | 500 | 01/05/18 | 02/05/18 | |||||
3 | 400 | 200 | 800 | 900 | 500 | 600 | 1000 | 400 | 02/05/18 | High | 01/05/18 | ||||
4 | 200 | 300 | 700 | 800 | 700 | 500 | 100 | 0 | 04/05/18 | Low | 03/05/18 | ||||
5 | 400 | 800 | 1000 | 200 | 0 | 0 | 300 | 400 | 04/05/18 | High | 02/05/18 |
<tbody>
</tbody>